表(T1,T2):T1 和T2 是1对多关系,CustID是主键
T1(ID,CustID)
ID CustID
1 A
2 B
3 C
4 D
5 ET2(ID,CustID,Name1, ANum)
ID CustID Name1 ANum
1 A John 1000
2 A Smith 800
3 A Marry 2000
4 B Bill 500
5 C Bob 500
6 C David 1000
7 C Jane 2000
8 C Tina 3000
9 E Betty 700
...怎样写语句名可以获得T1中存在的CustID在T2表中取出按ANum降序取前2个Name1,如下结果:
ID CustID Name1 ANum
1 A John 1000
3 A Marry 2000
4 B Bill 500
8 C Tina 3000
7 C Jane 2000
7 E Betty 700 以上结果怎样写Sql语句?
T1(ID,CustID)
ID CustID
1 A
2 B
3 C
4 D
5 ET2(ID,CustID,Name1, ANum)
ID CustID Name1 ANum
1 A John 1000
2 A Smith 800
3 A Marry 2000
4 B Bill 500
5 C Bob 500
6 C David 1000
7 C Jane 2000
8 C Tina 3000
9 E Betty 700
...怎样写语句名可以获得T1中存在的CustID在T2表中取出按ANum降序取前2个Name1,如下结果:
ID CustID Name1 ANum
1 A John 1000
3 A Marry 2000
4 B Bill 500
8 C Tina 3000
7 C Jane 2000
7 E Betty 700 以上结果怎样写Sql语句?
(select * from tb t where id in
( select top 2 id from tb where CustID=t.CustID order by ANum desc) )b
where a.CustID=b.CustID
select b.* from t1 a,t2 b where a.custid=b.custid
and t2.ANum in (select top 2 ANum from t2 where CustID=b.CustID order by ANum desc)
--modify
select b.* from t1 a,t2 b where a.custid=b.custid
and b.ANum in (select top 2 ANum from t2 where CustID=b.CustID order by ANum desc)
insert @t1 select 1 ,'A'
insert @t1 select 2 , 'B'
insert @t1 select 3 , 'C'
insert @t1 select 4 , 'D'
insert @t1 select 5, 'E'
declare @T2 table(ID int,CustID varchar(10),Name1 varchar(10), ANum int)
insert @t2 select 1 ,'A', 'John' , 1000
insert @t2 select 2 ,'A' , 'Smith' ,800
insert @t2 select 3 ,'A' , 'Marry' ,2000
insert @t2 select 4 ,'B' , 'Bill' , 500
insert @t2 select 5 , 'C' , 'Bob' ,500
insert @t2 select 6 , 'C' , 'David' ,1000
insert @t2 select 7 , 'C' , 'Jane' , 2000
insert @t2 select 8, 'C' , 'Tina' , 3000
insert @t2 select 9, 'E' ,'Betty', 700
select b.* from @t1 a,
(select * from @t2 t where id in
( select top 2 id from @t2 where CustID=t.CustID order by ANum desc) )b
where a.CustID=b.CustID
/*
ID CustID Name1 ANum
----------- ---------- ---------- -----------
1 A John 1000
3 A Marry 2000
4 B Bill 500
7 C Jane 2000
8 C Tina 3000
9 E Betty 700
*/
insert @t1 select 1 ,'A'
insert @t1 select 2 , 'B'
insert @t1 select 3 , 'C'
insert @t1 select 4 , 'D'
insert @t1 select 5, 'E'
declare @T2 table(ID int,CustID varchar(10),Name1 varchar(10), ANum int)
insert @t2 select 1 ,'A', 'John' , 1000
insert @t2 select 2 ,'A' , 'Smith' ,800
insert @t2 select 3 ,'A' , 'Marry' ,2000
insert @t2 select 4 ,'B' , 'Bill' , 500
insert @t2 select 5 , 'C' , 'Bob' ,500
insert @t2 select 6 , 'C' , 'David' ,1000
insert @t2 select 7 , 'C' , 'Jane' , 2000
insert @t2 select 8, 'C' , 'Tina' , 3000
insert @t2 select 9, 'E' ,'Betty', 700
select t.* from @t1 a,@t2 t where t.id in(
select top 2 id from @t2 where CustID=t.CustID order by ANum desc)
and a.CustID=t.CustID
/*
ID CustID Name1 ANum
----------- ---------- ---------- -----------
1 A John 1000
3 A Marry 2000
4 B Bill 500
7 C Jane 2000
8 C Tina 3000
9 E Betty 700
*/
insert @t1 select 1 ,'A'
insert @t1 select 2 , 'B'
insert @t1 select 3 , 'C'
insert @t1 select 4 , 'D'
insert @t1 select 5, 'E'
declare @T2 table(ID int,CustID varchar(10),Name1 varchar(10), ANum int)
insert @t2 select 1 ,'A', 'John' , 1000
insert @t2 select 2 ,'A' , 'Smith' ,800
insert @t2 select 3 ,'A' , 'Marry' ,2000
insert @t2 select 4 ,'B' , 'Bill' , 500
insert @t2 select 5 , 'C' , 'Bob' ,500
insert @t2 select 6 , 'C' , 'David' ,1000
insert @t2 select 7 , 'C' , 'Jane' , 2000
insert @t2 select 8, 'C' , 'Tina' , 3000
insert @t2 select 9, 'E' ,'Betty', 700 select m.id,m.custid,name1,ANum from @T1 t cross apply (select top 2 * from @t2 where CustID=t.CustID order by ANum desc) m order by id/*
id custid name1 ANum
----------- ---------- ---------- -----------
1 A John 1000
3 A Marry 2000
4 B Bill 500
7 C Jane 2000
8 C Tina 3000
9 E Betty 700(6 行受影响)*/
if object_id('[t1]') is not null drop table [t1]
create table [t1] (ID int,CustID varchar(1))
insert into [t1]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E'
--> 测试数据: [t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2] (ID int,CustID varchar(1),Name1 varchar(5),ANum int)
insert into [t2]
select 1,'A','John',1000 union all
select 2,'A','Smith',800 union all
select 3,'A','Marry',2000 union all
select 4,'B','Bill',500 union all
select 5,'C','Bob',500 union all
select 6,'C','David',1000 union all
select 7,'C','Jane',2000 union all
select 8,'C','Tina',3000 union all
select 9,'E','Betty',700--根本就不需要关联t1
select * from t2 a where ID in(select top 2 id from t2 where custid=a.custid order by anum desc)
drop table t1
go
create table t1(ID int,CustID varchar(2))
insert into t1 select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'D'
union all select 5,'E' if object_id('t2') is not null
drop table t2
go
create table t2(ID int , CustID varchar(2), Name1 varchar(20), ANum money)
insert into t2 select 1, 'A ', 'John', 1000
union all select 2, 'A ', ' Smith ', 800
union all select 3, 'A ', ' Marry', 2000
union all select 4, 'B ', ' Bill', 500
union all select 5, 'C ', ' Bob ', 500
union all select 6, 'C ', ' David ', 1000
union all select 7, 'C ', ' Jane', 2000
union all select 8, 'C ', 'Tina ', 3000
union all select 9, 'E ', 'Betty ', 700 select a.* from (
select * from t2 as t where t.id in (select top 2 id from t2 where CustID=t.CustID order by ANum desc)
)a,t1
where t1.CustID=a.CustID
/*ID CustID Name1 ANum
----------- ------ -------------------- ---------------------
1 A John 1000.00
3 A Marry 2000.00
4 B Bill 500.00
7 C Jane 2000.00
8 C Tina 3000.00
9 E Betty 700.00(6 行受影响)*/
drop table [t1]
go
create table [t1] (ID int,CustID varchar(1))
insert into [t1]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E'
--> 测试数据: [t2]
if object_id('[t2]') is not null
drop table [t2]
go
create table [t2] (ID int,CustID varchar(1),Name1 varchar(5),ANum int)
insert into [t2]
select 1,'A','John',1000 union all
select 2,'A','Smith',800 union all
select 3,'A','Marry',2000 union all
select 4,'B','Bill',500 union all
select 5,'C','Bob',500 union all
select 6,'C','David',1000 union all
select 7,'C','Jane',2000 union all
select 8,'C','Tina',3000 union all
select 9,'E','Betty',700select *
from T2 k
where ID in (select top 2 t2.ID
from t2 join t1 on t2.custid=t1.custid and k.CustID=t1.custid order by ANum desc)/*(5 行受影响)(9 行受影响)
ID CustID Name1 ANum
----------- ------ ----- -----------
1 A John 1000
3 A Marry 2000
4 B Bill 500
7 C Jane 2000
8 C Tina 3000
9 E Betty 700(6 行受影响)*/
drop table t1
go
create table t1(ID int,CustID varchar(2))
insert into t1 select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'D'
union all select 5,'E' if object_id('t2') is not null
drop table t2
go
create table t2(ID int , CustID varchar(2), Name1 varchar(20), ANum money)
insert into t2 select 1, 'A ', 'John', 1000
union all select 2, 'A ', ' Smith ', 800
union all select 3, 'A ', ' Marry', 2000
union all select 4, 'B ', ' Bill', 500
union all select 5, 'C ', ' Bob ', 500
union all select 6, 'C ', ' David ', 1000
union all select 7, 'C ', ' Jane', 2000
union all select 8, 'C ', 'Tina ', 3000
union all select 9, 'E ', 'Betty ', 700 select a.* from (
select * from t2 as t where t.id in (select top 2 id from t2 where CustID=t.CustID order by ANum desc)
)a,t1
where t1.CustID=a.CustID
/*ID CustID Name1 ANum
----------- ------ -------------------- ---------------------
1 A John 1000.00
3 A Marry 2000.00
4 B Bill 500.00
7 C Jane 2000.00
8 C Tina 3000.00
9 E Betty 700.00 (6 行受影响) */
我都到9楼了,水哥真神速呀,敬佩
drop table t1
go
create table t1(ID int,CustID varchar(2))
insert into t1 select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'D'
--union all select 5,'E' --注释这行,效果更明显if object_id('t2') is not null
drop table t2
go
create table t2(ID int , CustID varchar(2), Name1 varchar(20), ANum money)
insert into t2 select 1, 'A ', 'John', 1000
union all select 2, 'A ', ' Smith ', 800
union all select 3, 'A ', ' Marry', 2000
union all select 4, 'B ', ' Bill', 500
union all select 5, 'C ', ' Bob ', 500
union all select 6, 'C ', ' David ', 1000
union all select 7, 'C ', ' Jane', 2000
union all select 8, 'C ', 'Tina ', 3000
union all select 9, 'E ', 'Betty ', 700 --如果ID不唯一时:select a.* from (
select * from t2 as t where 2> (select count(*) from t2 where CustID=t.CustID and ANum>t.ANum)
)a,t1
where t1.CustID=a.CustID
/*ID CustID Name1 ANum
----------- ------ -------------------- ---------------------
1 A John 1000.00
3 A Marry 2000.00
4 B Bill 500.00
7 C Jane 2000.00
8 C Tina 3000.00(5 行受影响)*/
--如果楼主是SQL2005,可以这样
--> 测试数据: [t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1] (ID int,CustID varchar(1))
insert into [t1]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E'
--> 测试数据: [t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2] (ID int,CustID varchar(1),Name1 varchar(5),ANum int)
insert into [t2]
select 1,'A','John',1000 union all
select 2,'A','Smith',800 union all
select 3,'A','Marry',2000 union all
select 4,'B','Bill',500 union all
select 5,'C','Bob',500 union all
select 6,'C','David',1000 union all
select 7,'C','Jane',2000 union all
select 8,'C','Tina',3000 union all
select 9,'E','Betty',700
select * from
(
select T2.ID,T2.CustID,T2.Name1,T2.ANum,
row_number() over (partition by T2.CustID order by T2.ANum Desc) as rownum
from T1 join T2 on T1.CustID=T2.CustID
) T where RowNum <=2
(select top 2 id from t2 where CustID = t.CustID order by ANum desc)
--方法一
select * from t2 tb
where id in
(
select top 2 t2.ID from t2
inner join t1
on t2.CustID=t1.CustID and tb.CustID=t1.CustID --此处条件尤为重要
order by ANum )
--方法二 (??)
select * from t2 as t where 2>
(select count(*) from t2 where custid=t.custid and anum>t.anum)
and custid in (select custid from t1)
--方法三 (最为方便)
select * from t2 t where ID in
(select top 2 ID from t2 where CustID=t.CustID order by ANum )
--方法四(?)
select b.* from t1 a,t2 b
where a.custid=b.custid
and b.ANum in (select top 2 ANum from t2 where CustID=b.CustID order by ANum desc)
如:select * from t2 as t where 2>
(select count(*) from t2 where custid=t.custid and anum>t.anum)
and custid in (select custid from t1)让我很费解,如果作者在这里 看到,请帮忙解答下。