使用 SQL Server 2008 R2。我不确定这是否可能,但我想按两列进行分组并获取它们的差异并将它们放入新列中。用一个例子可能更容易解释:
BEFORE::
[DATE] [ID] [AMT] [TYPE]
2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 2 20
2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 10 21
2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 20
2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 21
AFTER::
[DATE] [ID] [AMT 20] [AMT 21]
2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 2 10
2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 4
这是我到目前为止的 SQL:
SELECT
CAST(TransDate AS DATE) AS [TransDate],ItemID,COUNT(TransactionTypeID) AS [TransAmt], TransactionTypeID
FROM
Transactions
WHERE
TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00'
AND TransactionTypeID IN (20,21)
GROUP BY
CAST(TransDate AS DATE),ItemID,TransactionTypeID
任何帮助表示赞赏,谢谢!
您可以将聚合函数与CASE
将行转换为列:
SELECT CAST(TransDate AS DATE) AS [TransDate],
ItemID,
count(case when TransactionTypeID=20 then TransactionTypeID end) Amt_20,
count(case when TransactionTypeID=21 then TransactionTypeID end) Amt_21
FROM Transactions
WHERE
TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00'
AND TransactionTypeID IN (20,21)
GROUP BY CAST(TransDate AS DATE),ItemID;
由于您使用的是 SQL Server,因此也可以使用PIVOT
功能:
select TransDate,
ItemId,
[20] as Amt_20,
[21] as Amt_21
FROM
(
SELECT CAST(TransDate AS DATE) AS [TransDate],
ItemID,
TransactionTypeID
FROM Transactions
WHERE TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00'
AND TransactionTypeID IN (20,21)
) d
pivot
(
count(TransactionTypeID)
for TransactionTypeID in ([20], [21])
) piv
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)