代码之家 › 专栏 › 技术社区 › Adi

如何修复查询以显示结果?

oracle sql

Adi · 技术社区 · 5 年前

我有多个表要联接,以便从所有表中获得不同列的总和。我的表结构示例如下所示。

顾客作为

+---+-------+------------+--------+----------+
|id | name  | profile_id |  grp   | end_date |
+---+-------+------------+--------+----------+
| 1 | cust1 |     7      |   ae   |   (null) |
+---+-------+------------+--------+----------+
| 2 | cust2 |     2      |   bz   |  (null)  |
+---+-------+------------+--------+----------+
| 3 | cust2 |     2      |   cc   | not_null |
+---+-------+------------+--------+----------+

作为B的配置文件

+---+-------------------+
|id | profile_name      |
+---+-------------------+
| 2 | pro_cust1         |
+---+-------------------+
| 7 | pro_cust2         |
+---+-------------------+

发票为C

+---+------------------+----------------+----------------+
|id | reference        | scandate       |  customer_id   |
+---+------------------+----------------+----------------+
| 1 | test_SOMETHING1  |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 2 | test_2           |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 3 | test_SOMETHING2  |  (not_today)   |   2            |
+---+------------------+----------------+----------------+
| 4 | test_SOMETHING3  |  (today)       |   1            |
+---+------------------+----------------+----------------+
| 5 | test_3           |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 6 | test_SOMETHING4  |  (not_today)   |   1            |
+---+------------------+----------------+----------------+

发票错误为D

+---+------------------+----------------+----------------+
|id | reference        | scandate       |  customer_id   |
+---+------------------+----------------+----------------+
| 1 | tst_SOMETHING1   |  (today)       |   1            |
+---+------------------+----------------+----------------+
| 2 | tst_2            |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 3 | tst_SOMETHING2   |  (not_today)   |   1            |
+---+------------------+----------------+----------------+
| 4 | tst_SOMETHING3   |  (today)       |   1            |
+---+------------------+----------------+----------------+
| 5 | tst_3            |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 6 | tst_SOMETHING4   |  (not_today)   |   1            |
+---+------------------+----------------+----------------+

发票未通过

+---+------------------+----------------+----------------+
|id | reference        | scandate       |  customer_id   |
+---+------------------+----------------+----------------+
| 1 | ttt_SOMETHING1   |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 2 | ttt_2            |  (today)       |   1            |
+---+------------------+----------------+----------------+
| 3 | ttt_SOMETHING2   |  (not_today)   |   2            |
+---+------------------+----------------+----------------+
| 4 | ttt_SOMETHING3   |  (today)       |   2            |
+---+------------------+----------------+----------------+
| 5 | ttt_3            |  (today)       |   1            |
+---+------------------+----------------+----------------+
| 6 | ttt_SOMETHING4   |  (not_today)   |   2            |
+---+------------------+----------------+----------------+

输出:

+---------+---------------+--------+--------+ 
| customer+ profile       |  group | Total  |
+---------+---------------+--------+--------+
| cust1   |   pro_cust1   |   ae   |   2    | 
+---------+---------------+--------+--------+
| cust2   |   pro_cust2   |   bz   |   3    |
+---------+---------------+--------+--------+

下面是我的代码,没有返回任何数据。我做错什么了?

SELECT 
     A.name as customer
    ,B.profile_name as profile
    ,A.grp as "Group"
    ,count(distinct C.reference) + count(distinct D.reference) + count(distinct E.reference) as "Total"
FROM 
    customers A 
INNER JOIN 
    profiles B 
ON 
    A.profile_id = B.id 
INNER JOIN 
        invoices C
ON 
        A.id = C.customer_id 
INNER JOIN 
        invoice_s_errors D
ON 
        A.id = D.customer_id 
INNER JOIN
        invoice_fail E
ON
        A.id = E.customer_id 
WHERE 
    A.end_date IS NULL
AND
        (upper(C.reference) NOT LIKE ('%SOMETHING%') AND trunc(C.scandate) = trunc(sysdate))
AND
        (upper(D.reference) NOT LIKE ('%SOMETHING%') AND trunc(D.scandate) = trunc(sysdate))
AND
        (upper(E.reference) NOT LIKE ('%SOMETHING%') AND trunc(E.scandate) = trunc(sysdate))
GROUP BY A.name, A.grp, B.profile_name ORDER BY A.name ASC

1 回复 | 直到 5 年前

LukStorms 5 年前

这是你的数据,因为你内在的加入了它们。

与C&D中的客户ID相比,表别名E中的“引用”不带“something”的客户ID有所不同。

所以改为左连接。

然后把一些标准放在连接的位置。
否则,它们仍然充当内部联接。

SELECT 
 cust.name AS customer
,prof.profile_name AS profile
,cust.grp AS "Group"
,(COUNT(DISTINCT inv.reference) +
  COUNT(DISTINCT inverr.reference) +
  COUNT(DISTINCT invfail.reference)) AS "Total"
FROM customers cust
JOIN profiles prof ON prof.id = cust.profile_id
LEFT JOIN invoices inv 
  ON inv.customer_id = cust.id
 AND upper(inv.reference) NOT LIKE ('%SOMETHING%') 
 AND trunc(inv.scandate) = trunc(sysdate)
LEFT JOIN invoice_s_errors inverr 
  ON inverr.customer_id = cust.id
  AND upper(inverr.reference) NOT LIKE ('%SOMETHING%') 
  AND trunc(inverr.scandate) = trunc(sysdate)
LEFT JOIN invoice_fail invfail 
  ON invfail.customer_id = cust.id
 AND upper(invfail.reference) NOT LIKE ('%SOMETHING%') 
 AND trunc(invfail.scandate) = trunc(sysdate)
WHERE cust.end_date IS NULL          
GROUP BY cust.name, cust.grp, prof.profile_name 
ORDER BY cust.name ASC