排序难题改错，有生成脚本和代码，求解

Question

sql server排序PARTITION BYUNPIVOT存储过程

Answer 1

或者是否可以没有factorylist表，视图View_factorylist 完全由Source生成  （没有的字段填Null）如何写代码？

Answer 2

还是需要factorylist表，视图好像不能写入数据的吧

Answer 3

针对LZ的这个需要,建议用触发器实现比较好,
每次插入或更新Source表时,自动更新factorylist表相应工厂对应字段的内容.
如果用视图的话,每次查询都相当于全部数据重算一次.

Answer 4

最好是做成存储过程。
/*
1. 检验次数 ：表source中"工厂名称"不重复出现的次数
这个“不重复”难以理解，既然是次数，当然会重复多次出现。
*/
UPDATE factorylist
   SET 检验次数 = s.c
  FROM factorylist,
       (SELECT 工厂名称, COUNT(*) c
          FROM Source
         GROUP BY 工厂名称
       ) s
 WHERE factorylist.工厂名称 = s.工厂名称
/*
2. 主要客户1
3. 主要客户2
4. 主要客户3
*/
;WITH t1 AS (
    SELECT 工厂名称, 客户名称, COUNT(*) c,
           ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) rn
      FROM Source
  GROUP BY 工厂名称, 客户名称
)
,t2 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3]
     FROM t1
    PIVOT (
           MAX(客户名称)
           FOR rn IN ([1], [2], [3])
          ) p
)
UPDATE factorylist
   SET 主要客户1 = t2.[1],
       主要客户2 = t2.[2],
       主要客户3 = t2.[3]
  FROM factorylist,
       t2
 WHERE factorylist.工厂名称 = t2.工厂名称
/*
5. 熟悉的检验员1
6. 熟悉的检验员2
7. 熟悉的检验员3
*/
;WITH t1 AS ( --列转行
    SELECT 工厂名称, 检验员
      FROM Source
   UNPIVOT (
            检验员
            FOR 职务 IN (验货Leader,全职Helper2,全职Helper3,
                         兼职Leader,兼职Helper1,兼职Helper2,兼职Helper3)
           ) up
)
,t2 AS (
    SELECT 工厂名称, 检验员, COUNT(*) c,
           ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) rn
      FROM t1
  GROUP BY 工厂名称, 检验员
)
,t3 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3]
     FROM t2
    PIVOT (
           MAX(检验员)
           FOR rn IN ([1], [2], [3])
          ) p
)
UPDATE factorylist
   SET 熟悉的检验员1 = t3.[1],
       熟悉的检验员2 = t3.[2],
       熟悉的检验员3 = t3.[3]
  FROM factorylist,
       t3
 WHERE factorylist.工厂名称 = t3.工厂名称
/*
8. 主营产品1
9. 主营产品2
...
*/
;WITH t1 AS (
    SELECT 工厂名称, 产品名称, COUNT(*) c,
           ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) rn
      FROM Source
  GROUP BY 工厂名称, 产品名称
)
,t2 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3], [4], [5], [6], [7], [8], [9]
     FROM t1
    PIVOT (
           MAX(产品名称)
           FOR rn IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
          ) p
)
UPDATE factorylist
   SET 主营产品1 = t2.[1],
       主营产品2 = t2.[2],
       主营产品3 = t2.[3],
       主营产品4 = t2.[4],
       主营产品5 = t2.[5],
       主营产品6 = t2.[6],
       主营产品7 = t2.[7],
       主营产品8 = t2.[8],
       主营产品9 = t2.[9]
  FROM factorylist,
       t2
 WHERE factorylist.工厂名称 = t2.工厂名称SELECT * FROM factorylist

Answer 5

在第一个UPDATE之前加一句
;WITH s AS (
    SELECT 工厂名称, COUNT(*) c
      FROM Source
     GROUP BY 工厂名称
)
INSERT INTO factorylist(工厂名称,检验次数)
SELECT 工厂名称, c
  FROM s
 WHERE NOT EXISTS (SELECT *
                     FROM factorylist f
                    WHERE f.工厂名称 = s.工厂名称)

Answer 6

Tiger_zhao大师，感谢再次赐予代码。
用于实际生产的时候发现前面给的代码有点问题，示例如下：
例如，我的source表如果是这样：如果运用您提供的代码    ;WITH t1 AS (
    SELECT 工厂名称, 客户名称, COUNT(*) c,
           ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) rn
      FROM Source
  GROUP BY 工厂名称, 客户名称
)
    SELECT 工厂名称, [1], [2], [3]
     FROM t1
    PIVOT (
           MAX(客户名称)
           FOR rn IN ([1], [2], [3])
          ) p得到的结果是这样的：但是实际生产需要的结果应该是这样：
工厂名称   1       2          3
工厂1       ABC  GHI     NULL
工厂11    CDF  NULL  NULL
大师看下是否可以实现？

Answer 7

更正一下。
/* 2,3,4 */
ROW_NUMBER() OVER(PARTITION BY 工厂名称, 客户名称 ORDER BY COUNT(*) DESC) rn
/* 5,6,7 */
ROW_NUMBER() OVER(PARTITION BY 工厂名称, 检验员 ORDER BY COUNT(*) DESC) rn
/* 8,9,... */
ROW_NUMBER() OVER(PARTITION BY 工厂名称, 产品名称 ORDER BY COUNT(*) DESC) rn

Answer 8

