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

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

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

行转列：使用PIVOT函数

在SQL查询中，行转列通常使用PIVOT函数来实现。PIVOT函数能够将行数据转换为列数据。例如，以下是从表格中选择半成品批号、日期、组次和重量列的查询：

SELECT Lot, Date, Group, Weight
FROM myTable 

使用PIVOT函数后的查询语句如下：

SELECT *
FROM myTable
PIVOT (MAX(Weight) FOR [Date] IN ([2022-01-01], [2022-01-02], [2022-01-03])) AS pTable

在上面的语句中，使用了MAX函数将重量作为聚合值，并使用[Date]列作为PIVOT的转置列。在转置列中，我们可以指定需要转置的值，例如[2022-01-01]、[2022-01-02]、[2022-01-03]等。如果不指定这些值，则PIVOT函数将根据原始数据自动生成列。

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

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

SELECT *
FROM myTable
WHERE Lot = '3IA270' AND Group = '91'
ORDER BY creatdate DESC

在这种情况下，我们可以通过使用窗口函数来解决查询结果只包含一条数据的问题。

使用窗口函数解决查询一条数据的问题

窗口函数是一种高级SQL技术，它可以对行数据进行排序和分组，并为每个行数据分配一个序号。以下是一个使用窗口函数的示例查询：

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Lot, Group ORDER BY creatdate DESC) AS rn
  FROM myTable
) X
WHERE Lot = '3IA270' AND Group = '91' AND rn = 1

在这里，我们使用了窗口函数ROW_NUMBER()来对每个半成品批号和组次分组，并根据创建日期降序排列。然后，在外部SELECT语句中，我们仅保留rn值为1的记录，以确保每个半成品批号和组次只返回最新的一条数据。

 我们来看看真实的操作语句，一下是原始实例查询语句与查询结果：

select t.*
  from (
  select  dc.datacollectiondefname,dl.dataname,dl.datavalue,dl.creatdate 
  from  datapointhistorydetail  dl,datacollectiondef df ,datacollectiondefbase dc
  where  dl.datacollectiondefid=df.datacollectiondefid
  and df.datacollectiondefbaseid=dc.datacollectiondefbaseid
  and dc.datacollectiondefname = 'DropEnzymeWeight')X pivot 
  (
    max(datavalue) for dataname in (
    'Lot' as 半成品批号,
    'Date' as 日期,
    'Class' as 班别,
    'Group' as 组次,
    'DroppingEnzymePlatformNo' as 滴酶平台编号,
    'ScaleNumber' as  天平编号,
    'Time' as 时间,
    'SlidesNo' as 片号,
    'Weight' as 重量,
    'Judge' as 判断,
    'Operator' as 操作人,
    'OperateDate' as 操作日期,
    'Exception' as 异常情况,
    'Recorder' as 记录人,
    'Checker' as 复核人
    )
) t where  t.半成品批号='3IA270' and t.组次='91'
order by creatdate desc 

 在Oracle中，如果需要查询具有相同创建日期的所有数据，即使时间相同，可以通过在窗口函数中添加序号，并根据序号筛选出需要的数据。以下是修改后的查询语句：

SELECT t.*
FROM (
  SELECT dc.datacollectiondefname, dl.dataname, dl.datavalue, dl.creatdate, 
         ROW_NUMBER() OVER (PARTITION BY dl.datacollectiondefid, dl.dataname, dl.creatdate ORDER BY dl.creatdate DESC) AS rn
  FROM datapointhistorydetail dl, datacollectiondef df, datacollectiondefbase dc
  WHERE dl.datacollectiondefid = df.datacollectiondefid
    AND df.datacollectiondefbaseid = dc.datacollectiondefbaseid
    AND dc.datacollectiondefname = 'DropEnzymeWeight'
) X PIVOT (
  MAX(datavalue) FOR dataname IN (
    'Lot' AS 半成品批号,
    'Date' AS 日期,
    'Class' AS 班别,
    'Group' AS 组次,
    'DroppingEnzymePlatformNo' AS 滴酶平台编号,
    'ScaleNumber' AS 天平编号,
    'Time' AS 时间,
    'SlidesNo' AS 片号,
    'Weight' AS 重量,
    'Judge' AS 判断,
    'Operator' AS 操作人,
    'OperateDate' AS 操作日期,
    'Exception' AS 异常情况,
    'Recorder' AS 记录人,
    'Checker' AS 复核人
  )
) t 
WHERE t.半成品批号 = '3IA270' AND t.组次 = '91' --AND t.rn = 1
ORDER BY creatdate DESC 

 

结论

本文介绍了如何使用PIVOT函数和窗口函数，将表格中的行数据转置为列数据，并解决只查询一条数据的问题。通过这些高级SQL技术，可以轻松地处理复杂的数据集，并实现更高效的数据处理。

 

不足之处，还望见谅！