RICHEER COCA 2022-02-03 00:20 采纳率: 93.9%
浏览 36
已结题

优化代码批量查询得到结果,请专家答疑解惑

优化代码批量查询得到结果,请专家答疑解惑
查询#test 表里的n1~~n40相同的数值,想得到的结果

img

 
DROP TABLE IF EXISTS  #test --  select * from #test

create table #test([MID] [int] IDENTITY(1,1) NOT NULL primary key,val INT,ID INT,ColName nvarchar(10),seq INT,n1 INT,n2 INT,n3 INT,n4 INT,n5 INT,n6 INT,n7 INT,n8 INT,n9 INT,n10 INT,n11 INT,n12 INT,n13 INT,n14 INT,n15 INT,n16 INT,n17 INT,n18 INT,n19 INT,n20 INT,n21 INT,n22 INT,n23 INT,n24 INT,n25 INT,n26 INT,n27 INT,n28 INT,n29 INT,n30 INT,n31 INT,n32 INT,n33 INT,n34 INT,n35 INT,n36 INT,n37 INT,n38 INT,n39 INT,n40 INT)  
insert into #test 
SELECT '12'Val,'43'ID,'DQ_RpNr43'ColName,'39'seq,'3'n1,'9'n2,'11'n3,'17'n4,'19'n5,'29'n6,'30'n7,'32'n8,'38'n9,'40'n10,'42'n11,'44'n12,'48'n13,'52'n14,'61'n15,'66'n16,'67'n17,'76'n18,'77'n19,'78'n20,'1'n21,'3'n22,'7'n23,'9'n24,'11'n25,'17'n26,'19'n27,'23'n28,'32'n29,'34'n30,'38'n31,'40'n32,'42'n33,'43'n34,'52'n35,'61'n36,'68'n37,'70'n38,'77'n39,'80'n40
UNION ALL SELECT '11','24','DQ_RpNr24','146','8','9','13','17','21','23','27','29','36','44','46','49','51','52','56','59','63','67','68','69','2','3','8','9','13','21','27','29','33','36','42','46','49','54','57','60','63','66','69','77'
UNION ALL SELECT '11','2','DQ_RpNr2','12','1','5','6','11','15','25','37','42','44','47','51','54','59','64','65','70','71','74','75','76','2','4','5','7','10','11','15','19','20','44','46','47','54','59','64','66','71','74','75','77'
UNION ALL SELECT '11','23','DQ_RpNr23','284','1','2','3','7','11','24','27','29','30','35','36','38','40','42','46','48','57','66','77','78','1','3','11','16','30','32','33','35','36','38','46','48','50','53','59','60','66','70','71','77'
UNION ALL SELECT '10','21','DQ_RpNr21','443','1','9','11','12','13','16','17','37','44','47','52','55','57','61','62','64','67','69','79','80','5','9','12','15','16','17','31','32','43','44','47','51','52','53','62','67','69','70','73','75'
UNION ALL SELECT '10','11','DQ_RpNr11','419','1','3','4','7','8','11','14','24','31','42','43','44','51','52','59','60','63','70','74','77','4','5','8','11','14','16','18','23','27','28','31','41','44','49','52','60','61','63','70','78'
UNION ALL SELECT '10','17','DQ_RpNr17','1','7','11','15','16','25','27','30','33','36','37','38','43','48','52','60','70','74','75','77','78','6','8','15','27','28','30','37','38','39','44','46','49','51','52','59','70','75','77','78','80'

 
DROP TABLE IF EXISTS #tt3 --  select * from #tt3

SELECT p.MID,p.val,p.ID,p.colname,p.seq, QTY
into  #tt3
from #test
unpivot (QTY for type in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20
,n21,n22,n23,n24,n25,n26,n27,n28,n29,n30,n31,n32,n33,n34,n35,n36,n37,n38,n39,n40 )) as p

-- 问题:如何批量一次性得到想要的结果  (以下是分别取MID=1,2,3,4,5的结果再合并得到结果)
;WITH t1 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from #tt3  WHERE MID=1
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T1
FROM    t1 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t2 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from #tt3  WHERE MID=2
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T2
FROM    t2 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t3 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=3
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T3
FROM    t3 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t4 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=4
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T4
FROM    t4 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t5 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=5
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T5
FROM    t5
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;

SELECT * FROM #T1
UNION ALL
SELECT * FROM #T2
UNION ALL
SELECT * FROM #T3
UNION ALL
SELECT * FROM #T4
UNION ALL
SELECT * FROM #T5

DROP TABLE  #T1
DROP TABLE  #T2
DROP TABLE  #T3
DROP TABLE  #T4
DROP TABLE  #T5

  • 写回答

1条回答 默认 最新

  • DarkAthena ORACLE应用及数据库设计方案咨询师 2022-02-03 12:30
    关注
     with tt as (
     select mid,val,id,colname,seq,qty,'N' +CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from (
    select distinct MID,VAL,ID,ColName,seq,QTY,count(1) over(partition by id,QTY) AS RPNR from #tt3
    ) as t where RPNR>1)
    select * from tt PIVOT (   MAX(QTY)
              FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
    

    img

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 2月10日
  • 已采纳回答 2月4日
  • 修改了问题 2月3日
  • 修改了问题 2月3日
  • 展开全部

悬赏问题

  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题
  • ¥15 主机可以ping通路由器但是连不上网怎么办
  • ¥15 数据库一张以时间排好序的表中,找出多次相邻的那些行
  • ¥50 关于DynamoRIO处理多线程程序时候的问题
  • ¥15 kubeadm部署k8s出错
  • ¥15 Abaqus打不开cae文件怎么办?
  • ¥15 小程序准备上线,软件开发公司需要提供哪些资料给甲方