有什么辦法可以讓這兩個查詢成為一個查詢？我需要將所有這些都放在一張桌子上。目前我沒有使用任何公用表運算式，我相信如果我使用它們，我可以將這兩個代碼放入一個表中。
我還想要一個名為“G”的列，它是“E”和“F”的總和
有什么辦法可以將這兩個查詢合二為一嗎？
這是我的兩個單獨查詢的代碼：
DECLARE @Results TABLE
(
Code NVARCHAR(20),
Name NVARCHAR(100),
EFFECTIVEDATE DATE,
Reference NVARCHAR(20),
RATE Decimal(3,2)
)
INSERT INTO @Results
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DR.Rate AS [RATE]
FROM TradingP TP
RIGHT JOIN Schema S on S.TradingPId = TP.Id
RIGHT JOIN Deals D ON D.SchemaId = S.Id
LEFT JOIN DealResults DR on D.Id = DR.DealId
LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId
SELECT
[Code] 'Supplier Code',
[Name] 'Supplier',
[EFFECTIVEDATE] 'Effective Date',
[A] 'A %',
[B] 'B %',
[C] 'C %',
[D] 'D %',
[E] 'E %'
FROM @Results
PIVOT (
MAX(RATE)
FOR
Reference IN ([A], [B], [C], [D], [E])
)
as pivottable1
DECLARE @Results TABLE
(
Code NVARCHAR(20),
Name NVARCHAR(100),
EFFECTIVEDATE DATE,
Reference NVARCHAR(20),
RATE Decimal(3,2)
)
INSERT INTO @Results
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DTB.PercentageRate AS [RATE]
FROM TradingPartners TP
RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id
RIGHT JOIN Deals D ON D.SchemeId = S.Id
LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
WHERE PercentageRate IS NOT NULL AND PercentageRate >0.1
SELECT
[Code] 'Supplier Code',
[Name] 'Supplier',
[EFFECTIVEDATE] 'Effective Date',
[F] 'F %'
FROM @Results
PIVOT (
MAX(RATE)
FOR
Reference IN ([F])
)
as pivottable1
```
uj5u.com熱心網友回復：
您不需要 CTE；只需將兩組資料插入到您的表變數中。
此外，您應該避免datetime僅僅為了洗掉時間部分而將值轉換為字串；只需將其投射到date.
如果您正在測驗PercentageRate大于的記錄0.1，您也不需要測驗是否PercentageRate是NOT NULL; 一個NULL值不會通過大于測驗。
DECLARE @Results TABLE
(
Code NVARCHAR(20),
Name NVARCHAR(100),
EFFECTIVEDATE DATE,
Reference NVARCHAR(20),
RATE Decimal(3,2)
);
INSERT INTO @Results
(
Code,
Name,
EFFECTIVEDATE,
Reference,
RATE
)
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
CAST(D.StartDate As date) AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DR.Rate AS [RATE]
FROM
TradingP TP
RIGHT JOIN Schema S on S.TradingPId = TP.Id
RIGHT JOIN Deals D ON D.SchemaId = S.Id
LEFT JOIN DealResults DR on D.Id = DR.DealId
LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId
WHERE
TP.Reference IN ('A', 'B', 'C', 'D', 'E')
;
INSERT INTO @Results
(
Code,
Name,
EFFECTIVEDATE,
Reference,
RATE
)
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
CAST(D.StartDate As date) AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DTB.PercentageRate AS [RATE]
FROM
TradingPartners TP
RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id
RIGHT JOIN Deals D ON D.SchemeId = S.Id
LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
WHERE
TP.Reference = 'F'
And
DTB.PercentageRate > 0.1
;
SELECT
[Code] 'Supplier Code',
[Name] 'Supplier',
[EFFECTIVEDATE] 'Effective Date',
[A] 'A %',
[B] 'B %',
[C] 'C %',
[D] 'D %',
[E] 'E %',
[F] 'F %',
[E] [F] 'G %'
FROM
@Results
PIVOT
(
MAX(RATE)
FOR Reference IN ([A], [B], [C], [D], [E], [F])
) As pivottable1
;
uj5u.com熱心網友回復：
只是說，“公用表運算式”或 CTE 就像是子查詢的可重用模板。
例如，這個查詢：
WITH CTE_DATA AS (select * from tableA)
SELECT * FROM CTE_DATA AS data;
回傳與此查詢完全相同：
SELECT * FROM (select * from tableA) AS data;
但是要結合您的兩個查詢，您可以將它們合并到資料透視的源查詢中。
WITH CTE1 AS (
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DR.Rate AS [RATE]
FROM TradingP TP
RIGHT JOIN Schema S on S.TradingPId = TP.Id
RIGHT JOIN Deals D ON D.SchemaId = S.Id
LEFT JOIN DealResults DR on D.Id = DR.DealId
LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId
WHERE D.Reference IN ('A', 'B', 'C', 'D', 'E')
)
, CTE2 AS (
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],
DTB.PercentageRate
FROM TradingPartners TP
RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id
RIGHT JOIN Deals D ON D.SchemeId = S.Id
LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
WHERE D.Reference IN ('F')
AND DBT.PercentageRate > 0.1
)
SELECT
[Code] AS [Supplier Code],
[Name] AS [Supplier],
[EFFECTIVEDATE] AS [Effective Date],
[A] AS [A %],
[B] AS [B %],
[C] AS [C %],
[D] AS [D %],
[E] AS [E %],
[F] AS [F %]
FROM (
SELECT Code, Name, EFFECTIVEDATE
, Reference
, RATE
FROM CTE1
UNION ALL
SELECT Code, Name, EFFECTIVEDATE
, Reference
, PercentageRate
FROM CTE2
) Src
PIVOT (
MAX(RATE)
FOR Reference IN ([A], [B], [C], [D], [E], [F])
) Pvt;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425354.html