大师，还是不行啊，您看：
如果Source表是：运用您修改过的代码得到：主要客户，检验员，主营产品都只会填写一个，实际工厂1应该对应两个客户ABC和GHI，对应两种产品：手机和衣服，对应多名检验员(这里应填充出现频率最高的前三位)
请帮我再看看，万分感谢！

Answer 9

Source数据贴出来啊，一个图怎么做测试。

Answer 10

/* 工厂ID 改为自增 */
ALTER TABLE factorylist
    DROP COLUMN [工厂ID]
GOALTER TABLE factorylist
    ADD [工厂ID] int IDENTITY NOT NULL
GO
/*
1. 检验次数 
*/
;WITH s AS (
    SELECT 工厂名称, COUNT(*) c
      FROM Source
     GROUP BY 工厂名称
)
INSERT INTO factorylist(工厂名称,检验次数)
SELECT 工厂名称, c
  FROM s
 WHERE NOT EXISTS (SELECT *
                     FROM factorylist f
                    WHERE f.工厂名称 = s.工厂名称)UPDATE factorylist
   SET 检验次数 = s.c
  FROM factorylist,
       (SELECT 工厂名称, COUNT(*) c
          FROM Source
         GROUP BY 工厂名称
       ) s
 WHERE factorylist.工厂名称 = s.工厂名称
/*
2. 主要客户1
3. 主要客户2
4. 主要客户3
*/
;WITH t1 AS (
    SELECT 工厂名称, 客户名称, --COUNT(*) c,
           ROW_NUMBER() OVER(PARTITION BY 工厂名称 ORDER BY COUNT(*) DESC) rn
      FROM Source
  GROUP BY 工厂名称, 客户名称
)
,t2 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3]
     FROM t1
    PIVOT (
           MAX(客户名称)
           FOR rn IN ([1], [2], [3])
          ) p
)
UPDATE factorylist
   SET 主要客户1 = t2.[1],
       主要客户2 = t2.[2],
       主要客户3 = t2.[3]
  FROM factorylist,
       t2
 WHERE factorylist.工厂名称 = t2.工厂名称
/*
5. 熟悉的检验员1
6. 熟悉的检验员2
7. 熟悉的检验员3
*/
;WITH t1 AS ( --列转行
    SELECT 工厂名称, 检验员
      FROM Source
   UNPIVOT (
            检验员
            FOR 职务 IN (验货Leader,全职Helper2,全职Helper3,
                         兼职Leader,兼职Helper1,兼职Helper2,兼职Helper3)
           ) up
)
,t2 AS (
    SELECT 工厂名称, 检验员, --COUNT(*) c,
           ROW_NUMBER() OVER(PARTITION BY 工厂名称 ORDER BY COUNT(*) DESC) rn
      FROM t1
  GROUP BY 工厂名称, 检验员
)
,t3 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3]
     FROM t2
    PIVOT (
           MAX(检验员)
           FOR rn IN ([1], [2], [3])
          ) p
)
UPDATE factorylist
   SET 熟悉的检验员1 = t3.[1],
       熟悉的检验员2 = t3.[2],
       熟悉的检验员3 = t3.[3]
  FROM factorylist,
       t3
 WHERE factorylist.工厂名称 = t3.工厂名称
/*
8. 主营产品1
9. 主营产品2
...
*/
;WITH t1 AS (
    SELECT 工厂名称, 产品名称, --COUNT(*) c,
           ROW_NUMBER() OVER(PARTITION BY 工厂名称 ORDER BY COUNT(*) DESC) rn
      FROM Source
  GROUP BY 工厂名称, 产品名称
)
,t2 AS ( -- 行转列
    SELECT 工厂名称, [1], [2], [3], [4], [5], [6], [7], [8], [9]
     FROM t1
    PIVOT (
           MAX(产品名称)
           FOR rn IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
          ) p
)
UPDATE factorylist
   SET 主营产品1 = t2.[1],
       主营产品2 = t2.[2],
       主营产品3 = t2.[3],
       主营产品4 = t2.[4],
       主营产品5 = t2.[5],
       主营产品6 = t2.[6],
       主营产品7 = t2.[7],
       主营产品8 = t2.[8],
       主营产品9 = t2.[9]
  FROM factorylist,
       t2
 WHERE factorylist.工厂名称 = t2.工厂名称SELECT 工厂ID, 工厂名称,
       主要客户1, 主要客户2, 主要客户3,
       熟悉的检验员1, 熟悉的检验员2, 熟悉的检验员3,
       主营产品1, 主营产品2, 主营产品3
  FROM factorylist
 WHERE 工厂名称 IN (N'工厂1', N'工厂11')工厂ID 工厂名称 主要客户1 主要客户2 主要客户3 熟悉的检验员1 熟悉的检验员2 熟悉的检验员3 主营产品1 主营产品2 主营产品3
------ -------- --------- --------- --------- ------------- ------------- ------------- --------- --------- ---------
1      工厂1    ABC       GHI       NULL      检验员1       检验员3       检验员4       手机      衣服      NULL
9      工厂11   CDF       NULL      NULL      检验员H       检验员G       检验员A       电器      NULL      NULL

Answer 11

Tiger_Zhao大神的方法确实可行，但需要改 工厂ID 改为自增 ，因为程序其他很多位置设置了 工厂ID 由程序维护。这里是否有办法不更改 工厂ID 改为自增 实现上面的要求，（factorylist中已经存在的列工厂ID不变，新列选区不重复的最小工厂ID值）
请教我！