sql - 如何组合三个查询?
问题描述
我有三个查询,我必须将它们组合在一起。
查询 1
WITH ph
AS (SELECT chrd, chwo, chse, chst, chvr, chfv, chrd,
ROW_NUMBER () OVER(PARTITION BY chwo ORDER BY chse, chvr desc) TEMP
FROM wrpd.wscl
WHERE chaj > '20180901'
AND chst = 'R'
AND chstb IN ( 'L1', 'R2' )
ORDER BY chse)
SELECT *
FROM ph A
WHERE A.temp = 1
查询 2
SELECT chrd, chwo, chse, chst, chvr, chfv, chrd,
ROW_NUMBER () OVER( PARTITION BY chwo ORDER BY chse, chvr desc) TEMP
FROM wrpd.wscl
WHERE chajdt > '20180901'
AND chst IN ( 'P', 'A' )
AND chstb IN ( 'L1', 'R2' )
ORDER BY chst desc, chse
查询 3
SELECT partd1, actdd1, dmre, dmde, dlro, dord, wvin, rcdt,
CHAR(DATE(Substr(CHAR(rcdt), 1, 4)
|| '-'
|| Substr(CHAR(rcdt), 5, 2)
|| '-'
|| Substr(CHAR(rcdt), 7, 2)), usa) AS "Conversion",
SUM(qty) AS Shipments,
rcdt-dord AS Ship_Days
FROM pspd.zpslsp,
pspd.zpslma,
dlpd.drdm,
wrpd.wscl
WHERE partd1 = partpm
AND delr = dmde
AND actdd1 BETWEEN '201801' AND '201810'
AND otypd1 NOT IN ( 'T', 'Z', 'W' )
AND dmty = 'RD'
AND partpm LIKE '21101%'
AND Substr(delr, 3, 3) NOT BETWEEN '390' AND '399'
AND qtys > 0
AND chfv = wvin
GROUP BY partd1, actdd1, dmde, dlro, dord, rcdt, dmre, wvin
ORDER BY 1, 2
最终结果中的所需列:
DMRE, CHRD, DMDE, CHWO, CHSE, CHST, CHVR, CHFV, WVIN, PARTd1, ACTDd1,
CHRD, DLRO, DORD, RCDT,
CHAR(DATE(Substr(CHAR(rcdt), 1, 4)
|| '-'
|| Substr(CHAR(rcdt), 5, 2)
|| '-'
|| Substr(CHAR(rcdt), 7, 2)), usa)
AS "Conversion",
SUM(QTYS) AS Shipments, RCDT-DORD AS Ship_Days
ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR DESC) TEMP
注意:我不能将查询 1 和查询 2 合并为一个,因为输出不同。
新编辑:我ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR DESC) TEMP
在查询 2 中添加了“”,这可能有助于使用联合。
提前致谢!
解决方案
我相信你可以自己解决这个问题..我想它需要看起来像这样..但你没有说你想要如何组合数据所以我只是一个交叉产品(the WHERE 1=1
)最后一个数据集为前两个的并集
with PH AS(
SELECT CHRD,CHWO,CHSE,CHST,CHVR,CHFV,CHRD,
ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR DESC) TEMP
FROM WRPD.WSCL
WHERE CHAJ > '20180901'
AND CHST ='R'
AND CHSTB in ('L1', 'R2')
--ORDER BY CHSE
), DT AS(
SELECT CHRD,CHWO,CHSE,CHST,CHVR,CHFV,CHRD,
ROW_NUMBER () OVER(PARTITION BY CHWO ORDER BY CHSE,CHVR DESC) TEMP
FROM WRPDAT.WSCLHP
WHERE CHAJDT > '20180901'
AND CHST IN ('P','A')
AND CHSTB in ('L1', 'R2')
)
, U AS (
SELECT *
FROM PH A
WHERE A.TEMP=1
UNION ALL
SELECT *
FROM DT
)
, Z AS
(
SELECT
PARTd1, ACTDd1, DMRE,DMDE,DLRO,DORD,WVIN,RCDT,
CHAR(DATE(SUBSTR(CHAR(RCDT),1,4) ||'-'||
SUBSTR(CHAR(RCDT),5,2) ||'-'||
SUBSTR(CHAR(RCDT),7,2)), USA)
AS "Conversion",
SUM(QTY) AS Shipments, RCDT-DORD AS Ship_Days
FROM PSPD.ZPSLSP, PSPD.ZPSLMA, dlpd.drdm,WRPD.WSCL
WHERE PARTd1 = PARTpm
AND DELR = DMDE
AND ACTDd1 between '201801' and '201810'
AND OTYPd1 NOT IN ('T','Z','W')
AND DMTY = 'RD'
and partpm like '21101%'
and substr(delr,3,3) not between '390' and '399'
and QTYS >0
and CHFV = WVIN
Group By PARTd1, ACTDd1, DMDE, DLRO, DORD,RCDT, DMRE,WVIN
)
SELECT U.*, Z.* FROM U, Z WHERE CHFV = WVIN
ORDER BY TEMP, PARTd1, ACTDd1
推荐阅读
- python - 根据要拆分的关键字将行移动到新列中
- c - 如何使用堆栈以与输入顺序相反的顺序打印?
- r - 在R中将函数作为列表返回
- stm32 - flash下载失败-STM32F401RB
- eclipse - Eclipse 和 Pydev 问题
- asp.net-core - ASP.NET Core:HttpContext.SignInAsync 不起作用
- c# - CollectionAssert.AreEquivalent 失败。集合中的元素数量不匹配
- python - 在函数python内部递增
- plotly - 创建一个 2 轴图,其中第 3 轴作为彩色图表
- reactjs - 元素类型无效:应为字符串或类/函数(用于复合组件)但得到:未定义的反应