J****R
发帖数: 373 | 1 谢谢回复。可能是我没表述清楚。
假设某个regionA里面已经做过compaction,所以只有一个接近maxsize的HFile1，里面
存了3行数据
001:column1
002:column1
003:column1
那么当在每一行都加一个新的column2的时候，会在这个region folder里面产生一个新
的HFile2.里面也是3行数据：
001：column2
002：column2
003：column2
在下一次compaction的时候, sort data by key, 同一key的data需要存放在一起，从
而形成：
001：column1
001: column2
002: column1
002：column2
003: column1
003：column2
但是这个合并后的Hfile文件会超过maxsize，所以又要region split。导致产生一个新
的regionB,结果变成了：
regionA:
001：column1
001: column2
002: column1
002：column2
regionB:
003: column1... 阅读全帖 |
|
l*********8
发帖数: 4642 | 2 CREATE UNIQUE INDEX
ON t1(column1, column2, column3);
CREATE UNIQUE INDEX
ON t2(column1, column2, column3);
SELECT 'different'
FROM dual
WHERE EXIST
(
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
); |
|
b******d
发帖数: 1948 | 3 no man, you need a 'group by' here, google 'group by' see what ppl say.
If you want select * from table1 + count(column1), you need do a join
also.
select t.*, x.cnt from table1 t inner join (
select column1, count(pk) as cnt from table1
) as x on t.column1 = x.column1
pk is the primarykey, |
|
w****n
发帖数: 266 | 4 请教一个conditional join问题，
A left join B ON a.column1=b.column1 and a.column2=b.column2..., 如何动态的
决定ON clause里的column。
我有一个表c， 如果c.column1=TRUE, put a.column1 in ON clause. 依此类推。 |
|
e****e
发帖数: 3450 | 5 想按列合并两个文件，每个几百万行，每个文件４列，不可能用excel，用join也非常
messy，我硬着头皮东查西查的写了个perl script，不work （这是这辈子写的第２
个perl程序）。
麻烦大家帮我看看？或者有什么更简单的方法？xiexie！
#!/usr/bin/perl
use strict;
# this program is for merging the columns of two files, column 1,2 3, 4 of
file1, column 3 and 4 of file 2
my $f1 = $ARGV[0];
my $f2 = $ARGV[1];
print "Processing file $filename_1 $filename_2\n";
open (FILE_IN_1, "<$f1") || die "can not open";
open (FILE_IN_2, "<$f2") || die "can not open";
open (FILE_OUT, ">$f1.$f2.merged") || die "can not... 阅读全帖 |
|
a*******t
发帖数: 891 | 6 can new table be used?
not sure if it is the shortest/cleanest, but it's simple
select *
into tmpTableC
from tableA
update tmpTableC
set tmpTableC.column1 = tmpTableC.column1 + tableB.column1
select *
from tmpTableC |
|
g*********e
发帖数: 29 | 7 Table A:
column1 column2 column3
a x 2
a c 4
a x 1
b d 9
c f 7
c j 3
请问怎样加index based on column1 to create table B:
index column1 column2 column3
1 a x 2
2 a c 4
3 a x 1
1 b d 9
1 c f 7
2 c j 3
我想这样我就可以select * from table B where index=2
2 a c 4
2 c j 3 |
|
j**n
发帖数: 551 | 8 请问如何实现：
if a row is exist, then update it.
otherwise, insert a new row.
procedure(in argu1, in argu2)
if (select * from table1 where column1=argu1) exist // how to implement
this???
then (update table set column2=argu2 where column1=argu1)
else
insert into table set column1=argu1, column2=argu2.
多谢。 |
|
d*******n
发帖数: 109 | 9 this is a single table query,
table looks like this:
column1 column2 column3 column4
1 0 1 0
0 1 1 1
1 0 0 0
1 1 1 1
...
four columns, each can be 1 or 0,
write one SQL query to get output looks like
TotalRow Count1 Count23 Count4
total row is the total row of table
count1 is the count of all row which column1 = 1 and the rest 3 columns are
0
count23 is the count of all row which... 阅读全帖 |
|
w****n
发帖数: 266 | 10 不同的market, ON的column不同, 我想用dynamic query,
@sqlcommand= 'select...'
case when c.maretID=@marketID Then
when c.column1=TRUE Then
@sqlcommand = @sqlcommand+'ON a.column1 = b.column1'
.....
exec(@sqlcommand)
不知是否可行？ |
|
l******9
发帖数: 579 | 11 I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR: 9434 : Not enough memory for merge-style join
Are there better ways to do this comparison.
thanks ! |
|
m*******o
发帖数: 264 | 12 column1 column2 column3
a b 2
a c 4
b d 9
a c 7
Query以后，把column1和column2完全相同的行输出，并把column3的结果相加：
a c 11 |
|
M**********n
发帖数: 432 | 13 select column1,column2,sum(column3) from table_name group by column1,
column2
having count(*)>1; |
|
m******o
发帖数: 61 | 14 不好意思，问个土问题： 我用的是mysql server。
需要做update的query，用一个table a 里的每一个row 作为参数。请问用while loop
怎么做一个windows 的SQL script? 只知道在unix下，可以用cat.
比如：
table a: column1 id 是auto-increment.
column1 id column2 vend
1 1
2 4
3 22
4 184
我想做的update query 是：
update tableB set somecolumn=something where columnInTableB like tableA.
column2;
多谢了！ |
|
i****a
发帖数: 36252 | 15 what is 变量? variable? or value?
select column1
from tableA
where len(column1) = 1 |
|
d*******n
发帖数: 109 | 16 请问以下两个SQL语句有区别吗？
select a.column1, b.column2
from a inner join b
on a.column3 = b.column3 and b.column4 = 'XXX'
where a.column4 = 'YYY'
select a.column1, b.column2
from a inner join b
on a.column3 = b.column3
where a.column4 = 'YYY' and b.column4 = 'XXX'
谢谢 |
|
l******9
发帖数: 579 | 17 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: compare two large tables SQL
发信站: BBS 未名空间站 (Thu May 8 19:33:54 2014, 美东)
I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR:... 阅读全帖 |
|
O***T
发帖数: 124 | 18 我现在有一个很简单的stored procedure in db2
create or replace procedure P_1()
begin
insert into table_A ( column1,column2)
select a,b
from table_b;
end;
为了防止这个procedure fail（比如table_b不存在之类的情况...)，我想放一个error
handler在这个statement里面，我不在乎到底是什么error,只要上面那个Insert
statement fail了（或者说这个sp fail了？），就往另外一个table添加一条类似Log
的记录Insert into table_c (column1,column2) values (P_1,current date)
想请教各位大牛这个code我该怎么写，从来没写过error handler的东西，网上查了很
久，仍旧是一片雾水，连应该放在这个insert statement前面还是后面还是专门写一个
error handler的sp都没想明白。
另外，因为工作关系，现在开始接触... 阅读全帖 |
|
s******a
发帖数: 184 | 19 我有两个Excel 文件，第一个文件存着下面这样的数据
12 A P1
23 B P5
24 C P2
15 D P1
06 E P5
第二个文件存着下面这样的数据
06 100
23 20
06 200
06 95
23 05
24 18
基于第一个文件， 我产生了下面这样的结构
$VAR1 = {
'P5' => {
'E' => '06',
'B' => '23'
},
'P2' => {
'C' => '24'
},
'P1' => {
'A' => '12',
'D' => '15'
}
};
我是这么做得
my %Var1;
for my $i (1 .. $row1)
{
# for simplicity, I just keep the main part to building this hash chain
$Var1{$column3}->{$column2} = {$column1};
}... 阅读全帖 |
|
l******9
发帖数: 579 | 20 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: compare two large tables SQL
发信站: BBS 未名空间站 (Thu May 8 19:33:54 2014, 美东)
I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR:... 阅读全帖 |
|
j*a
发帖数: 14423 | 21 how about
: SELECT *
: FROM t1, t2
: WHERE NOT(t1.column1 = t2.column1 AND t1.column2 = t2.column2
: AND t1.column3 = t2.column3 AND t1.column4 != t2.column4) |
|
l******9
发帖数: 579 | 22 It also ran out of memory. Thanks !
发信人: joa (不痛不痒), 信区: Quant
标 题: Re: compare two large tables SQL (转载)
发信站: BBS 未名空间站 (Thu May 8 20:05:30 2014, 美东)
how about
: SELECT *
: FROM t1, t2
: WHERE NOT(t1.column1 = t2.column1 AND t1.column2 = t2.column2
: AND t1.column3 = t2.column3 AND t1.column4 != t2.column4) |
|
g********3
发帖数: 123 | 23 COLUMN1 COLUMN2 COLUMN3
1 L 0.1
1 U 0.2
2 L 0.11
2 U 0.6
3 L 0.3
3 U 0.33
需要变成：
COLUMN1 COLUMN2 COLUMN3
1 0.1 0.2
2 0.11 0.6
3 0.3 0.33
请问如何用sas实现？
谢谢！ |
|
l******9
发帖数: 579 | 24 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: compare two large tables SQL
发信站: BBS 未名空间站 (Thu May 8 19:33:54 2014, 美东)
I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR:... 阅读全帖 |
|
K******g
发帖数: 1870 | 25 请问谁能解释一下Column1里的第9题?我看了好多遍,仍然不知所云.多谢了 |
|
L****3
发帖数: 5 | 26 Is there anyone can help me this question?
Thanks in advance!
Write a query to parse the order number highlighted in yellow from the
following strings, assuming that the column being parsed is named Column1
and the Table name is TableA:
RWXTR*345724*127BRZU
GHE*2546*4569MRIA
Assume that if an order number exists, the order number is always bounded by
*, but is not always at the same position within the string and the order
number is not always the same length.
Note: the highlighted number is ... 阅读全帖 |
|
l******9
发帖数: 579 | 27 i got error:
CREATE UNIQUE INDEX Pindex1
ON t1 (column1, column2, column3);
ERROR [HY000] ERROR: CREATE INDEX not supported in this release
Thanks |
|
g******h
发帖数: 291 | 28 one strategy is to look at each number (1-9) till it is exhausted
(having taken 9 positions).
*1 is gone, 9 positions are taken.
*2 you have a bunch of empty slots and the next position is not obvious
*3 is the same as 2, the positions are iffy
*4 is good candidate 8 positions are taken, only the lower-left 3x3 square
is not populated with 4. 2 horizontal sweeps take out rows 1 and 2. 2
vertical sweeps take out columns 2 and 3 so you put 4 in the lower left
corner, row3/column1, of the lower-lef... 阅读全帖 |
|
d**********o
发帖数: 1321 | 29 RTOS作业：2514
The purpose of this assignment is to give you more experience using the AVR
ports, and to add some I/O devices that might become useful later for our
RTOS.
You are to use one of the provided keypads to implement a “digital lock.”
Your program should allow the user to enter a four digit code from the
keypad, and if the code that is input matches the one included in your
program, the “lock” should open. In this case, the lock opening will be
represented by the lighting of an LED.
As th... 阅读全帖 |
|
d**********o
发帖数: 1321 | 30 RTOS作业：2514
The purpose of this assignment is to give you more experience using the AVR
ports, and to add some I/O devices that might become useful later for our
RTOS.
You are to use one of the provided keypads to implement a “digital lock.”
Your program should allow the user to enter a four digit code from the
keypad, and if the code that is input matches the one included in your
program, the “lock” should open. In this case, the lock opening will be
represented by the lighting of an LED.
As th... 阅读全帖 |
|
C****y
发帖数: 581 | 31 最近刚学python。刚好写一个游戏叫步步紧追(quoridor).就是求两个方格之间的最短
距离。
我现在的问题是根本不知道怎么开始。大家能给点思路吗？
像图上的墙壁是由一组4个数字组成的(row1, column1) to (row2, column2).
图中的是。
3 2 3 4
4 0 4 2
6 1 8 1
5 7 7 7
如果建立遍历图的话？class 应该是x,y,neighbor吗？
谢谢 |
|
i*****t
发帖数: 220 | 32 I am a new user of SQL. I just have a simple question: in a SQL statement:
create table tablename (column1 varchar2(20));
what the 2(20) means? Thanks. |
|
c*****d
发帖数: 6045 | 33 在oracle里可以这样做
declare
row_cnt number;
begin
select count(*) into row_cnt from table1 where column1=argu1;
if ( row_cnt = 0 )
then
...
else
...
end if;
end; |
|
S*********d
发帖数: 119 | 34 sql server 里面update一个table with a resultset，要根据被update的column来决
定怎么update，比如如果column1的value如果跟resultset的那个对应的value一样的话
，就不update这个column了
问题是除了CURSOR外，是否可以用一句UPDATE做呢 |
|
n********6
发帖数: 1511 | 35 如果在sql server里面，可以用
SELECT * FROM Table1 WHERE Column1 like '[_]1'
[_]表示任意一个字符/数字。
不过你用“”表示字符串，可能你用oracle。我不知道oracle用什么表示。 |
|
M***7
发帖数: 2420 | 36 could I use "where column1 not like" ? |
|
B*********L
发帖数: 700 | 37 下面这个query（其中B是个长公式），column3 就是 column1 乘以 column2，
SQL server总是给我0.
SELECT
A
,POWER(cast(10 as dec(38,20)),B)
,A*POWER(cast(10 as dec(38,20)),B)
FROM TABLE_A
result:
8694 0.00000000003548134000 0.00000000000000000000
2103 0.00000000003981072000 0.00000000000000000000
对column3，我试过以下几种办法，都不行。
,CAST(A as dec(38,20))*POWER(cast(10 as dec(38,20)),B)
,CAST(A as dec(38,20))*CAST(A POWER(cast(10 as dec(38,20)),B) as dec(38,20))
,CAST(CAST(A as dec(38,20))*CAST(A POWER(cast(10 as dec(38,20)), |
|
c*******e
发帖数: 8624 | 38 select *
from your_table
qualify row_number() over (partition by column1 order by column3 desc) = 1 ; |
|
t********5
发帖数: 274 | 39 问个弱问题,
只有在conditionA的情况下，更新column2，否则不更新，不想把IF写在update外边，
按照我下边的样子，能实现吗
update tableA
set column1 ='aaa',
if(conditionA)
set column2='bbb',
else
do not update column2
end
set column3='sdfd',
....... |
|
m***i
发帖数: 2480 | 40 update tableA
set Column1='aaa'
where (condition='True') |
|
j****s
发帖数: 881 | 41 题是抄对了，这是其中的一道，两个小时交卷。我当时云里雾里，就稀里糊涂地写:
Select distinct column1, distinct column2
From Table1
go
知道一定是错的,但是看不懂题啊。
大牛也不知所云，我心稍安。 |
|
B*****g
发帖数: 34098 | 42 把columnname加到insert里，只有当column和value一一对应的时候column才能省略（
而且不建议这样做）
inset int DeptAudit (column1, column2....)
values ..... |
|
s*****d
发帖数: 7 | 43 谢谢Beijing and NaCN. 说的是不清楚，例子给得也不好。实际数据太大太复杂，全是
多对多，没有任何key. 总之就是，一个column 要先 inner join, 另一个 column 在
column1 inner join 之后 再 outer join. 但是第一个inner join 两个 columns 都
要 ON. Like on t1.c1=t2.c11 and t1.c2=t2.c22, 但是这样又把t1.c2中的NULL
filtered out了。 NaCN的方法看起来可行，我去真数据上试试看。
再一次谢谢你们。 |
|
x******m
发帖数: 736 | 44 errrrrrrrrrrrrrr
在初级的DBA，也不会这么命名吧。我只是简单说col1，并不是column1的header就是
col1.哪个DBA要是这么命名，估计离丢工作也不远了。。。。
这样说吧，table1的attributes在数据库中被分成好几类，比如，身高，体重属于body
index，心跳，血压属于health index（table2）。
要生成的table3，就是id attribute 在加上 attribute的类别。
其实这就是个sql问题，跟这些都无关。
实在不行，我就把table output出来，几行python code就搞定。就是想借这个机会看
看sql有没有好的解法。 |
|
v*****r
发帖数: 1119 | 45 1. 简单的 query, 直接在 query 里dynamic赋值
sqlplus -silent id/password@oracel_instance <
select 'foo='id from table;
...
EOF
2.复杂的 Query ，re-direct here document output to a temporary file and awk
the temporary file
sqlplus -silent id/password@oracel_instance < tempfile
select column1,column2...,columnN from table;
...
EOF
awk .... tempfile
printf( |
|
G***G
发帖数: 16778 | 46 a data.frame in R like
column1 column2
rowname1 0.1 0.3
rowname2 0.4 0.2
rowname3 0.5 0.1
How can I extract the second column with the rowname remained?
column2
rowname1 0.3
rowname2 0.2
rowname3 0.1
thanks. |
|
z****g
发帖数: 1978 | 47 1. 同Newton method.
2. notice the row, column, diagonal operation are interchangeable.
Suppose there is r1,r2,r3 row operation on row1, 2, 3 and c1, c2, c3
operation on column1, 2, 3, and d1, d2 diagonal operation for two
diagonal lines. so you have equations in terms of mod 2. There should be
standard theory after this step.
3. Basic CS algorithm question. suppose you have tree structure
A, A.B1, A.B2, A.B1.C1. A breadth-first is A, A.B1, A.B2, A.B1.C1 where
you loop through the nodes at the sam... 阅读全帖 |
|
l***a
发帖数: 12410 | 48 proc transpose data=one;
id column2;
by column1;
var column3;
run; |
|
s********r
发帖数: 297 | 49 Column1 Column2: (#.of.A) / (total.#)
A, B, C 1 / 3
A, B, A, D 2 / 4
A, B, A, D, A 3 / 5
.....
请问已知在一个CSV文件里 column 1 里面 是无次序并且可能重复的人的代号(用A,B..
.等字母代表）
delimiter是 ","
请问怎么在csv文件里添加一个 new column (column2) 并且算出 A 那个人在每个row
的出现的frequency 除以 总共人数的ratio呢 |
|
w******a
发帖数: 986 | 50 column1是A,B,C,D. Column2是对应的值。想把每个column2里的值所对应的A，B,C,D给
sum一下。比如下面这个简单的例子， A加起来16 （1+15），B为4。 这个怎么在excel
里实现。Thanks
col1 col2
A 1
A 15
B 2
C 5
D 6
B 2 |
|
