求一条SQL语句，急！

Question

记录如下：
PACKINGLISTID         ORDERID         SKU SKUDESC UNITSAVAILABLE CASENO
===============================================================================
00000000000000000005 20130713001PS 68001 680-DG 10.0000         C03                 
00000000000000000005 20130713001PS 68001 680-DG 15.0000         C03                 
00000000000000000005 20130713001PS 68001 680-DG 5.0000         C03                 
00000000000000000006 20130713001PS 68001 680-DG 70.0000         C04     想通过一条语句实现如下：
TotalCase  PACKINGLISTID ORDERID         SKU SKUDESC UNITSAVAILABLE CASENO
======================================================================================
2          00000000000000000005 20130713001PS 68001 680-DG 10.0000         C03                 
2          00000000000000000005 20130713001PS 68001 680-DG 15.0000         C03                 
2          00000000000000000005 20130713001PS 68001 680-DG 5.0000         C03                 
2          00000000000000000006 20130713001PS 68001 680-DG 70.0000         C04     因为CASENO只有2个，如何实现呢？谢谢！
SQL语句

Answer 1

就是将CASENO汇总，相同的CASENO只算1个：PACKINGLISTID          CASENO
=================================
00000000000000000005   C03                 
00000000000000000005   C03                 
00000000000000000005   C03                 
00000000000000000006   C04     想通过一条语句实现如下：
TOTALCASENO  PACKINGLISTID          CASENO
==========================================
2            00000000000000000005   C03                 
2            00000000000000000005   C03                 
2            00000000000000000005   C03                 
2            00000000000000000006   C04   

Answer 2

try this,select count(distinct CASENO) 'TotalCase',* 
 from [表名]

Answer 3

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

Answer 4

select count(distinct CASENO) 'TotalCase',*   from [表名] 返回如下：
TOTALCASENO  PACKINGLISTID          CASENO
==========================================
1            00000000000000000005   C03                 
1            00000000000000000005   C03                 
1            00000000000000000005   C03                 
1            00000000000000000006   C04   

Answer 5

try this,select (select count(distinct CASENO) from [表名]) 'TotalCase',* 
 from [表名]