常用函数

最新推荐文章于 2024-11-14 22:35:19 发布

转载最新推荐文章于 2024-11-14 22:35:19 发布 · 53 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/csyusu/p/10811763.html

博客介绍了一些SQL查询技巧，包括将scn转换为时间戳，使用pivot进行行转列、unpivot进行列转行，以及用WM_CONCAT将多行合并为一条记录，并给出了转载来源。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、查询scn转换为时间戳

SELECT to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24miss'),txn_time FROM dwr_eda_cf_rep_gls

2、行转列，pivot

WITH WIP AS
 (SELECT SHIFT_TIMEKEY, PRODUCT_ID, GLS_ID, FACTORY
    FROM DWT_WIP_EOH_GLS
   WHERE SHIFT_TIMEKEY = '20190505 060000'
     AND FACTORY IN ('CF', 'CELL'))
SELECT PRODUCT_ID,CF,CE
  FROM WIP
PIVOT (COUNT(GLS_ID) FOR FACTORY IN('CF' AS CF, 'CELL' AS CE)) P;
--1.FROM WIP 查询出需要的数据
--2.PIVOT 对查询结果做行转列生成新表P，会对GLS_ID、FACTORY列之外的列进行聚合

3、列转行，unpivot

SELECT * FROM P UNPIVOT(GLS_QTY FOR FACTORY IN(CF, CE))

4、多行合并为一条记录，WM_CONCAT

WITH
TEST
AS(
SELECT 1 AS a,'a' AS b FROM dual
UNION ALL SELECT 1 AS a,'b' AS b FROM dual
UNION ALL SELECT 1 AS a,'c' AS b FROM dual
UNION ALL SELECT 2 AS a,'a' AS b FROM dual
UNION ALL SELECT 2 AS a,'d' AS b FROM dual
)
SELECT a,to_char(WM_CONCAT(b)) AS WM FROM TEST GROUP BY a