我正在為美國各州創建一份月度收入報告，為了簡單起見，我把重點放在阿拉斯加。從下面的查詢和結果中，你可以看到，每張發票可以與多個發票專案相關聯：
SELECT
i.id AS "i.id"。
i.小計。
i.credit,
i.tax。
ii.id AS "ii.id"。
ii.refunded_amount
FROM[/span
發票i
JOIN customer ON i.customer_id = customer.id
JOIN invoice_items ii ON i.id = ii. invoices_id
WHERE
i.status = 'Paid'and i.datepaid BETWEEN '2016-01-01' and'2016-02-01'
AND customer.billing_day <>/span> 0
and customer.register_date < '2016-02-01'
AND customer.account_exempt = 'f'/span>
and customer.country = 'US'
and customer.state = 'AK';
i.id | subtotal | credit | tax | II.id | refunded_amount
---------- ---------- -------- ------- -------- -----------------
27111851 | 100 | 0 | 20 | 746219 | 0
27111851 | 100 | 0 | 20 | 746218 | 15
27111851 | 100 | 0 | 20 | 746217 | 0
27111852 | 0 | 1 | 0 | 746217 | 0
27111853 | 200 | 0 | 40 | 746220 | 0
我想將阿拉斯加州每月的銷售、稅收和總收入串列。 下面是我寫的查詢和結果：
SELECT
customer.state as "State",
ROUND((SUM(i.subtotal - i.credit)): 。NUMERIC, 2) AS "銷售額"。
ROUND((SUM(i.tax)): 。NUMERIC, 2) as "Tax",
繞數(
(
SUM((i.subtotal - i.credit i.tax) - ii.refunded_amount)
):: NUMERIC,
2
) AS "Gross"
FROM[/span
發票 i
JOIN customer ON i.customer_id = customer.id
JOIN invoice_items ii ON i.id = ii. invoices_id
WHERE
i.status = 'Paid'and i.datepaid BETWEEN '2016-01-01' and'2016-02-01'
AND customer.billing_day <>/span> 0
and customer.register_date < '2016-02-01'
AND customer.account_exempt = 'f'/span>
and customer.country = 'US'
and customer.state = 'AK'/span>
GROUP BY
customer.state。
國家 | 銷售 | 稅收 | 毛額
------- --------- -------- ----------
AK | 499 | 100 | 584
以下是結果：
州|銷售|稅收|毛額
------- --------- -------- ----------
AK | 299 | 60 | 344
我的查詢對同一發票的小計、稅金和貸方進行了多次制表，這人為地夸大了結果。我需要改變查詢，以便它只將每張發票納入計算一次，但仍然查看所有相關的發票專案。我不確定如何在sql中實作這一目標。謝謝你的指點！
uj5u.com熱心網友回復：
預先匯總invoice_items，所以它不會影響其他的結果：
SELECT c.state as "State",
ROUND((SUM(i.subtotal - i.credit)): 。NUMERIC, 2) AS "銷售額"。
ROUND((SUM(i.tax)): 。NUMERIC, 2) AS "Tax"。
ROUND(SUM((i.subtotal - i.credit i.tax) - ii.refunded_amount) ：: NUMERIC, 2.
) AS "毛額"
FROM發票 i JOIN
客戶c
ON i.customer_id = c.id JOIN
(SELECT ii.invoices_id, SUM（ii.refunded_amount） as refunded_amount
FROM invoice_items ii
GROUP BY ii.invoiced_id
) ii
ON i.id = ii.invoices_id
WHERE i.status = 'Paid' AND
i.datepaid BETWEEN '2016-01-01' and '2016-02-01' and
c.billing_day <> 0 AND
c.register_date </span> '2016-02-01' AND
c.account_exempt = 'f'/span> AND
c.country = 'US' and
c.state = 'AK'
GROUP BY c.state。
uj5u.com熱心網友回復：
我使用了Gordon Linoff的答案，但我重復了與客戶表的JOIN陳述句，并將WHERE子句移到子查詢中。
SELECT
customer.state AS "State",
ROUND((SUM(i.subtotal - i.credit)): 。NUMERIC, 2) AS "銷售額"。
ROUND((SUM(i.tax)): 。NUMERIC, 2) as "Tax",
繞行(
SUM((i.subtotal - i.credit i.tax) - refunded_amount)：: NUMERIC,
2
) AS "毛額"
FROM[/span
發票 i
JOIN customer ON i.customer_id = customer.id
JOIN (
SELECT[/span
ii.invoices_id。
SUM(ii.refunded_amount) AS refunded_amount
FROM[/span
發票i
JOIN customer ON i.customer_id = customer.id
JOIN invoice_items ii ON i.id = ii. invoices_id
WHERE
i.datepaid BETWEEN '2016-01-01' AND '2016-02-01' AND
customer.billing_day <>/span> 0 AND
customer.register_date </span> '2016-02-01' AND
customer.account_exempt = 'f'/span> AND
customer.country = 'US'/span> AND
customer.state = 'AK'
GROUPBY
ii.invoices_id
) ii ON i.id = ii. invoices_id
GROUP BY customer.state。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/334035.html
標籤：