行专列有关问题

行专列问题
数据如下：
不良表：
日期            品名不良项目数量
2012/12/11451-EE29-18砂渣孔        1
2012/12/11451-EE29-18文字不良1
2012/12/11451-EJ33-28壳模损坏2
2012/12/15531-0803-58壳模破裂18
2012/12/15541-0503-58壳内穿刺60
2012/12/13852-1602-68壳模脱落1
2012/12/13111-0510-58缩孔        1
2012/12/15541-0603-58鼓胀        1
2012/12/11481-AF02-28缩孔        1
2012/12/11451-EE29-18缩孔        1
2012/12/11451-EK47-18缩孔        1
入库表：
日期            品名数量
2012/12/11451-EE29-182
2012/12/11451-EE29-182
2012/12/11451-EJ33-2852
2012/12/15531-0803-58203
2012/12/15541-0503-58410
2012/12/13852-1602-682
2012/12/13111-0510-58819
2012/12/15541-0603-58257
2012/12/11481-AF02-2857
2012/12/11451-EE29-182
2012/12/11451-EK47-1850

想要的结果：
日期品名鼓胀壳模破裂壳模损坏壳模脱落壳内穿刺砂渣孔缩孔文字不良总计入库数量不良率%
2012/12/11451-EE29-181113633.33
2012/12/21451-EJ33-2822523.70
2012/12/31451-EK47-1811501.96
2012/12/41481-AF02-2811571.72
2012/12/53111-0510-58118190.12
2012/12/63852-1602-6811233.33
2012/12/75531-0803-5818182038.14
2012/12/85541-0503-58606041012.77
2012/12/95541-0603-58112570.39



[解决办法]

SELECT '日期' = CONVERT(VARCHAR(10),A.日期,120),
       A.品名,
       '砂渣孔' = ISNULL(SUM(CASE WHEN 不良项目 = '砂渣孔' THEN B.数量 END),0),
       '缩孔' = ISNULL(SUM(CASE WHEN 不良项目 = '缩孔' THEN B.数量 END),0),
       '文字不良' = ISNULL(SUM(CASE WHEN 不良项目 = '文字不良' THEN B.数量 END),0),
       '壳模损坏' = ISNULL(SUM(CASE WHEN 不良项目 = '壳模损坏' THEN B.数量 END),0),
       '壳模脱落' = ISNULL(SUM(CASE WHEN 不良项目 = '壳模脱落' THEN B.数量 END),0),
       '壳模破裂' = ISNULL(SUM(CASE WHEN 不良项目 = '壳模破裂' THEN B.数量 END),0),
       '壳内穿刺' = ISNULL(SUM(CASE WHEN 不良项目 = '壳内穿刺' THEN B.数量 END),0),
       '鼓胀' = ISNULL(SUM(CASE WHEN 不良项目 = '鼓胀' THEN B.数量 END),0),
       '总计' = ISNULL(C.不良数量,0),
       A.入库数量,
       '不良率%' = 100.0*ISNULL(C.不良数量,0)/NULLIF(A.入库数量+C.不良数量,0)
FROM (SELECT CONVERT(VARCHAR(10),日期,120) AS '日期',品名,SUM(数量) AS '入库数量' FROM 入库表
      GROUP BY CONVERT(VARCHAR(10),日期,120),品名) A
  LEFT JOIN 不良表 B ON B.品名 = A.品名 AND CONVERT(VARCHAR(10),B.日期,120) = CONVERT(VARCHAR(10),A.日期,120)
  LEFT JOIN (SELECT CONVERT(VARCHAR(10),日期,120) AS '日期',品名,SUM(数量) AS '不良数量' FROM 不良表
             GROUP BY CONVERT(VARCHAR(10),日期,120),品名) C 
 
    ON C.品名 = A.品名 AND CONVERT(VARCHAR(10),C.日期,120) = CONVERT(VARCHAR(10),A.日期,120)
GROUP BY CONVERT(VARCHAR(10),A.日期,120),A.品名,A.入库数量,C.不良数量

