我想回傳兩個表中至少存在 1 個代碼的結果。
我知道我可以做到：
SELECT t1.id, taxcode, earncode, dductcode
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t3.id
JOIN table_3 t3 ON t1.id = t3.id
WHERE taxcode = earncode OR taxcode = dductcode OR dductcode = earncode
但是，例如，這將顯示 1 個匹配的稅碼對的所有 dductcodes = Earncodes。相反，我寧愿回傳每個匹配項針對 ID 出現的次數。
表格1
| ID | 稅法 |
|---|---|
| 1 | 美國廣播公司 |
| 1 | 國防軍 |
| 1 | 全球健康指數 |
| 2 | 貓 |
| 2 | 喬爾 |
表_2
| ID | 賺取代碼 |
|---|---|
| 1 | 美國廣播公司 |
| 1 | NGM |
| 1 | 全球健康指數 |
| 2 | 貓 |
| 2 | YPL |
表3
| ID | 產品代碼 |
|---|---|
| 1 | 美國廣播公司 |
| 1 | QST |
| 1 | RBD |
| 2 | 貓 |
| 2 | YPL |
期望的查詢結果：
| ID | # OF TIMES that taxcode = Earncode | # 次 dductcode = Earncode | # 次稅碼 = dductcode |
|---|---|---|---|
| 1 | 2 | 1 | 1 |
| 2 | 1 | 2 | 1 |
uj5u.com熱心網友回復：
您可以使用 CTE 來避免一對多的陷阱；但是，我覺得奇怪的是我們假裝在所有 3 個表中都找到了 id。只要保持真實，這就會起作用。如果沒有，那么您可能需要一些左連接并稍微調整一下。
with tax_equals_earn as
(SELECT t1.id,
sum(case when t1.taxcode = t2.earncode then 1 else 0 end) as ttotal
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id
group by t1.id
),
tax_equals_dduct as
(SELECT t1.id,
sum(case when t1.taxcode = t3.dductcode then 1 else 0 end) as ttotal
FROM table_1 t1
JOIN table_3 t3 ON t1.id = t3.id
group by t1.id
),
earn_equals_dduct as
(
select t2.id,
sum(case when t2.earncode = t3.dductcode then 1 else 0 end) as ttotal
from table_2 t2
join table_3 t3 on t2.id = t3.id
group by t2.id
)
select distinct t1.id,
tee.ttotal as tax_equals_earn_times,
ted.ttotal as tax_equals_dduct_times,
eed.ttotal as earn_equals_dduct_times
from table_1 t1
join tax_equals_earn tee on t1.id = tee.id
join tax_equals_dduct ted on t1.id = ted.id
join earn_equals_dduct eed on t1.id = eed.id
Db-fiddle 在這里找到。
uj5u.com熱心網友回復：
如果 2 列相等，則可以使用“case”陳述句生成 1，否則生成 0。然后取總和。
SELECT
t1.id,
SUM(CASE WHEN taxcode = earncode THEN 1 ELSE 0 END) AS NUM_TIMES_TAXCODE_EARNCODE,
SUM(CASE WHEN dductcode = earncode THEN 1 ELSE 0 END) AS NUM_TIMES_DDUCTCODE_EARNCODE,
SUM(CASE WHEN taxcode = dductcode THEN 1 ELSE 0 END) AS NUM_TIMES_TAXCODE_DDUCTCODE
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t3.id
JOIN table_3 t3 ON t1.id = t3.id
WHERE taxcode = earncode OR taxcode = dductcode OR dductcode = earncode
GROUP BY
t1.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448964.html