已知
表A
AID Aname
2 a2
3 a3
4 a4 表B
BID Bname
1 b1
2 b2
3 b3
4 b4
5 b5
6 b6求一条SQL语句查询出以下结果BID Bname AID
3 b3 3
4 b4 4
5 b5 4
6 b6 4(求查询出B表中B.BID 比A.AID 大的结果,
但是如果BID同时大于多个AID,那么只取大于最大的AID的记录)
表A
AID Aname
2 a2
3 a3
4 a4 表B
BID Bname
1 b1
2 b2
3 b3
4 b4
5 b5
6 b6求一条SQL语句查询出以下结果BID Bname AID
3 b3 3
4 b4 4
5 b5 4
6 b6 4(求查询出B表中B.BID 比A.AID 大的结果,
但是如果BID同时大于多个AID,那么只取大于最大的AID的记录)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:12:38
---------------------------------
--> 生成测试数据表:AIf not object_id('[A]') is null
Drop table [A]
Go
Create table [A](AID nvarchar(3),Aname nvarchar(5))
Insert A
Select '2','a2' union all
Select '3','a3' union all
Select '4','a4'
Go
--Select * from A--> 生成测试数据表:BIf not object_id('[B]') is null
Drop table [B]
Go
Create table [B](BID nvarchar(3),Bname nvarchar(5))
Insert B
Select '1','b1' union all
Select '2','b2' union all
Select '3','b3' union all
Select '4','b4' union all
Select '5','b5' union all
Select '6','b6'
Go
--Select * from B-->SQL查询如下:
select * from B where exists(select 1 from A where aid<B.bid)
/*
BID Bname
---- -----
3 b3
4 b4
5 b5
6 b6(4 行受影响)
*/
3 b3 3
4 b4 4
5 b5 4
6 b6 4 是B和A表都有取的字段 ,请看上面的结果, BID Bname是B表的,AID是A表的,
结果中当BID=3的时候 AID不应该等于3 应该等于2吧
ADI=4,5,6的时候 应该等于3吧
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:12:38
---------------------------------
--> 生成测试数据表:AIf not object_id('[A]') is null
Drop table [A]
Go
Create table [A](AID nvarchar(3),Aname nvarchar(5))
Insert A
Select '2','a2' union all
Select '3','a3' union all
Select '4','a4'
Go
--Select * from A--> 生成测试数据表:BIf not object_id('[B]') is null
Drop table [B]
Go
Create table [B](BID nvarchar(3),Bname nvarchar(5))
Insert B
Select '1','b1' union all
Select '2','b2' union all
Select '3','b3' union all
Select '4','b4' union all
Select '5','b5' union all
Select '6','b6'
Go
--Select * from B-->SQL查询如下:
select *,
aid=(select max(aid) from a where aid<=bid)
from B
where exists(select 1 from A where a.aid<B.bid)/*
BID Bname aid
---- ----- ----
3 b3 3
4 b4 4
5 b5 4
6 b6 4(4 行受影响)
*/
Drop table [A]
Go
Create table [A](AID nvarchar(3),Aname nvarchar(5))
Insert A
Select '2','a2' union all
Select '3','a3' union all
Select '4','a4'
Go
--Select * from A--> 生成测试数据表:BIf not object_id('[B]') is null
Drop table [B]
Go
Create table [B](BID nvarchar(3),Bname nvarchar(5))
Insert B
Select '1','b1' union all
Select '2','b2' union all
Select '3','b3' union all
Select '4','b4' union all
Select '5','b5' union all
Select '6','b6'
Goselect bid,bname,(select max(aid) from a where aid <=bid) aid from b where b.bid>=3/*bid bname aid
---- ----- ----
3 b3 3
4 b4 4
5 b5 4
6 b6 4(所影响的行数为 4 行)*/