请教一条SQL查询语句

来源：互联网发布：gif动态图制作软件编辑：程序博客网时间：2024/05/30 05:02

Team PO Item Serial Sdate
A A100 L001 0603-E 2010-9-10
A A101 L001 0603-E 2010-9-10
A A102 L002 0603-E 2010-9-10
A A103 L001 0603-E 2010-9-11
B B100 N001 0806-M 2010-9-10
B B101 N001 0806-M 2010-9-11
B B102 L001 0603-E 2010-9-12
C C001 K001 1111-F 2010-9-21
C C002 L001 0603-E 2010-9-22
C C003 N001 0806-M 2010-9-23
C C004 K001 1111-F 2010-9-24

把相同Team,Item,Serial,不同Sdate的数据取出,得表下面的表

Team PO Item Serial Sdate
A A100 L001 0603-E 2010-9-10
A A103 L001 0603-E 2010-9-11
B B100 N001 0806-M 2010-9-10
B B101 N001 0806-M 2010-9-11
C C001 K001 1111-F 2010-9-21
C C004 K001 1111-F 2010-9-24
这SQL语句怎么写?

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Team] varchar(1),[PO] varchar(4),[Item] varchar(4),[Serial] varchar(6),[Sdate] datetime)
insert [TB]
select 'A','A100','L001','0603-E','2010-9-10' union all
select 'A','A101','L001','0603-E','2010-9-10' union all
select 'A','A102','L002','0603-E','2010-9-10' union all
select 'A','A103','L001','0603-E','2010-9-11' union all
select 'B','B100','N001','0806-M','2010-9-10' union all
select 'B','B101','N001','0806-M','2010-9-11' union all
select 'B','B102','L001','0603-E','2010-9-12' union all
select 'C','C001','K001','1111-F','2010-9-21' union all
select 'C','C002','L001','0603-E','2010-9-22' union all
select 'C','C003','N001','0806-M','2010-9-23' union all
select 'C','C004','K001','1111-F','2010-9-24'

select * from [TB] T
    where not exists(select 1 from Tb where t.Team=Team and T.Item=Item and Serial=T.Serial and T.[Sdate]>[Sdate])

select
    Team,
    PO=min(PO),
    Item,Serial,
    Sdate=min(Sdate)
from tb
group by Team,Item,Serial

/*
Team PO   Item Serial Sdate
---- ---- ---- ------ -----------------------
A    A100 L001 0603-E 2010-09-10 00:00:00.000
A    A101 L001 0603-E 2010-09-10 00:00:00.000
A    A102 L002 0603-E 2010-09-10 00:00:00.000
B    B100 N001 0806-M 2010-09-10 00:00:00.000
B    B102 L001 0603-E 2010-09-12 00:00:00.000
C    C001 K001 1111-F 2010-09-21 00:00:00.000
C    C002 L001 0603-E 2010-09-22 00:00:00.000
C    C003 N001 0806-M 2010-09-23 00:00:00.000

(8 行受影响)

*/

drop table [TB]
你测试的结果跟我所要的结果不一致;
Team PO Item Serial Sdate
A A100 L001 0603-E 2010-9-10
A A103 L001 0603-E 2010-9-11
B B100 N001 0806-M 2010-9-10
B B101 N001 0806-M 2010-9-11
C C001 K001 1111-F 2010-9-21
C C004 K001 1111-F 2010-9-24
这是我想要的结果
你得到的结果是：(在字段Team中C组同Item,serial,不同sdate只有两条C C001 K001 1111-F 2010-9-21
C C004 K001 1111-F 2010-9-24
你的结果确是三条C记录item,serial下的数据都不同

*
Team PO Item Serial Sdate
---- ---- ---- ------ -----------------------
A A100 L001 0603-E 2010-09-10 00:00:00.000
A A101 L001 0603-E 2010-09-10 00:00:00.000
A A102 L002 0603-E 2010-09-10 00:00:00.000
B B100 N001 0806-M 2010-09-10 00:00:00.000
B B102 L001 0603-E 2010-09-12 00:00:00.000
C C001 K001 1111-F 2010-09-21 00:00:00.000
C C002 L001 0603-E 2010-09-22 00:00:00.000
C C003 N001 0806-M 2010-09-23 00:00:00.000

(8 行受影响)
来源：英超直播