如何在 SQL PIVOT 中排序
我目前有这个查询使用 PIVOT 生成这样的表:
I currently have this query using PIVOT generating a table like this:
USER | DEC | NOV | OCT
---------------------------------
bob | 3 | 5 | 2
jon | 7 | 0 | 1
tim | 4 | 2 | 6
我想做但看起来有点牵强的是，ORDER BY 按 DEC 值降序排列结果.
What I would like to do but it looks like a stretch is to ORDER BY the results by the DEC value descending.
这是查询:
with Mth (st, nd) as (
select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)
union all
select DATEADD (m, 1, st),
DATEADD (m, 1, nd)
from Mth
where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
)
select *
from
(
select MONTH(Mth.st) Month,
U.USER,
COUNT(S.QRY_ID) Searches
FROM Mth
LEFT JOIN SEARCHES S
on Mth.st <= S.CREATED
and Mth.nd > S.CREATED
LEFT JOIN MEMBERS U
on U.AID = S.AID
GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN
) src
pivot
(
sum(searches)
for month in ([12],[11],[10])
) piv
执行 piv ORDER BY piv.Searches 会报错，所以可以指定列吗?
Doing piv ORDER BY piv.Searches gives an error so is it possible to specify the column?
推荐答案
试试这个:
with Mth (st, nd) as (
select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)
union all
select DATEADD (m, 1, st),
DATEADD (m, 1, nd)
from Mth
where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
), Pivoted
AS
(
select *
from
(
select MONTH(Mth.st) Month,
U.USER,
COUNT(S.QRY_ID) Searches
FROM Mth
LEFT JOIN SEARCHES S
on Mth.st <= S.CREATED
and Mth.nd > S.CREATED
LEFT JOIN MEMBERS U
on U.AID = S.AID
GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN
) src
pivot
(
sum(searches)
for month in ([12],[11],[10])
) piv
)
SELECT *
FROM Pivoted
ORDER BY Dec
相关文章