給我的作業是編輯查詢，但我不能做我想做的操作。首先，我要說明的查詢輸出如下：
FacilityName AmountWithoutDiscount DiscountedAmount
Atlantis 18.305 12.335
OneTower 4.595 3.168
Panora 10.043 6.831
Grand Total 32.944 22.334
我的查詢：
SELECT DISTINCT g.FacilityName, SUM(g.BrutPrice) AS AmountWithoutDiscount, SUM(g.NetPrice) AS DiscountedAmount
From(
SELECT e.FacilityName, b.ItemAmount, b.BrutPrice, b.NetPrice,
(SELECT DISTINCT f.ProductId FROM FAZIKI.dbo.NM_ProductCampaign f WHERE c.Id = f.ProductId AND (f.IsActive=1) AND (f.CampaignId='7') AND (f.ProductId IS NOT NULL)) as Product
FROM FAZIKI.dbo.PM_InvoiceInformation a
INNER JOIN FAZIKI.dbo.PM_InvoiceDetail b ON a.Id = b.InvoiceId
INNER JOIN FAZIKI.dbo.PM_Product c ON b.ProductId = c.Id
INNER JOIN FAZIKI.dbo.DF_GNFacility e ON a.FacilityId = e.Id
WHERE(a.InvoiceDate > CONVERT(DATETIME, '2021-11-08 00:00:00', 102)) AND (a.InvoiceType = 2) AND (a.IsFromNCR = 1)
AND (c.RayonId = 1) AND (b.BrutPrice <> b.NetPrice) AND (a.IsCancel = 0)) AS g
WHERE g.Product IS NOT NULL
GROUP BY g.FacilityName,g.BrutPrice, g.NetPrice
查詢結果：
FacilityName AmountWithoutDiscount DiscountedAmount
Atlantis 8.50 5.74
Atlantis 8.50 5.75
Atlantis 8.50 5.77
.
.
.
OneTower 8.50 5.70
OneTower 8.50 5.83
OneTower 8.50 5.87
.
.
.
Panora 8.50 5.66
Panora 8.50 5.78
Panora 8.50 5.81
我如何做我上面提到的欄位中的查詢結果的總和以及每個商店的總和？我提出了我的查詢到某一點，但我不能做更多。你能幫助我嗎？
uj5u.com熱心網友回復：
我認為您應該從查詢中洗掉：
- 獨特 - 沒有“分組依據”和“獨特”的意義。
- g.BrutPrice 和 g.NetPrice 來自 group by。如果您將它們分組，則沒有必要在它們上使用諸如“sum”之類的聚合函式。
所以：
SELECT g.FacilityName, SUM(g.BrutPrice) AS AmountWithoutDiscount, SUM(g.NetPrice) AS DiscountedAmount
From(
SELECT e.FacilityName, b.ItemAmount, b.BrutPrice, b.NetPrice,
(SELECT DISTINCT f.ProductId FROM FAZIKI.dbo.NM_ProductCampaign f WHERE c.Id = f.ProductId AND (f.IsActive=1) AND (f.CampaignId='7') AND (f.ProductId IS NOT NULL)) as Product
FROM FAZIKI.dbo.PM_InvoiceInformation a
INNER JOIN FAZIKI.dbo.PM_InvoiceDetail b ON a.Id = b.InvoiceId
INNER JOIN FAZIKI.dbo.PM_Product c ON b.ProductId = c.Id
INNER JOIN FAZIKI.dbo.DF_GNFacility e ON a.FacilityId = e.Id
WHERE(a.InvoiceDate > CONVERT(DATETIME, '2021-11-08 00:00:00', 102)) AND (a.InvoiceType = 2) AND (a.IsFromNCR = 1)
AND (c.RayonId = 1) AND (b.BrutPrice <> b.NetPrice) AND (a.IsCancel = 0)) AS g
WHERE g.Product IS NOT NULL
GROUP BY g.FacilityName
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/362906.html
標籤：sql sql-server 查询语句
上一篇：布爾運算子行為