在汇总计数功能方面需要帮助

debugcn 发表于 Dev

皮特

我一直在做一个不错的小项目，以计算在特定办公室下每个国家/地区的发货数量。我更喜欢编写一个查询，因为否则将花费大量时间使用while / for循环等处理整个数据。因此，我编写了一个查询，如下所示：

   SELECT
COUNTRY.Code, COUNTRY.Name,
(SELECT COUNT(Shipment.Ref) FROM Shipment
LEFT JOIN Station  (NOLOCK) ON Station.STID = Shipment.STDEST
LEFT JOIN Country (NOLOCK) ON Station.Code = Country.Code
LEFT JOIN Client (NOLOCK) ON Client.CID = Shipment.CID
WHERE Client.CBUR = 'UKLON'
) AS NumOrder
FROM COUNTRY

这将返回总出货量，而不是每个特定国家/地区

--------------------------------------
|Code    |Name              |NumOrder|
--------------------------------------
| BE     |BELGIUM           | 82460  |
| FR     |FRANCE            | 82460  |
| UK     |UNITED KINGDOM    | 82460  |
| NL     |NETHERLANDS       | 82460  |
| AZ     |AZERBAIJAN        | 82460  |
| HK     |HONG KONG         | 82460  |
| AU     |AUSTRALIA         | 82460  |
| RE     |REUNION ISLAND    | 82460  |

如果有人给我提示如何修改现有查询，以便能够根据给定的办公室帐户计算每个国家/地区的发货量，我将不胜感激。

亚历山大·马蒂奇（Aleksandar Matic）

尝试将国家/地区添加到计数中

SELECT
COUNTRY.Code, COUNTRY.Name,
(SELECT COUNT(Shipment.Ref) FROM Shipment
LEFT JOIN Station  (NOLOCK) ON Station.STID = Shipment.STDEST
LEFT JOIN Country (NOLOCK) ON Station.Code = TPAY.Code
LEFT JOIN Client (NOLOCK) ON Client.CID = Shipment.CID
WHERE Client.CBUR = 'UKLON' and Country.Name = C.Name --or this "and Country.Code = C.Code"
) AS NumOrder
FROM COUNTRY C

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。