如何在数字范围上执行Pivot查询并对结果进行排序？

Question

我正在寻找一条MS SQL查询语句（用于SQL报告服务），以便为我提供以下结果:

JobCode   0-10     11-20     21-30    31-40    41-50
A           1        2         0        0        2
B           0        2         2        0        0
C           3        0         1        1        0

当前查询：

SELECT  RecordID
    , CAST(GETDATE() - JobStartDate AS Int) AS DaysSinceStart
    , Code
FROM    tblJobs

返回以下结果：

RecordID | DaysSinceStart | Code
158987    11    A
158968     3    A
158972     4    C
158973    16    B
158974    23    C
158975    13    B
158976    45    A
158977    32    C
158985     9    C
158981    25    B
158982    47    A
158983    18    A
158978     5    C
158979    27    B

我可以使用以下代码返回总体摘要，但它不允许我查看按JobCode分类的个别结果：

SELECT  Range AS [Day Range], COUNT(*) AS Jobs
FROM    (SELECT CASE WHEN DaysSinceStart BETWEEN 0 AND 9 THEN ' 0- 9'
                     WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19'
             WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29'
             WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39'
             WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49'
             WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59'
             WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69'
             WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79'
             WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89'
             WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' 
             ELSE 'Over 100' END AS Range
        FROM    (SELECT DaysSinceStart
                 FROM tblJobs) AS derivedtbl_1) AS t
GROUP BY Range

请问有谁可以帮忙按照“Code”字段进行分解吗？ 非常感谢。 附言：我正在使用MS SQL Server 2012。

- Brett

Answer 1

尝试这个：

试一试：

select * from 
(SELECT  code,Range AS [Day Range], COUNT(*) AS Jobs
FROM    (SELECT code,CASE WHEN DaysSinceStart BETWEEN 0 AND 9 THEN '0- 9'
                     WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19'
             WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29'
             WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39'
             WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49'
             WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59'
             WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69'
             WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79'
             WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89'
             WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' 
             ELSE 'Over 100' END AS Range
        FROM    (SELECT code,DaysSinceStart
                 FROM demo) AS derivedtbl_1) AS t
GROUP BY Range,code) q1
pivot
(max(jobs)
 for [Day Range] in ([0- 9],[10-19],[20-29],[30-39],[40-49],[50-59],
                     [60-69],[70-79],[80-89],[90-99],[Over 100])
)as pvt

SQL Fiddle

- Ravi Singh

---

非常感谢你，Ravi，这完美地完成了工作。 - Brett

Answer 2

尝试这个 -

查询：

SET NOCOUNT ON;

DECLARE @tblJobs TABLE
(
      RecordID INT
    , DaysSinceStart INT
    , Code CHAR(1)
)

INSERT INTO @tblJobs (RecordID, DaysSinceStart, Code)
VALUES 
    (158987,    11,    'A'), (158968,     3,    'A'),
    (158972,     4,    'C'), (158973,    16,    'B'),
    (158974,    23,    'C'), (158975,    13,    'B'),
    (158976,    45,    'A'), (158977,    32,    'C'),
    (158985,     9,    'C'), (158981,    25,    'B'),
    (158982,    47,    'A'), (158983,    18,    'A'),
    (158978,     5,    'C'), (158979,    27,    'B')

SELECT *
FROM (
    SELECT  
          t.Code
        , [Range] = 
            CASE 
                WHEN DaysSinceStart BETWEEN 0 AND 9   THEN '0-9'
                WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19'
                WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29'
                WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39'
                WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49'
                WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59'
                WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69'
                WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79'
                WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89'
                WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' 
                ELSE 'Over 100' 
            END
    FROM @tblJobs t
) o
PIVOT
(
    COUNT(o.[Range])
    FOR [Range] IN (
        [0-9], [10-19], [20-29],
        [30-39], [40-49], [50-59],
        [60-69], [70-79], [80-89], 
        [90-99], [Over 100]
    )
) pt

输出：

Code 0-9         10-19       20-29       30-39       40-49       50-59       60-69       70-79       80-89       90-99       Over 100
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A    1           2           0           0           2           0           0           0           0           0           0
B    0           2           2           0           0           0           0           0           0           0           0
C    3           0           1           1           0           0           0           0           0           0           0

- Devart

Answer 3

创建一个派生表/公共表表达式（CTE）/表变量/临时表或普通表来保存您的时间间隔。使用该表与 JobStartDate 和 getdate() 之间的 datediff 进行连接，以获取时间间隔。然后，您可以使用 pivot 对每个 Code 和 interval 计算值的数量。

with C(low, high, interval) as
(
  select 0,  10, '0-10'  union all
  select 11, 20, '11-20' union all
  select 21, 30, '21-30' union all
  select 31, 40, '31-40' union all
  select 41, 50, '41-50' union all
  select 51, 2147483647, 'over 50'
)
select Code, [0-10], [11-20], [21-30], [31-40], [41-50], [over 50]
from  (
      select J.Code,
             J.RecordID,
             C.interval
      from tblJobs as J
        inner join C
          on datediff(day, J.JobStartDate, getdate()) between C.low and C.high
      ) as T
pivot (
      count(T.RecordID) 
      for T.Interval in ([0-10], [11-20], [21-30], [31-40], [41-50], [over 50])
      ) as P

{{链接1：SQL Fiddle}}

- Mikael Eriksson

---

我和你在此问题上有着完全相同的想法，为什么要把它删除？ - ErikE

@ErikE 我不想在弄清楚他到底想要什么之前卷入混乱的讨论中。我会收回并观察发生了什么 :) - Mikael Eriksson

@ErikE 如果他在使用postgresql，可以使用这个神器：http://sqlfiddle.com/#!12/1197b/3 - Mikael Eriksson