c*********u
发帖数: 607 | 1 code如下：
create table nt (x int, y int);
insert into nt values (10, 10);
insert into nt values (10, 20);
insert into nt values (20, 10);
insert into nt values (30, 40);
insert into nt values (30, 50);
insert into nt values (30, 60);
insert into nt values (40, 70);
select * FROM nt WHERE exists
(SELECT t.* FROM nt t WHERE nt.x = t.x AND nt.y > t.y) ;
select * FROM nt WHERE exists
(SELECT nt.* FROM nt t, nt nt WHERE nt.x = t.x AND nt.y > t.y) ;
网上跑SQL的结果的链接在这里：
http://ideone.com/3CtqN9
第一个query的结果是
10|20
30|50
30|60
第二个query的结果是
10|10
10|20
20|10
30|40
30|50
30|60
40|70
我的问题是：
1，我不理解为什么第二个query的结果是全部row都选中？
2，如果用SAS来泡，第一个query是没有结果的，但是如果把data输入两遍，变成nt，t
两个表格，就和ideone的第一个query跑出来的一样了，请问为什么？
非常感谢，回答的人我会尽量发包子的！（看我自己包子有多少。。。） | O***T
发帖数: 124 | 2 In your second query, the subquery
SELECT nt.* FROM nt t, nt nt WHERE nt.x = t.x AND nt.y > t.y
doesn't have any join with you main query, alias nt only refer to the table
in your subquery.
It can be rewrite to SELECT a.* FROM nt t, nt a WHERE a.x = t.x AND a.y > t.
y
In this way, it is very obvious there is no join between your subquery with
the main one. Exist function here only plays a role like checking if the
statement is correct.
So be careful with the alias. Don't use the same one. It will be quite
confusing... | O***T
发帖数: 124 | 3 I only know SQL, don't know SAS. So I can only answer your first one. | t*****w
发帖数: 254 | 4 excellent job!
table
t.
with
【在 O***T 的大作中提到】
: In your second query, the subquery
: SELECT nt.* FROM nt t, nt nt WHERE nt.x = t.x AND nt.y > t.y
: doesn't have any join with you main query, alias nt only refer to the table
: in your subquery.
: It can be rewrite to SELECT a.* FROM nt t, nt a WHERE a.x = t.x AND a.y > t.
: y
: In this way, it is very obvious there is no join between your subquery with
: the main one. Exist function here only plays a role like checking if the
: statement is correct.
: So be careful with the alias. Don't use the same one. It will be quite
|
|
