时间戳

首页 > 解决方案 > ORACLE 10G，我是 oracle 10g 的新手。如何在没有联合的情况下对查询进行语法并并排查看列

oracle - ORACLE 10G，我是 oracle 10g 的新手。如何在没有联合的情况下对查询进行语法并并排查看列

问题描述

我想比较标题，按贸易代码的数量。00PAT 是订单，553028 是发票。4 TITLES 订购 3 TITLES 已售出。

SELECT CUS.CODE, CUS.NAME, F1.ID, F1.FTRDATE, F1.TRADECODE,MAT.SUBCODE1, MAT.DESCRIPTION, S1.PRIMARYQTY
FROM CUSTOMER CUS, FINTRADE F1, STORETRADELINES S1, MATERIAL MAT
WHERE S1.FTRID=F1.ID AND CUS.ID=F1.CUSID AND MAT.ID=S1.ITEID
AND F1.DSRID='14500' AND F1.FTRDATE='13/4/2021'
UNION   
SELECT CUS.CODE, CUS.NAME, F2.ID, F2.FTRDATE, F2.TRADECODE,MAT.SUBCODE1, MAT.DESCRIPTION, S2.PRIMARYQTY
FROM CUSTOMER CUS, FINTRADE F2, STORETRADELINES S2, MATERIAL MAT
WHERE S2.FTRID=F2.ID AND CUS.ID=F2.CUSID AND MAT.ID=S2.ITEID
AND F2.DSRID='15500' AND F2.FTRDATE='14/4/2021'

  NAME         ID   FTRDATE     TRADECODE   DESCRIPTION PRIMARYQTY
CUSTOMER1   1382757 13/4/2021   00PAT0000001    TITLE1  2
CUSTOMER1   1382757 13/4/2021   00PAT0000001    TITLE2  3
CUSTOMER1   1382757 13/4/2021   00PAT0000001    TITLE3  1
CUSTOMER1   1382757 13/4/2021   00PAT0000001    TITLE4  2
CUSTOMER1   1382766 14/4/2021   553028          TITLE2  2
CUSTOMER1   1382766 14/4/2021   553028          TITLE3  1
CUSTOMER1   1382766 14/4/2021   553028          TITLE4  2

期望输出

标签： oracleoracle10g

解决方案

对我来说，它看起来像这样：

SELECT cus.code,
       cus.name,
       f1.id,
       f1.ftrdate,
       f1.tradecode,
       mat.subcode1,
       mat.description,
       s1.primaryqty
FROM customer         cus
JOIN fintrade         f1  ON f1.cusid = cus.id
JOIN storetradelines  s1  ON s1.ftrid = f1.id
JOIN material         mat ON mat.id = s1.iteid
WHERE f1.dsrid IN ('14500', '15500')
  AND f2.ftrdate IN (DATE '2021-04-13', DATE '2021-04-14');

推荐阅读