我正在寻找一条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。