我正在尝试在多货币设置中执行SUM()。以下将展示我面临的问题:-
Customer
-------------------------
Id | Name
1 | Mr. A
2 | Mr. B
3 | Mr. C
4 | Mr. D
-------------------------
Item
-------------------------
Id | Name | Cost | Currency
1 | Item 1 | 5 | USD
2 | Item 2 | 2 | EUR
3 | Item 3 | 10 | GBP
4 | Item 4 | 5 | GBP
5 | Item 5 | 50 | AUD
6 | Item 6 | 20 | USD
7 | Item 3 | 10 | EUR
-------------------------
Order
-------------------------
User_Id | Product_Id
1 | 1
2 | 1
1 | 2
3 | 3
1 | 5
1 | 7
1 | 5
2 | 6
3 | 4
4 | 2
-------------------------
现在,我想要一个SELECT查询的输出,该查询将客户名称和购买产品的总价值列为:-
Customer Name | Amount
Mr. A | Multiple-currencies
Mr. B | 25 USD
Mr. C | 15 GBP
Mr. D | 2 EUR
因此,基本上,我正在寻找一种方法来添加同一客户下多个产品的成本,如果所有产品都有相同的货币,否则只显示"多种货币"。运行以下查询没有帮助:-
SELECT Customer.Name, SUM(Item.Amount) FROM Customer
INNER JOIN Order ON Order.User_Id = Customer.Id
INNER JOIN Item ON Item.Id = Order.Product_Id
GROUP BY Customer.Name
我的问题应该是什么?我正在使用Sqlite
我建议有两个输出列,一个用于货币,另一个用于金额:
SELECT c.Name,
(case when max(currency) = min(currency) then sum(amount)
end) as amount,
(case when max(currency) = min(currency) then max(currency)
else 'Multiple Currencies'
end) as currency
FROM Customer c INNER JOIN
Order o
ON o.User_Id = c.Id INNER JOIN
Item
ON i.Id = o.Product_Id
GROUP BY c.Name
如果需要,可以将它们连接到一个字符串列中。我只是更喜欢把信息放在两个不同的栏里,这样做。
以上是标准SQL。
我认为您的查询应该看起来像这个
SELECT
Data.Name AS [Customer Name],
CASE WHEN Data.Count > 1 THEN "Multiple-currencies" ELSE CAST(Data.Amount AS NVARCHAR) END AS Amount
FROM
(SELECT
Customer.Name,
COUNT(Item.Currency) AS Count,
SUM(Item.Amount) AS Amount
FROM
Customer
INNER JOIN Order ON Order.User_Id = Customer.Id
INNER JOIN Item ON Item.Id = Order.Product_Id
GROUP BY
Customer.Name) AS Data
一个子查询,用于获取货币计数,然后在主查询中要求它们显示总数或文本"多个货币"。
抱歉,如果有任何错误或键入错误,但我没有数据库服务器来测试
希望这能有所帮助。
IMO我将从标准化变量名开始。为什么在订单表中调用客户表中的ID USER_ID?只是一个宠物脾气。无论如何,您应该学习如何构建查询。
首先将customer表连接到上的order表,然后将结果连接到item表。第一个联接在CUSTOMER_ID上,第二个联接在PRODUCT_ID上。一旦您完成了该工作,请使用SUM和GROUP BY
好的,我用这种方式解决了这个问题:-
SELECT innerQuery.Name AS Name, (CASE WHEN innerQuery.Currencies=1 THEN (innerQuery.Amount || innerQuery.Currency) ELSE 'Mutliple-Currencies' END) AS Amount, FROM
(SELECT Customer.Name, SUM(Item.Amount), COUNT(DISTINCT Item.Currency) AS Currencies, Item.Currency AS Currency FROM Customer
INNER JOIN Order ON Order.User_Id = Customer.Id
INNER JOIN Item ON Item.Id = Order.Product_Id
GROUP BY Customer.Name)innerQuery