Sql-Server

SQL Server 2014 從表中提取值，行列和小計中的雙重連接

  • July 4, 2017

我有一張歐洲央行每日匯率表

FXDate              EUR     USD     AUD     BGN     BRL     CAD     CHF     ....
30/06/2017 00:00    1.00    1.1412  1.4851  1.9558  3.76    1.4785  1.093
29/06/2017 00:00    1.00    1.1413  1.4868  1.9558  3.7476  1.4867  1.0935
28/06/2017 00:00    1.00    1.1375  1.4986  1.9558  3.7632  1.4888  1.0913
27/06/2017 00:00    1.00    1.1278  1.4819  1.9558  3.7216  1.4895  1.0883
26/06/2017 00:00    1.00    1.1187  1.4771  1.9558  3.7259  1.4812  1.0881
23/06/2017 00:00    1.00    1.1173  1.4764  1.9558  3.7293  1.4783  1.0851
22/06/2017 00:00    1.00    1.1169  1.4812  1.9558  3.7164  1.4853  1.0867
21/06/2017 00:00    1.00    1.1147  1.4738  1.9558  3.7028  1.4803  1.0857
20/06/2017 00:00    1.00    1.1156  1.4674  1.9558  3.6845  1.4812  1.0854
19/06/2017 00:00    1.00    1.1199  1.4705  1.9558  3.6957  1.4827  1.087

然後我有一個這樣的銷售表：

UserName    Purchasedate        Currency    Price   Quantity    
John        24/06/2017 14:53    EUR         5.4     3
Jude        24/06/2017 18:47    USD         4       2
Jack        25/06/2017 07:34    USD         6.35    2
Jill        25/06/2017 11:42    EUR         4.15    1
Joe         26/06/2017 11:13    USD         4.50    1

我需要創建一個表格，其中包含給定貨幣的最新匯率，以及每日金額的摘要（如果可能），如下所示：

UserName    PurchaseDate        Currency    Price   Quantity    ExchangeRate    ConvertedAmountEUR
John        24/06/2017          EUR         5.4     3           1.00            16.20
Jude        24/06/2017          USD         4       2           1.1173           8.94       //Exchange rate of 23rd since for 24th is not available
DAILY TOTAL 24/06/2017                                                          25.14
Jack        25/06/2017          USD         6.35    2           1.1173          14.19       //Exchange rate of 23rd since for 24th is not available
Jill        25/06/2017          EUR         4.15    1           1.00             4.15
DAILY TOTAL 25/06/2017                                                          18.34
Joe         26/06/2017          USD         4.50    1           1.1187           5.03       //Exchange rate of 26th
DAILY TOTAL 26/06/2017                                                           5.03

我嘗試使用交叉連接來提取正確的匯率，但是存在“不匹配天數”的問題，而不能使用匯總進行小計，因為它需要分組，而我每次購買都需要單獨的行

甚至可以提出部分解決方案嗎？

謝謝

這可以逐步完成。

首先：我們有表格sales區分 thePurchaseDateTime和 the PurchaseDate：

CREATE TABLE sales
(
   UserName         varchar(100), 
   PurchaseDateTime datetime, 
   Currency         character(3),
   Price            numeric(10,2),
   Quantity         numeric(10,2),

   PurchaseDate AS CAST(PurchaseDateTime AS date),  -- Computed column
   PRIMARY KEY (UserName, PurchaseDateTime)
) ;

-- Secondary index, to help some JOINs
CREATE INDEX idx_sales_date ON sales(PurchaseDate, UserName);

這將使我們的生活更輕鬆，因為我們已經預先計算了日期。

您需要做的下一件事是取消透視exchange_rate表：

CREATE TABLE #exchange_rates_EUR 
(
   FXDate date,
   Currency char(3),
   ExchangeRate decimal(10,4),
   PRIMARY KEY (Currency, FXDate)
) ;

