请教一条SQL查询语句

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 行受影响)
来源:英超直播

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值