如何在SQL PIVOT中订购

本文介绍了如何在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

我想做的事，但看起来像是拉伸，是通过DEC值递减的方式来ORDER BY结果.

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

这篇关于如何在SQL PIVOT中订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

dec