表sphwph表如下图
spid pihao hw sxrq
1 110 1 2012-01
1 112 1 2012-02
1 156 1 2012-05
1 110 2 2012-01
1 112 2 2012-02
2 114 1 2012-01
2 115 1 2012-02
2 110 2 2012-01
2 112 2 2012-02
3 132 1 2012-03要求就是同种商品,同一货位显示sxrq最小的一个语句应该这么写呢 应该达到的效果是
spid pihao hw sxrq
1 110 1 2012-01
1 110 2 2012-01
2 114 1 2012-01
2 110 2 2012-01
3 132 1 2012-03
spid pihao hw sxrq
1 110 1 2012-01
1 112 1 2012-02
1 156 1 2012-05
1 110 2 2012-01
1 112 2 2012-02
2 114 1 2012-01
2 115 1 2012-02
2 110 2 2012-01
2 112 2 2012-02
3 132 1 2012-03要求就是同种商品,同一货位显示sxrq最小的一个语句应该这么写呢 应该达到的效果是
spid pihao hw sxrq
1 110 1 2012-01
1 110 2 2012-01
2 114 1 2012-01
2 110 2 2012-01
3 132 1 2012-03
where not exists(select 1 from tb where pihao=t.pihao and hw=t.hw and sxrq<t.sxrq )
create table sphwph
(spid int, pihao int, hw int, sxrq varchar(10))
insert into sphwph
select 1, 110, 1, '2012-01' union all
select 1, 112, 1, '2012-02' union all
select 1, 156, 1, '2012-05' union all
select 1, 110, 2, '2012-01' union all
select 1, 112, 2, '2012-02' union all
select 2, 114, 1, '2012-01' union all
select 2, 115, 1, '2012-02' union all
select 2, 110, 2, '2012-01' union all
select 2, 112, 2, '2012-02' union all
select 3, 132, 1, '2012-03'
select spid,pihao,hw,sxrq
from (select *,
row_number() over(partition by spid,hw order by pihao) rn
from sphwph) t
where rn=1/*
spid pihao hw sxrq
----------- ----------- ----------- ----------
1 110 1 2012-01
1 110 2 2012-01
2 114 1 2012-01
2 110 2 2012-01
3 132 1 2012-03(5 row(s) affected)
*/
select * from sphwph where sxrq=(select min(sxrq) from sphwph sh1 where sphwph.spid=sh1.spid and sphwph.hw=sh1.hw);
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([spid] int,[pihao] int,[hw] int,[sxrq] nvarchar(7))
Insert #T
select 1,110,1,N'2012-01' union all
select 1,112,1,N'2012-02' union all
select 1,156,1,N'2012-05' union all
select 1,110,2,N'2012-01' union all
select 1,112,2,N'2012-02' union all
select 2,114,1,N'2012-01' union all
select 2,115,1,N'2012-02' union all
select 2,110,2,N'2012-01' union all
select 2,112,2,N'2012-02' union all
select 3,132,1,N'2012-03'
Go
select [spid],[pihao],[hw],[sxrq]
from (Select *,rn=dense_rank()over(partition by [spid] order by [sxrq] asc) from #T ) as a
where rn=1
order by 1,2,3
/*
1 110 1 2012-01
1 110 2 2012-01
2 110 2 2012-01
2 114 1 2012-01
3 132 1 2012-03
*/