我不認為我的帖子標題是正確的/最好的，但我真的不知道如何簡明扼要地解釋它。
我有一個 SQL 查詢
SELECT a.id,
c.firstname AS "First Name",
c.lastname AS "Last Name",
c.telephone AS "Telephone",
c.email AS "Email",
a.name AS "Company Name",
i.available_integration_id AS 'Stripe'
FROM users u
INNER JOIN contacts c
ON c.id = u.contact_id
INNER JOIN accounts a
ON u.account_id = a.id
LEFT JOIN integrations i
ON a.id = i.account_id
WHERE a.id IN
(SELECT DISTINCT a1.id
FROM accounts a1
INNER JOIN orders o1
ON a1.id = o1.account_id
WHERE o1.created_at >= '2020-10-01 00:00:00'
)
這將回傳 600 多個結果
然后我需要運行
SELECT count(id), sum(amount)
FROM transactions t
WHERE t.order_id IN
(SELECT o.id
FROM orders o
WHERE o.account_id = 'c59bde2a-3ff8-61ea-e449-42699342a444'
AND o.status = 'completed'
)
AND t.status = 'completed'
針對從第一個查詢回傳的每個 ID。
是否可以將這兩個查詢合并在一起以回傳一個看起來像這樣的表
ID, First Name, Last Name, Telephone, Email, Company Name, Stripe, Total Orders, Total Amount
還是我必須運行第二個查詢 600 多次并手動復制/粘貼資料？
uj5u.com熱心網友回復：
如果我沒有遺漏什么，這就是您需要的：
SELECT a.id,
c.firstname AS "First Name",
c.lastname AS "Last Name",
c.telephone AS "Telephone",
c.email AS "Email",
a.name AS "Company Name",
i.available_integration_id AS "Stripe",
totals.cnt_orders AS "Total Orders",
totals.sum_amount AS "Total Amount"
FROM users u
INNER JOIN contacts c
ON c.id = u.contact_id
INNER JOIN accounts a
ON u.account_id = a.id
LEFT JOIN integrations i
ON a.id = i.account_id
LEFT JOIN (SELECT a.id, COUNT(t.id) AS cnt_orders, SUM(t.amount) AS sum_amount
FROM accounts a
INNER JOIN orders o
ON o.account_id = a.id
INNER JOIN transactions t
ON o.id = t.order_id
WHERE o.status = 'completed'
AND o.created_at >= '2020-10-01 00:00:00'
GROUP BY a.id) totals
ON a.id = totals.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/454861.html
標籤：sql
上一篇：SQL多對一條件