/*
日期品名砂渣孔缩孔文字不良壳模损坏壳模脱落壳模破裂壳内穿刺鼓胀总计入库数量不良率%
2012-12-011451-EE29-18111000003633.333333333333
2012-12-011451-EJ33-28000200002523.703703703703
2012-12-011451-EK47-18010000001501.960784313725
2012-12-011481-AF02-28010000001571.724137931034
2012-12-013111-0510-580100000018190.121951219512
2012-12-013852-1602-68000010001233.333333333333
2012-12-015531-0803-58000001800182038.144796380090
2012-12-015541-0503-580000006006041012.765957446808
2012-12-015541-0603-580000000112570.387596899224

*/

感觉写的有点繁杂，看看有木有更简洁的写法
[解决办法]
第一次就这样没了···
---创建测试表
CREATE TABLE #b(d date,
                 NAME VARCHAR(20),
                 bad VARCHAR(20),
                 n numeric )

INSERT #b SELECT 
'2012/12/1','1451-EE29-18','砂渣孔' , 1 union   all
SELECT'2012/12/1','1451-EE29-18', '文字不良',1 union   all
SELECT'2012/12/1' ,'1451-EJ33-28', '壳模损坏', 2   union   all
SELECT'2012/12/1', '5531-0803-58' ,'壳模破裂', 18 union   all
SELECT'2012/12/1' ,'5541-0503-58' ,'壳内穿刺' ,60 union   all
SELECT'2012/12/1' ,'3852-1602-68' ,'壳模脱落', 1union   all
SELECT'2012/12/1' ,'3111-0510-58' ,'缩孔'  ,       1   union   all
SELECT'2012/12/1' ,'5541-0603-58' ,'鼓胀' ,        1   union   all
SELECT'2012/12/1' ,'1481-AF02-28' ,'缩孔' ,        1   union   all
SELECT'2012/12/1' ,'1451-EE29-18' ,'缩孔' ,        1   union   all
SELECT'2012/12/1' ,'1451-EK47-18', '缩孔' ,        1

CREATE TABLE #r(d date,NAME VARCHAR(20),gn numeric)

INSERT #r SELECT '2012/12/1', '1451-EE29-18', 2 union   all
SELECT '2012/12/1', '1451-EE29-18', 2 union   all
SELECT '2012/12/1','1451-EJ33-28', 52    union   all
SELECT '2012/12/1', '5531-0803-58', 203 union   all
SELECT '2012/12/1','5541-0503-58', 410 union   all
SELECT '2012/12/1', '3852-1602-68', 2 union   all
SELECT '2012/12/1', '3111-0510-58', 819  union   all
SELECT '2012/12/1', '5541-0603-58', 257  union   all
SELECT '2012/12/1', '1481-AF02-28', 57    union   all
SELECT '2012/12/1', '1451-EE29-18', 2    union   all
SELECT '2012/12/1', '1451-EK47-18', 50
GO

SELECT #b.d,#b.NAME,#b.bad,n,n+gn '总数',n/(n+gn)*100 '不良率' intO #a FROM #b LEFT JOIN #r ON #b.d=#r.d AND #b.NAME=#r.name

SELECT * FROM #a
PIVOT (  max(n) FOR bad IN(砂渣孔,文字不良,壳模损坏,壳模破裂,壳内穿刺,壳模脱落,缩孔,鼓胀 ) )m

结果
dNAME总数不良率砂渣孔文字不良壳模损坏壳模破裂壳内穿刺壳模脱落缩孔鼓胀
2012-12-011451-EE29-18333.333333333333333311NULLNULLNULLNULL1NULL
2012-12-011451-EJ33-28543.7037037037037037NULLNULL2NULLNULLNULLNULLNULL
2012-12-011451-EK47-18511.9607843137254902NULLNULLNULLNULLNULLNULL1NULL
2012-12-011481-AF02-28581.7241379310344828NULLNULLNULLNULLNULLNULL1NULL
2012-12-013111-0510-588200.1219512195121951NULLNULLNULLNULLNULLNULL1NULL
2012-12-013852-1602-68333.3333333333333333NULLNULLNULLNULLNULL1NULLNULL
2012-12-015531-0803-582218.1447963800904977NULLNULLNULL18NULLNULLNULLNULL
2012-12-015541-0503-5847012.7659574468085106NULLNULLNULLNULL60NULLNULLNULL
2012-12-015541-0603-582580.3875968992248062NULLNULLNULLNULLNULLNULLNULL1
[解决办法]
参照方法
http://bbs.csdn.net/topics/240002706

查看更多 下一篇