求统计语句

Question

表1 ：wip_DispInfo (DeviceNo,StoreID)
MS1000    1
MS2000    1
MS1000    2
MS1000    3
MS2000    3表2： wip_StoreClass(ID,StoreName) 库类型
1   正品
2   次品
3   废品我想得到如下的效果 
器件    正品   次品  废品
MS1000    1      2    1      
MS2000    1      0    1发表此贴，想看看有没有简单使用的办法。请高手指点

Answer 1

SELECT DeviceNo,
   SUM(CASE WHEN StoreId = 1 THEN 1 ELSE 0 END) AS 正品,
   SUM(CASE WHEN StoreId = 2 THEN 1 ELSE 0 END) AS 次品,
   SUM(CASE WHEN StoreId = 3 THEN 1 ELSE 0 END) AS 废品
FROM wip_DispIfo 
GROUP BY DeviceNo;

Answer 2

select 器件=DeviceNo,
正品=sum(case when StoreName='正品' then 1 else 0 end) ,
次品=sum(case when StoreName='次品' then 1 else 0 end) ,
废品=sum(case when StoreName='废品' then 1 else 0 end)  
from wip_DispInfo a,wip_StoreClass b
where a.StoreID=b.ID
GROUP BY DeviceNo

Answer 3

--动态的.DECLARE @str varchar(8000);
SET @str = '';SELECT @str = @str + ',SUM(CASE WHEN B.StoreName=''' 
                     + StoreName + ''' THEN 1 ELSE 0 END) AS ['+StoreName+']'
FROM wip_StoreClass;EXEC('SELECT A.DeviceNo'+@str +' FROM wip_DispIfo AS A
        JOIN wip_StoreClass AS B ON A.StoreID = B.ID
       GROUP BY A.DeviceNo');

Answer 4

if object_id('wip_DispInfo') is not null drop table wip_DispInfo
  create table wip_DispInfo(DeviceNo varchar(10),StoreID int)
insert into wip_DispInfo
           select 'MS1000',    1 
union  all select 'MS2000',    1 
union  all select 'MS1000',    2 
union  all select 'MS1000',    2 
union  all select 'MS1000',    3 
union  all select 'MS2000',    3 
if object_id('wip_StoreClass') is not null drop table wip_StoreClass 
create table wip_StoreClass(ID int,StoreName varchar(20))
insert wip_StoreClass
           select 1,  '正品' 
union  all select 2,  '次品' 
union  all select 3,  '废品' --使用动态SQL方法
declare @sql nvarchar(4000)
set @sql=N'select DeviceNo '
select @sql=@sql+
                 N','+QUOTENAME(B.StoreName)+
                 N'=sum(case a.StoreID when '+quotename(a.StoreID,N'''')+  
                 N' then 1 else 0 end)'
       from wip_DispInfo a,wip_StoreClass b where a.StoreID=b.ID group by b.StoreName,storeid   order by a.storeid
       print @sql
       
select DeviceNo ,
[正品]=sum(case a.StoreID when '1' then 1 else 0 end),
[次品]=sum(case a.StoreID when '2' then 1 else 0 end),
[废品]=sum(case a.StoreID when '3' then 1 else 0 end)
from wip_DispInfo a
group by devicenoset @sql=@sql+N' from wip_DispInfo a group by deviceno'
exec(@sql)/*
DeviceNo 正品 次品 废品
MS1000 1 2 1
MS2000 1 0 1
*/