d*******n
发帖数: 109 | 1 create table table1 (id int , description varchar(20))
insert into table1 values (1, '0-2')
insert into table1 values (2, '2-5')
insert into table1 values (3, '5-10')
insert into table1 values (4, 'more than 10')
create table table2 (userid int, id int)
insert into table2 (userid, id) values (1,1)
insert into table2 (userid, id) values (2,NULL)
insert into table2 (userid, id) values (3,4)
insert into table2 (userid, id) values (4,3)
insert into table2 (userid, id) values (5,NULL)
insert into tab... 阅读全帖 |
|
g***x
发帖数: 494 | 2 有两个表分别有1024和2048个0到4000的数（不一定是整数），这两个表都是递增的。
并且表里的值都是已知的，两个表的第一个元素都是0，最后一个元素都是4000例如
table1[0]=0;table1[1]=0.5;table1[2]=1; table1[3]=2; ...... table1[1023]=4000
table2[0]=0;table2[1]=0.2;table2[2]=0.5;table2[3]=0.75;table2[4]=1.1; table2
[5]=2; ... table2[2047]=4000
现在的问题是如何从table2中挑选出1024个index使得这1024个值所组成的曲线和
table1的曲线最好地一致。
比如上面的例子中可从表2中挑选出第 0，2，4,5,... 2047个元素得到的数是：
0，0.5，1.1，2，... 4000这和表1的元素0,0.5,1,2,4000符合的很好。
请问可以用什么算法来实现，并且最快。 |
|
l******9
发帖数: 579 | 3 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to combine two tables into one. Ans also, add a column (assign an int
value) to the new table on SQL. So that the rows from table1 and ones from
table2 are assigned with different values.
Example,
table1
ID1 ID2 ID3 VALUE
table2
ID1 ID2 ID3 VALUE
table3
ID1 ID2 ID3 VALUE
i need to combine ... 阅读全帖 |
|
p*c
发帖数: 421 | 4 declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id
where emp_id = 1
select @emp_code
this does return for Mary who is emp_id=1:
a b c
however, if i supply more emp_id in the where clause, for example:
declare @emp_code varchar(1000)
select @emp_code = COALESCE(@emp_code + ' ', '') + table2.emp_code
from table1 table1
LEFT JOIN table2 table2 ON table1.emp_id = table2.emp_id |
|
h****t
发帖数: 22 | 5
首先声明，我是作应用程序开发的，在这个版上贴query是在班门弄斧，让大家见笑了。
CREATE TABLE table1(id int,InheritedFromID int)
CREATE TABLE table2(id int,prop1 nvarchar(50),prop2 nvarchar(50))
CREATE TABLE table3(id int,prop1 nvarchar(50),prop2 nvarchar(50))
insert into table1 (id,InheritedFromID) values(1,0)
insert into table1 (id,InheritedFromID) values(2,1)
insert into table1 (id,InheritedFromID) values(3,2)
insert into table2(id,prop1,prop2) values(1,'a0','a1')
insert into table2(id,prop1,prop2) values(1,'a2','a3')
insert into... 阅读全帖 |
|
l******9
发帖数: 579 | 6 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
s*****n
发帖数: 2174 | 7 看到不同人写的sql语句用不用的join方法, 比如下面这两种写法:
SELECT
table1.id
FROM
table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table1.id = table3.id
INNER JOIN table4 ON table1.id = table4.id
WHERE
table1.value > 0
AND table2.value > 0
AND table3.value > 0
AND table4.value > 0;
SELECT
table1.id
FROM
table1
INNER JOIN table2 ON table1.id = table2.id
AND table2.value > 0
INNER JOIN table3 ON table1.id = table3.id
AND table3.value > 0
INNER JOIN table4 ON table |
|
l******9
发帖数: 579 | 8 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
l******9
发帖数: 579 | 9 【 以下文字转载自 Statistics 讨论区 】
发信人: light009 (light009), 信区: Statistics
标 题: SQL check likeness between two large tables
发信站: BBS 未名空间站 (Tue May 6 15:38:29 2014, 美东)
I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx ... 阅读全帖 |
|
l******9
发帖数: 579 | 10 I need to check the likeness between two data tables on SQL. I am working on
Aginity Workbench for Netezza on Win 7.
The tables are very large. One of them has 100 million rows and 4 columns;
another one has 1500 million rows and 3 columns.
Example, table1
ID1 ID2 ID3 Value
xxxx xxxxxx xxxxxxxx xxx.xxxxxx // here x is 0-9 int
table2:
ID1 ID2 Value
xxxx xxxxxx xxx.xxxxxx
the ID1 and ID2 may be duplicated but Values are not duplicated in the sam... 阅读全帖 |
|
a*******t
发帖数: 891 | 11 double check your query
select distinct table1.A, table1.B --you really mean to pull distinct only?
from table1, table2
where table1.A=RXs.table2.A --what is RXs?
AND table2.B != table2.B --you mean table1.B <> table2.B
order by A --you didn't get an error with this? need to provide table name |
|
m******y
发帖数: 588 | 12 SQL SERVER 2000:
SELECT t1.A, t1.B
FROM table1 t1
LEFT OUTER JOIN table2 t3 ON t1.A=t3.A AND t1.B=t3.B
WHERE t3.A IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.A=t1.A)
2005:
SELECT A, B
FROM
(
SELECT A,B FROM table1 t1
EXCEPT
SELECT A,B FROM table2 t2
) t3
WHERE EXISTS (SELECT 1 FROM table2 WHERE t3.A=table2.A) |
|
c*******e
发帖数: 8624 | 13 具体query;
select A, B from table1
minus
select A, B from table2
或者
select table1.A, table1.B
from table1
left outer join table2
on table1.A = table2.A
and table1.B = table2.B
where table2.B is null ; |
|
r*g
发帖数: 3159 | 14 SELECT *
FROM table1 LEFT OUTER JOIN table2
ON table1.ID1 = table2.ID1 AND table1.ID2 = table2.ID2
WHERE table2.ID1 IS NULL
OR table2.ID2 IS NULL |
|
s*z
发帖数: 132 | 15 You can not use set to merge columnwise in data step. Set is used to do
rowwise union.
search for 'sas data step merge', and try something like this:
proc sort data = table1;
by x y z;
proc sort data = table2;
by x y z;
data new_table;
merge table1 table2;
by x y z;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z
then num_new = num + A;
else num_new = num + B;
run; |
|
l******9
发帖数: 579 | 16 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
s******r
发帖数: 1524 | 17 update table2 set table2.maxday = (select max(day) from table1);
Table2 have only one record. table1 have more than 10,000 records. Want to get
max(date) and update table2.
run it and got error message:
"Operation Must Be Updatable Query". I know it is the link problem. No idea ho
w to deal with it or is there any other way to work on it? Thanks. Help please
. |
|
x****e
发帖数: 55 | 18 我在自己的PC上用MYSQL做了一下测试，
对于都是5万条记录的两张表 table1, table2
第一个方法：
select * from table1, table2 where table1.ID = table2.ID;
大约需要600秒
第二个方法：
create index table1_index on table1 (id);
create index table2_index on table2 (id);
SELECT * FROM (SELECT ID FROM TB1) A INNER JOIN (SELECT ID FROM TB2) B ON (A
.ID = B.ID)
大约需要350秒
的确快了
第三个方法：
用JAVA编程，随机生成两个size都是5万的array
先分别对两个array排序（从小到大），然后从小比到大，时间复杂度是2×50000
最终运算只需要不到1秒 ！！！
为什么数据库的效率会比JAVA的程序慢那么多？
哪位兄台能否解释一下？
多谢 |
|
I******e
发帖数: 101 | 19 You basically need sort merge join or hash join to do this fast. But, MySQL
does not support any of these. Do you have other databases?
After you create index, can you do:
explain select * from table1, table2 where table1.ID = table2.ID;
and tell us the resule.
BTW, if you output a lot of records, to measure performance, you should do:
select count(*) from table1, table2 where table1.ID = table2.ID;
(A |
|
w*********g
发帖数: 2330 | 20 两个table, table1, table2 . 每个table各有两列A 和 B. 。 如何从table 1 选择出，
table1.A 和table2.A相等，在这个条件下，B不等的那些行。
想选择出如下的行
A B
835448 3
835448 4
835448 5
835449 2
835449 3
table1 的数据
A B
835448 0
835448 1
835448 2
835448 3
835448 4
835448 5
835449 0
835449 1
835449 2
835449 3
table2的数据
A B
835448 0
835448 1
835448 2
835449 0
835449 1
我写的语句
select distinct table1.A, table1.B
from table1, table2
where table1.A |
|
c*******e
发帖数: 8624 | 21 你这个query有问题,
table1:
A B
835448 0
table2:
A B
835448 0
835448 1
这里table1和table2第二个record满足你的条件
但是实际上table2里面有table1里面的record
记住在A相等的情况下,table2里面只要有一条记录不等,
table1里面那条就会被pull进来
一般你可以用minus或者outer join + where ... is null
出， |
|
m******y
发帖数: 588 | 22 SELECT t1.A, t1.B
FROM table1 t1
LEFT OUTER JOIN table2 t3 ON t1.A=t3.A AND t1.B=t3.B
WHERE t3.A IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.A=t1.A)
LEFT OUTER JOIN plus NULL criteria 就等于找table1里面与table2 A和B value有不
相同的records. 这个同2005的except是一样的。
Exists 那部分找table1里面A value也在table2里面的records.
两个合起来就是你要的了。 |
|
l******9
发帖数: 579 | 23 need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 col4
abc 346 6546 1
hth 549 974 0
Table2:
col1
abc
sfsdf
If Table2's col1 appear in Table1 col1, mark col4 as 1 in Table1_new, el... 阅读全帖 |
|
Y***e
发帖数: 1030 | 24 在不同的资料里看到Alias和join 不同的写法，请教各位前辈是怎么回事。是在不同的
环境下的区别嘛？
1 Alias: 一种带as, 一种不带
SELECT column_name AS alias_column_name
FROM table_name As alias table_name;
和
SELECT column_name alias_column_name
FROM table_name alias table_name;
为什么我在各种教材里看到这两种写法？
2 join: 一种写join... on.., 一种就用where
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
和
SELECT table1_column_name_1, table2_column_name_2
FROM table1， table2
where table1.column_name=table2.column_name;
而且下面只用where 这种写... 阅读全帖 |
|
S****e
发帖数: 10596 | 25 cmd = "SELECT table2.item FROM table2 INNER JOIN table1 ON table1.ID=table2.
ID WHERE !@#!@#!@#!@$^%$%^$% "
有以上一个sql查询语句，目的是通过table1中的条件，选取合适的ID
再通过join ID，在table2中对号入座找到item
现在的问题是，为了方便查询，ID 在table1里面并不唯一，有可能查询到两行具有相同
的ID，然后显示出来的item就重复了
有什么简单方法能够只显示一个ID如果重复
不知道是否说明白
先谢了！ |
|
l******9
发帖数: 579 | 26 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
b*****r
发帖数: 359 | 27 有两个表格，如图 table1 和 table2。
要执行的操作：
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A (A is a constant)
else
num_new = num + B (B is another constant)
也就是说，表格1里面有5万条数据，表格2里面有100条数据。
只有当表格1里面的 x,y 和 z 同时等于 表格2里面的x y z
的时候， num_new = num + A; 否则， num_new = num+B.
最后得到的新表格的行数和表格1 是一样多的，也是5万条。
怎样实现呀？
使用 data step 语句 还是 proc SQL ?
谢谢 |
|
b*****r
发帖数: 359 | 28 like this?
data new_table; set table1 table2;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A;
else
num_new = num + B;
run;
这个出错：
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable table1 is not an object. |
|
l******9
发帖数: 579 | 29 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL copy a table into a new table and add a new column
发信站: BBS 未名空间站 (Fri May 23 12:05:22 2014, 美东)
need to copy a table into a new table on SQL server 2008. Also, add a new
column into the new table.
The values of the new column depends on the compare result between the new
table and another table.
Example,
Table1:
col1 col2 col3
abc 346 6546
hth 549 974
Table1_new:
col1 col2 col3 c... 阅读全帖 |
|
h**k
发帖数: 3368 | 30 如果两条曲线最好的一致，是定义为sum( abs(table1[i] - table2[the i-th chose
element]) )最小么？
如果这样可以找到一个线性的算法。
同时开始扫描两个数组，对于table1中的每个值a，可以只需要计算table2中的两个值
与它的差，这两个值一个是最大的比a小的值，一个是最小的比a大的值。
4000
table2 |
|
m*********u
发帖数: 1491 | 31 SELECT *
FROM
(
SELECT *,'80' as top_id
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a
union all
(
SELECT *,'81' as top_id
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b
GROUP BY top_id, id2, id3 |
|
l******9
发帖数: 579 | 32 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
l******9
发帖数: 579 | 33 I need to so a sql query on IBM netezza sql database from Aginity workbench
on win7.
My query:
SELECT *
from table1 AS c ,
table2 AS b
where CAST(c.id as int) = b.id
in table1, id is character varying(20) and in table2, id is int.
Table1:
id value1 value2
'985' 'casdqwdc' '654.3184' // they are all char
Table2:
id value1
985 694381 // id is int, value1 is int
I got error:
ERROR [HY000] ERROR: pg_atoi: error in "id": can't parse "id"
Any help would be apprec... 阅读全帖 |
|
t******t
发帖数: 51 | 34 Suppose I have 2 tables in the database (Oracle9):
create table table1 (table_spec XMLTYPE);
create table table2 (table_spec XMLTYPE);
Then somewhere in the table_spec of data entries of table1 I needed to link to
a node in the XML tree of table_spec in table2. How can I do it? For example,
insert into table1 values (XMLTYPE ('
.....
'));
I understand that XPath can be used here, but exactly how? Especially how to
refer to table2, the path within the XMLTYPE |
|
x****e
发帖数: 55 | 35
MySQL
sort merge join正是我想要的，是否只有ORACLE才有这个命令？
结果是：
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE table1 index table1_index table1_index 9
47506 Using index
1 SIMPLE table2 ref table2_index table2_index 9 codis.
table1.id 1 Using where; Using index
似乎采用这个命令，速度非常快，也是不到1秒
这个跟 create table jointable select table1.id from table1, table2 where
table1.ID = table2.ID; 好像一样的快
但select table1.i |
|
n****u
发帖数: 229 | 36 Sorry I can not write Chinese here.
In MySQL, I have two databases: newdb and olddb
table1 in newdb, table2 and table3 in olddb
table1 has userid, nickname, msn
table2 has userid, nickname
table3 has userid msn
First I use NaviCat to import table2 into table1, very fast! 320,000 records
in 2 mins!
Then I tried to import table 3 into table1, now I have to check userid first
, then UPDATE table1. Very slow. 50,000 records in 12 hours
Anyway I can make it fast?
Thanks |
|
a*******t
发帖数: 891 | 37 select table1.*
from table1, table2
where table1.A = table2.B
and table1.B <> table2.B
you can try it, not sure if it'll work
and it's recommanded to use "join" operator instead of implied join by using "where" causes |
|
e***e
发帖数: 1040 | 38 SELECT CustomerID, Inquery_code FROM table1
WHERE Inquerydate IN (SELECT max(inquerydate) FROM table1,table2 WHERE
table1.CustomerID = table2.CustomerID AND table1.inquerydate
orderdate GROUP BY table1.CustomerID)
包子 please,当然date那个可以自己改一下,懒得写了 |
|
e*********y
发帖数: 29 | 39 比如说
table1
a,b,c,d
a,d,c,b
table2
a,b,c,d
a,d,c,b
当table1里头任意一个有update,table2也会跟随update.在没有Key的情况下,就是说不
能join两个表. 请问还有没有其它最优解?(除了删除Table2里头的然后在一笔一笔
insert) |
|
l******9
发帖数: 579 | 40 I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 80
FROM
(
SELECT *
FROM table1
JOIN
table3
ON table1.id1 = table3.... 阅读全帖 |
|
l******9
发帖数: 579 | 41 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
l******9
发帖数: 579 | 42 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: Error of SQL query on IBM netezza SQL database from Aginity workbench
发信站: BBS 未名空间站 (Fri Dec 5 16:46:56 2014, 美东)
I need to so a sql query on IBM netezza sql database from Aginity workbench
on win7.
My query:
SELECT *
from table1 AS c ,
table2 AS b
where CAST(c.id as int) = b.id
in table1, id is character varying(20) and in table2, id is int.
Table1:
id value1 value2
'985' 'casdqwdc' '654.3184' // t... 阅读全帖 |
|
s**********o
发帖数: 14359 | 43 一般都是SELECT DISTINCT * into table2 from table1
然后吧TABLE2的 KEY INDEX都弄好了，RENAME TABLE1 TO TABLE3
RENAME TABLE2 TO TABLE1，这样是最快的，可以避免BLOCK |
|
l******9
发帖数: 579 | 44 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: Error of SQL query on IBM netezza SQL database from Aginity workbench
发信站: BBS 未名空间站 (Fri Dec 5 16:46:56 2014, 美东)
I need to so a sql query on IBM netezza sql database from Aginity workbench
on win7.
My query:
SELECT *
from table1 AS c ,
table2 AS b
where CAST(c.id as int) = b.id
in table1, id is character varying(20) and in table2, id is int.
Table1:
id value1 value2
'985' 'casdqwdc' '654.3184' // t... 阅读全帖 |
|
t******t
发帖数: 51 | 45 Suppose I have 2 tables in the database (Oracle9):
create table table1 (table_spec XMLTYPE);
create table table2 (table_spec XMLTYPE);
Then somewhere in the table_spec of data entries of table1 I needed to link to
a node in the XML tree of table_spec in table2. How can I do it? For example,
insert into table1 values (XMLTYPE ('
.....
'));
I understand that XPath can be used here, but exactly how? Especially how to
refer to table2, the path within the XMLTYPE |
|
l******9
发帖数: 579 | 46 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, c... 阅读全帖 |
|
l******9
发帖数: 579 | 47 I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.i... 阅读全帖 |
|
p********r
发帖数: 1465 | 48 我是想这样做：
1、有一个叫table的数据，我随机把他分成两个，table1和table2（table1包含80%
table的数据，table2包含剩余的20%）
2、用table2做一些运算，用proc iml，然后把运算结果输出到result里面。
我想把这个两个步骤循环做1000遍，这样我的result表里面就会出来1000个结果。
请问应该如何做会比较好？ |
|
k***n
发帖数: 997 | 49 outer join can display difference
select t1id , t2id from
(select table1.id1 t1id , table2.id1 t2id from table1
full join table2 on (table1.id1=table2.id1) )
where t1id is null ; |
|
l******9
发帖数: 579 | 50 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to add a column with an int value to the result from a subquery on
SQL.
It is Netezza SQL.
SELECT *
FROM
(
SELECT *
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, c... 阅读全帖 |
|
