如何为下面的输出编写查询 [英] how to write query for below output

问题描述

我们有一个表table1，其中包含ordereddate，金额和货币列，该表如下所示:

订购日期金额币种
2012-02-07 2.6美元
2012-02-07 1.3 gbp
2012-02-09 10.08 USD
2012-02-09 15 gbp



我们的查询是如何编写sql查询，该查询给出以下输出，并应按ordereddate排序，金额应转换为任何一种货币(例如usd的gbp)，您可以采用此公式将usd转换为gbp

1.00 USD = 0.629876 GBP


需要输出
----------------

Ordereddate TotalAmount货币
2012-02-07 2.9376776 gbp
2012-02-09 21.34915 gbp


所以请任何人帮助我们

We have a table table1 which has columns ordereddate, amount and currency and the table looks like this

Ordereddate amount currency
2012-02-07 2.6 usd
2012-02-07 1.3 gbp
2012-02-09 10.08 usd
2012-02-09 15 gbp



Our query is how to write sql query which gives following output and that should be ordered by ordereddate and the amount should be converted into any one currency(say gbp from usd) and you can take this formula for converting usd to gbp

1.00 USD = 0.629876 GBP


Output needed
----------------

Ordereddate TotalAmount currency
2012-02-07 2.9376776 gbp
2012-02-09 21.34915 gbp


so please anybody help us out

推荐答案

在这里您要走

Here you go

--temp table
DECLARE @Orders TABLE (OrderedDate DATETIME, Amount FLOAT, Currency VARCHAR(50))
INSERT INTO @Orders  VALUES ('2012-02-07', 2.6, 'USD')
INSERT INTO @Orders VALUES ('2012-02-07', 1.3, 'GBP' )
INSERT INTO @Orders VALUES ('2012-02-09', 10.08 , 'USD')
INSERT INTO @Orders VALUES ('2012-02-09', 15, 'GBP')

SELECT OrderedDate, SUM(Amount), 'GBP' Unit
FROM
--Create a derived table with single currency unit
(SELECT OrderedDate, CASE WHEN Currency = 'USD' THEN Amount * 0.629846 ELSE Amount END Amount
FROM @Orders) As New
GROUP BY OrderedDate

但是，我有一个问题，您是否尝试了一些东西或将其直接发布到论坛中，以便其他人为您完成工作?

I have one question though, did you try out something or posting it directly in forums so that someone else will do the work for you?

SELECT orderdate,CASE WHEN max(currency)='usd' THEN
SUM(amount*0.629876)
else sum(amount)
END
from currencyDet group by OrderDate

这篇关于如何为下面的输出编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！