INSERT INTO #exchange_rates_EUR 
SELECT
   FXDate, 'EUR' AS Currency, EUR AS ExchangeRate
FROM
   exchange_rates_pivot
UNION
SELECT
   FXDate, 'USD' AS Currency, USD AS ExchangeRate
FROM
   exchange_rates_pivot
// do the same for all currencies .. omitted here for simplicity

注意：我使用了exchange_rates_pivot您原來的透視匯率表的名稱。

您需要此中間步驟才能稍後在正確的 JOIN 上執行（實際上您不能在行和列中進行 JOIN。您不能將列名視為值

$$ at least, not in any easy fashion $$). 您也可以使用UNPIVOTSQL Server 中的基礎結構，但這更符合 SQL 標準，儘管可能效率不高。

該表的內容如下所示：

SELECT TOP 12 * FROM #exchange_rates_EUR ORDER BY FXDate, Currency ;

外匯日期 | 貨幣 | 匯率
:------------------ | :------- | :-----------
19/06/2017 00:00:00 | 澳元 | 1.4705 
19/06/2017 00:00:00 | BGN | 1.9558 
19/06/2017 00:00:00 | 加元 | 1.4827 
19/06/2017 00:00:00 | 瑞士法郎 | 1.0870 
19/06/2017 00:00:00 | 歐元 1.0000 
19/06/2017 00:00:00 | 美元 | 1.1199 
20/06/2017 00:00:00 | 澳元 | 1.4674 
20/06/2017 00:00:00 | BGN | 1.9558 
20/06/2017 00:00:00 | 加元 | 1.4812 
20/06/2017 00:00:00 | 瑞士法郎 | 1.0854 
20/06/2017 00:00:00 | 歐元 1.0000 
20/06/2017 00:00:00 | 美元 | 1.1156

您需要做的第二件事是找到您所有人的匯率sales並將資訊放在一起。

對於每個特定的購買，匯率是通過以下方式找到的：

SELECT TOP 1
  ExchangeRate
FROM
  #exchange_rates_EUR er
WHERE
  er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
ORDER BY
  er.FXDate DESC ;

請注意， 和 的組合TOP 1將為ORDER BY您提供當天的貨幣兌換，如果不可用，則為提供此資訊的最接近的先前日期；正如我從你的例​​子中推斷出來的。為了完整起見，它實際上應該除以歐元的匯率，以防匯率數據不是基於歐元 = 1.00。在這一點上，我還沒有做到不要使事情過於復雜。

這將導致我們進行以下查詢：

SELECT
   q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate, 
   CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR
FROM
   (
   SELECT
       s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity, 
       -- Subquery to retrieve Exchange Rate
       CAST (
          (SELECT TOP 1
             ExchangeRate
          FROM
             #exchange_rates_EUR er
          WHERE
             er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
          ORDER BY
             er.FXDate DESC
          ) 
          AS decimal(10,4))          
       AS ExchangeRate
   FROM
       sales s
   ) AS q1 
ORDER BY 
   PurchaseDate, UserName ;

…這導致：

使用者名 | 購買日期 | 貨幣 | 價格 | 數量 | 匯率 | 兌換金額EUR
:------- | :------------------ | :------- | :---- | :------- | :----------- | :-----------------
約翰 | 24/06/2017 00:00:00 | 歐元 | 5.40 | 3.00 | 1.0000 | 16.20 
裘德 | 24/06/2017 00:00:00 | 美元 | 4.00 | 2.00 | 1.1173 | 8.94 
傑克 | 25/06/2017 00:00:00 | 美元 | 6.35 | 2.00 | 1.1173 | 14.19 
吉爾 | 25/06/2017 00:00:00 | 歐元 | 4.15 | 1.00 | 1.0000 | 4.15 
喬 | 26/06/2017 00:00:00 | 美元 | 4.50 | 1.00 | 1.1187 | 5.03

