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语句怎么写?
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语句怎么写?
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 行受影响)
*/
left outer join
(select Team,Item,Serial,count(0) as tt from dbo.TB group by Team,Item,Serial having count(0)>1) b
on a.Team=b.Team and a.Item=b.Item and a.Serial=b.Serial
where tt is not null这个能用,不过效率是完蛋了..下午再改改,先吃饭去
A A101 L001 0603-E 2010-9-10
A A102 L002 0603-E 2010-9-10
A A103 L001 0603-E 2010-9-11
这四个数据,把相同Team,Item,Serial,不同Sdate取出来,要求是A100和A103被取出,为什么A101不会取出?有点儿不合逻辑..
select distinct * from table1 order by Team,PO,Item,Serial,Sdate
这方法是可以基本实现了,但是不能把相同team,item,serial,sdate只取一条;这方法会把所有相同team,item,serial,sdate的都取出
select distinct a.Team,a.Item,a.Serial,a.sdate from tb a,tb b where
a.Team=b.Team and a.Item=b.Item and a.Serial=b.Serial and a.sdate<>b.sdate
a.Team=b.Team and a.Item=b.Item and a.Serial=b.Serial and a.sdate<>b.sdate group by a.Team,a.Item,a.Serial,a.sdate