求一条SQL语句,急!
记录如下:
PACKINGLISTID ORDERID SKUSKUDESCUNITSAVAILABLECASENO
===============================================================================
0000000000000000000520130713001PS68001680-DG10.0000 C03
0000000000000000000520130713001PS68001680-DG15.0000 C03
0000000000000000000520130713001PS68001680-DG5.0000 C03
0000000000000000000620130713001PS68001680-DG70.0000 C04
想通过一条语句实现如下:
TotalCase PACKINGLISTIDORDERID SKUSKUDESCUNITSAVAILABLECASENO
======================================================================================
2 0000000000000000000520130713001PS68001680-DG10.0000 C03
2 0000000000000000000520130713001PS68001680-DG15.0000 C03
2 0000000000000000000520130713001PS68001680-DG5.0000 C03
2 0000000000000000000620130713001PS68001680-DG70.0000 C04
因为CASENO只有2个,如何实现呢?谢谢!
SQL语句
[解决办法]
create table #tb (PACKINGLISTID varchar(50),CASENO varchar(10))
insert into #tb
select '00000000000000000005','C03' union all
select '00000000000000000005','C03' union all
select '00000000000000000005','C03' union all
select '00000000000000000006','C04'
;with cte as
(
select rn= ROW_NUMBER() over ( PARTITION by caseno order by getdate()), * from #tb
)
select TOTALCASENO=(select COUNT(1) from cte where rn=1) ,PACKINGLISTID ,CASENO from cte
drop table #tb
select (select count(distinct CASENO) from [表名]) 'TotalCase',*
from [表名]