最後，我們還計算“每日總計”，並UNION ALL使用我們已有的數據執行 a。在WITH這種情況下，聲明會派上用場。需要一些小技巧來簡化排序（查找OrderBy列）。

這是通過以下語句完成的：

; WITH exchanged_sales AS
(
SELECT
   q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate, 
   CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR, 0 AS OrderBy
FROM
   (
   SELECT
       s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity, 
       -- Subqueries to retrieve Exchange Rate
       CAST (
          (SELECT TOP 1 ExchangeRate
          FROM      #exchange_rates_EUR er
          WHERE     er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
          ORDER BY  er.FXDate DESC) /    
          (SELECT TOP 1 ExchangeRate
          FROM      #exchange_rates_EUR er
          WHERE     er.Currency = 'EUR' AND er.FXDate <= s.PurchaseDate 
          ORDER BY  er.FXDate DESC) AS decimal(10,4)) AS ExchangeRate
   FROM
       sales s
   ) AS q1 
)
SELECT 
   UserName, PurchaseDate, Currency, Price, Quantity, ExchangeRate, ConvertedAmountEUR
FROM
   (-- The purchases date, user
   SELECT   *
   FROM     exchanged_sales
   UNION 
   -- The daily totals
   SELECT   'DAILY TOTAL' AS UserName, PurchaseDate, NULL AS Currency, NULL AS Price, NULL AS Quantity, NULL AS ExchangeRate,
       SUM(ConvertedAmountEUR) AS ConvertedAmountEUR, 1 AS OrderBy
   FROM     exchanged_sales
   GROUP BY PurchaseDate
   ) AS q
ORDER BY
   PurchaseDate, OrderBy, UserName ;

…其結果是：

使用者名 | 購買日期 | 貨幣 | 價格 | 數量 | 匯率 | 兌換金額EUR
:---------- | :----------- | :------- | :---- | :------- | :----------- | :-----------------
約翰 | 2017 年 6 月 24 日 | 歐元 | 5.40 | 3.00 | 1.0000 | 16.20 
裘德 | 2017 年 6 月 24 日 | 美元 | 4.00 | 2.00 | 1.1173 | 8.94 
每日總計 | 2017 年 6 月 24 日 | | | | | 25.14 
傑克 | 25/06/2017 | 美元 | 6.35 | 2.00 | 1.1173 | 14.19 
吉爾 | 25/06/2017 | 歐元 | 4.15 | 1.00 | 1.0000 | 4.15 
每日總計 | 25/06/2017 | | | | | 18.34 
喬 | 2017 年 6 月 26 日 | 美元 | 4.50 | 1.00 | 1.1187 | 5.03 
每日總計 | 2017 年 6 月 26 日 | | | | | 5.03

您可以在此處查看**dbfiddle的所有分步詳細資訊

如果您只需要一個語句中的所有內容，則可以將 #exchange_rate 的等價物作為 WITH 的一部分。這會給你一個非常大的聲明，不是那麼容易閱讀……對於多合一版本的dbfiddle這裡

引用自：https://dba.stackexchange.com/questions/177816

相關問答

Sql-Server

如何以某種方式使用 GROUP BY 連接一列中的數據，但過濾另一列中的特定數據

  • September 14, 2021
檢視問答
Sql-Server

SQL Server 查詢需要幫助：訪問不在 GROUP BY 子句中的列

  • August 8, 2019
檢視問答
Sql-Server

當我從外鍵約束中選擇不同的值時，結果重複

  • July 15, 2019
檢視問答
Sql-Server

使用 PIVOT 轉換多行多列

  • June 21, 2019
檢視問答
Sql-Server

使用 Pivot/Unpivot 將列轉換為行的 SQL 查詢

  • September 14, 2018
檢視問答
Sql-Server

使用可以跨越一天的 DATETIME 標記對會話進行分組

  • December 18, 2017
檢視問答