Sql group和join问题
Sql group和join问题
提问于 2016-05-09 13:24:50
也许只是因为我这个周末睡眠不足，但我似乎无法解决它。我有几张桌子：
- 产品(PK Productid与fk类别fk)
- ShippedProducts (PK ShippedProductsID与FK ShipmentID和fk productid)
- 装运(PK ShipmentID和FK InvoiceID)
- 发票(PK InvoiceID和FK CustomerID)
- 客户(PK CustomerID和FK CountryId)
- 国家(PK CountryID)
抱歉，没有其他解释架构的方法了。请让我知道，如果我可以提供一个更好的数据结构概述。
这是我的SQL (数据库是2008)
SELECT countries.countryid,
countryname,
isnull(round(sum(InvoiceTotal), 2),0) as TotalInvoice,
count(invoices.invoiceid) as nrOfInvoices,
count(shipments.shipmentid) as nrOfShipments
FROM INVOICES
inner join customers on invoices.CustomerID = customers.customerid
inner join countries on customers.CountryID = COUNTRIES.CountryID
inner join shipments on shipments.invoiceid = invoices.invoiceid
inner join ShippedProducts on ShippedProducts.ShipmentID = shipments.ShipmentID
group by countryname, COUNTRIES.CountryID, CurrencyName, CURRENCIES.CurrencyID如果我注释掉最后一个内部连接(与船运产品)，我得到了正确的发票数等等，但是当我内部加入船运产品时，计数并不包括发票数量，而是从货物中计算出的产品数量。如果我把更多的东西加到这个组里，它就不会再按国家分组了，而且我对每一张发票和装运货物都有一排。这个星期一，我不知怎么没办法看到我的错误。也许我只是需要更多的咖啡。
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-05-14 12:34:37
您应该能够使用一个窗口函数来完成这一任务，而不需要分组。像这样
SELECT
countries.countryid,
countryname,
isnull(round(sum(InvoiceTotal) over (partition by invoice.invoiceID), 2),0)
as TotalInvoice,
count(invoices.invoiceid)
over (partition by countries.countryid) as nrOfInvoicesPerCountry,
count(shipments.shipmentid) over (partition by coutries.countryid)
as nrOfShipments
FROM INVOICES
inner join customers on invoices.CustomerID = customers.customerid
inner join countries on customers.CountryID = COUNTRIES.CountryID
inner join shipments on shipments.invoiceid = invoices.invoiceid
inner join ShippedProducts on ShippedProducts.ShipmentID = shipments.ShipmentIDStack Overflow用户
发布于 2016-05-09 13:36:47
看来你的问题是在一对多的关系中加入。
我认为您应该将ShippedProducts.ShipmentProductsID计数放入select语句中的子select中。也许是这样..。
select countries.countryid,
countryname,
isnull(round(sum(InvoiceTotal), 2),0) as totalInvoice,
count(invoices.invoiceid) as nrOfInvoices,
count(shipments.shipmentid) as nrOfShipments
(select count shipmentproductsid from shipmentproducts where shipmentproducts.shipmentid = shipments.shipmentID) as totalProd
from invoices
inner join customers on invoices.customerid = customers.customerid
inner join countries on customers.countryid = countries.countryid
inner join shipments on shipments.invoiceid = invoices.invoiceid
group by countryname, countries.countryid , currencyname, currencies.currencyid页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37116966
复制相关文章
相似问题
腾讯云开发者
