写本博客的目为了温故而知新把学习过程记录下来，以备后查。
当我们需要将表格中的行转置为列时，通常可以使用PIVOT函数来实现。但是在某些情况下，由于创建日期等字段相同，只有一条数据会被查询出来。这时候，我们就可以使用窗口函数进行分组和排序，以便返回具有相同创建日期的所有记录。本文将介绍如何通过使用PIVOT函数和窗口函数，解决只查询一条数据的问题。
在SQL查询中，行转列通常使用PIVOT函数来实现。PIVOT函数能够将行数据转换为列数据。例如，以下是从表格中选择半成品批号、日期、组次和重量列的查询：
1 SELECT Lot, Date, Group, Weight 2 FROM myTable
View Code
使用PIVOT函数后的查询语句如下：
1 SELECT * 2 FROM myTable 3 PIVOT (MAX(Weight) FOR [Date] IN ([2022-01-01], [2022-01-02], [2022-01-03])) AS pTable
View Code
在上面的语句中，使用了MAX函数将重量作为聚合值，并使用[Date]列作为PIVOT的转置列。在转置列中，我们可以指定需要转置的值，例如[2022-01-01]、[2022-01-02]、[2022-01-03]等。如果不指定这些值，则PIVOT函数将根据原始数据自动生成列。
然而，在某些情况下，由于创建日期等字段相同，只有一条数据会被查询出来。例如，以下查询语句只返回最新创建日期的一条数据：
1 SELECT * 2 FROM myTable 3 WHERE Lot = '3IA270' AND Group = '91' 4 ORDER BY creatdate DESC
View Code
在这种情况下，我们可以通过使用窗口函数来解决查询结果只包含一条数据的问题。
窗口函数是一种高级SQL技术，它可以对行数据进行排序和分组，并为每个行数据分配一个序号。以下是一个使用窗口函数的示例查询：
1 SELECT * 2 FROM ( 3 SELECT *, ROW_NUMBER() OVER (PARTITION BY Lot, Group ORDER BY creatdate DESC) AS rn 4 FROM myTable 5 ) X 6 WHERE Lot = '3IA270' AND Group = '91' AND rn = 1
View Code
在这里，我们使用了窗口函数ROW_NUMBER()来对每个半成品批号和组次分组，并根据创建日期降序排列。然后，在外部SELECT语句中，我们仅保留rn值为1的记录，以确保每个半成品批号和组次只返回最新的一条数据。
我们来看看真实的操作语句，一下是原始实例查询语句与查询结果：
1 select t.* 2 from ( 3 select dc.datacollectiondefname,dl.dataname,dl.datavalue,dl.creatdate 4 from datapointhistorydetail dl,datacollectiondef df ,datacollectiondefbase dc 5 where dl.datacollectiondefid=df.datacollectiondefid 6 and df.datacollectiondefbaseid=dc.datacollectiondefbaseid 7 and dc.datacollectiondefname = 'DropEnzymeWeight')X pivot 8 ( 9 max(datavalue) for dataname in ( 10 'Lot' as 半成品批号, 11 'Date' as 日期, 12 'Class' as 班别, 13 'Group' as 组次, 14 'DroppingEnzymePlatformNo' as 滴酶平台编号, 15 'ScaleNumber' as 天平编号, 16 'Time' as 时间, 17 'SlidesNo' as 片号, 18 'Weight' as 重量, 19 'Judge' as 判断, 20 'Operator' as 操作人, 21 'OperateDate' as 操作日期, 22 'Exception' as 异常情况, 23 'Recorder' as 记录人, 24 'Checker' as 复核人 25 ) 26 ) t where t.半成品批号='3IA270' and t.组次='91' 27 order by creatdate desc
View Code
在Oracle中，如果需要查询具有相同创建日期的所有数据，即使时间相同，可以通过在窗口函数中添加序号，并根据序号筛选出需要的数据。以下是修改后的查询语句：
1 SELECT t.* 2 FROM ( 3 SELECT dc.datacollectiondefname, dl.dataname, dl.datavalue, dl.creatdate, 4 ROW_NUMBER() OVER (PARTITION BY dl.datacollectiondefid, dl.dataname, dl.creatdate ORDER BY dl.creatdate DESC) AS rn 5 FROM datapointhistorydetail dl, datacollectiondef df, datacollectiondefbase dc 6 WHERE dl.datacollectiondefid = df.datacollectiondefid 7 AND df.datacollectiondefbaseid = dc.datacollectiondefbaseid 8 AND dc.datacollectiondefname = 'DropEnzymeWeight' 9 ) X PIVOT ( 10 MAX(datavalue) FOR dataname IN ( 11 'Lot' AS 半成品批号, 12 'Date' AS 日期, 13 'Class' AS 班别, 14 'Group' AS 组次, 15 'DroppingEnzymePlatformNo' AS 滴酶平台编号, 16 'ScaleNumber' AS 天平编号, 17 'Time' AS 时间, 18 'SlidesNo' AS 片号, 19 'Weight' AS 重量, 20 'Judge' AS 判断, 21 'Operator' AS 操作人, 22 'OperateDate' AS 操作日期, 23 'Exception' AS 异常情况, 24 'Recorder' AS 记录人, 25 'Checker' AS 复核人 26 ) 27 ) t 28 WHERE t.半成品批号 = '3IA270' AND t.组次 = '91' --AND t.rn = 1 29 ORDER BY creatdate DESC
View Code
本文介绍了如何使用PIVOT函数和窗口函数，将表格中的行数据转置为列数据，并解决只查询一条数据的问题。通过这些高级SQL技术，可以轻松地处理复杂的数据集，并实现更高效的数据处理。
不足之处，还望见谅！
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。