1.根据如下表的查询结果,那么以下语句的结果是?
SQL>select* from usertable
USERID USERNAME
------ --------
1 user1
2 null
3 user3
4 null
5 user5
6 user6SQL>select * from usergrade
USERID USERNAME GRADE
------ -------- -----
1 user1 90
2 null 80
7 user7 80
8 user8 90
执行语句:
select count(*) from usergrade where username not in (select username from usertable)
结果为:()
A:0 B:1 C:2 D:3 E:NULL2.根据要求写Sql语句(不能使用游标和子查询)
Table:Sales
---------------------------- -----------------------------------------
| Person | Year | Amount | | Person | Year | Amount | Last_Year |
---------------------------- -----------------------------------------
| Crane | 2005 | 516 | | Crane | 2005 | 516 | null |
---------------------------- -----------------------------------------
| Crane | 2006 | 983 | | Crane | 2006 | 983 | 516 |
---------------------------- -----------------------------------------
| Crane | 2007 | 1238 | | Crane | 2007 | 1238 | 983 |
---------------------------- -----------------------------------------
| Crane | 2005 | 790 | | Crane | 2005 | 790 | null |
---------------------------- -----------------------------------------
| Crane | 2006 | 981 | | Crane | 2006 | 981 | 790 |
---------------------------- -----------------------------------------
| Crane | 2007 | 1381 | | Crane | 2007 | 1381 | 981 |
---------------------------- -----------------------------------------
根据左边的表生成右边的结果
小生在此感谢大家了。
SQL>select* from usertable
USERID USERNAME
------ --------
1 user1
2 null
3 user3
4 null
5 user5
6 user6SQL>select * from usergrade
USERID USERNAME GRADE
------ -------- -----
1 user1 90
2 null 80
7 user7 80
8 user8 90
执行语句:
select count(*) from usergrade where username not in (select username from usertable)
结果为:()
A:0 B:1 C:2 D:3 E:NULL2.根据要求写Sql语句(不能使用游标和子查询)
Table:Sales
---------------------------- -----------------------------------------
| Person | Year | Amount | | Person | Year | Amount | Last_Year |
---------------------------- -----------------------------------------
| Crane | 2005 | 516 | | Crane | 2005 | 516 | null |
---------------------------- -----------------------------------------
| Crane | 2006 | 983 | | Crane | 2006 | 983 | 516 |
---------------------------- -----------------------------------------
| Crane | 2007 | 1238 | | Crane | 2007 | 1238 | 983 |
---------------------------- -----------------------------------------
| Crane | 2005 | 790 | | Crane | 2005 | 790 | null |
---------------------------- -----------------------------------------
| Crane | 2006 | 981 | | Crane | 2006 | 981 | 790 |
---------------------------- -----------------------------------------
| Crane | 2007 | 1381 | | Crane | 2007 | 1381 | 981 |
---------------------------- -----------------------------------------
根据左边的表生成右边的结果
小生在此感谢大家了。
from sales s1 left outer join sales s2 on s1.year = s2.year + 1
--测试数据
DECLARE @1 TABLE(col1 int)
INSERT @1 SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT 2DECLARE @2 TABLE(col1 int)
INSERT @2 SELECT 1--查询
SELECT [@1总记录数]=COUNT(*) FROM @1
--结果: 3SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 1--在@2中插入一条NULL值
INSERT @2 SELECT NULL
SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 0
GO
--2. 使用 EXISTS 代替IN
--测试数据
DECLARE @1 TABLE(col1 int)
INSERT @1 SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT 2DECLARE @2 TABLE(col1 int)
INSERT @2 SELECT 1
UNION ALL SELECT NULLSELECT [@1在@2表中存在的记录数]=COUNT(*)
FROM @1 a
WHERE EXISTS(SELECT * FROM @2 WHERE col1=a.col1)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*)
FROM @1 a
WHERE NOT EXISTS(SELECT * FROM @2 WHERE col1=a.col1)
--结果: 2这样可以说明选A了.
not in 后有null就查询就查不到记录, 这是一个常见的陷阱.