我有一個合同表，其中有父合同和子合同。我想選擇所有的父合同和其下級合同的數量。
SELECT
sup.id_contract, sup.parent_id, sup.contractor_name
IFNULL(count(sub.id_contract)，0) AS訂單
FROM Contracts sub
LEFT JOIN Contracts sup ON sub.parent_id=sup.id_contract
where sup.delete !=1 GROUP by sup.id_contract;
但是對我來說，父合同也是一個帶有parent_id=NULL的合同，它的 "訂單 "可以是'0'
。我如何做才能同時選擇所有parent_id為NULL且訂單量為0的合同？
uj5u.com熱心網友回復：
你正在尋找的東西叫做左鍵連接
SELECT
sup.id_contract, sup.parent_id, sup.contractor_name, sup.subject_of_contract, sup.type_of_contract, sup.notice_period, sup.delete_parent_contract。
d.日期。
count(sub.id_contract) AS訂單
FROM Contracts sup
LEFT JOIN Contracts sub ON sub.parent_id = sup.id_contract
LEFT JOIN (
SELECT MAX(DATE_FORMAT(d.date, "%Y-%m-%d") date, d.id_contract FROM.
Dates d GROUP BY d.id_contract
) d ON sup.id_contract=d.id_contract
WHERE sup.delete!= 1
GROUP BY sup.id_contract。
uj5u.com熱心網友回復：
我想選擇所有的父合同和他們的下級合同的編號。
你應該可以用視窗函式來做這個。 基本的想法是：COALESCE()用合同的id對父級id進行計數。
SELECT c.*
FROM (SELECT c.*,
COUNT(*) OVER（PARTITION BY COALESCE（c. parent_id, c.id_contract)) - 1 as num_subcontracts
FROM Contracts c
WHERE c.delete <>/span> 1
) c
WHERE c.parent_id IS NULL;
實際上，你也可以在沒有- 1的情況下這樣做：
SELECT c.*
FROM (SELECT c.*,
COUNT(c. parent_id) OVER (PARTITION BY COALESCE（c.parent_id, c.id_contract）) as num_subcontracts
FROM Contracts c
WHERE c.delete <> 1
) c
WHERE c.parent_id IS NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/320168.html
標籤：
上一篇：排列函式和連接