Oracle高级技巧：使用PIVOT函数和窗口函数解决只查询一条数据的问题-526互联

写本博客的目为了温故而知新把学习过程记录下来，以备后查。

当我们需要将表格中的行转置为列时，通常可以使用PIVOT函数来实现。但是在某些情况下，由于创建日期等字段相同，只有一条数据会被查询出来。这时候，我们就可以使用窗口函数进行分组和排序，以便返回具有相同创建日期的所有记录。本文将介绍如何通过使用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函数将根据原始数据自动生成列。

creatdate时间一样，只查询一条数据

然而，在某些情况下，由于创建日期等字段相同，只有一条数据会被查询出来。例如，以下查询语句只返回最新创建日期的一条数据：

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