代码之家 › 专栏 › 技术社区 › slowlygettingthere

显示请求的计数结果,水平显示作业结果,垂直显示位置3个表

join sql-server sql

slowlygettingthere · 技术社区 · 8 年前

因此,我有一个主要的数据表,其中包含针对作业和位置的ID,例如:

请求表dbo。请求

RequestID    JobID    LocationID
1            3        5
2            4        8
3            3        2

作业表dbo。乔布斯

JobID    JobTitle
1        Job 1
2        Job 2
3        Job 3

位置表dbo。位置

LocationID   LocationName
1            Location 1
2            Location 2
3            Location 3

现在我要做的是显示已完成作业和位置的计数列表,例如:

Job Title      Total Jobs    Location 1   Location 2  Location 3
Job 1          30            5            15          10
Job 2          10            2            2           6
Job 3          40            22           0           18

我第一部分很好地显示了作业和总计数,但我无法将要列出的位置放在右侧?

SELECT 
 job.JobTitle
,isnull(COUNT(req.JobID),0) AS 'Total Jobs'

FROM  Requests req 

RIGHT OUTER JOIN Jobs job
ON req.JobID = job.JobID

GROUP BY job.JobTitle
ORDER BY JobTitle

非常感谢

1 回复 | 直到 8 年前

Sergey Menshov 8 年前

您可以使用以下查询

SELECT JobID,JobTitle,[1],[2],[3],[4],[5],[6],[7],[8]
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN([1],[2],[3],[4],[5],[6],[7],[8])) p

如果您想使用动态列数,可以生成脚本并使用EXEC执行

DECLARE @locationIDs varchar(200)=''

SELECT @locationIDs+=CONCAT(',[',LocationID,']')
FROM Locations
ORDER BY LocationID

SET @locationIDs=STUFF(@locationIDs,1,1,'')

--PRINT @locationIDs

DECLARE @query varchar(1000)=CONCAT('SELECT JobID,JobTitle,',@locationIDs,'
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN(',@locationIDs,')) p')

--PRINT @query

EXEC(@query)

带有位置标题的变体

DECLARE
  @locationIDs varchar(200)='',
  @locationTitles varchar(2000)=''

SELECT
  @locationIDs+=CONCAT(',[',LocationID,']'),
  @locationTitles+=CONCAT(',[',LocationID,'] [',LocationName,']')
FROM Locations
ORDER BY LocationID

SET @locationIDs=STUFF(@locationIDs,1,1,'')
SET @locationTitles=STUFF(@locationTitles,1,1,'')

--PRINT @locationIDs
--PRINT @locationTitles

DECLARE @query varchar(2000)=CONCAT('SELECT JobID,JobTitle,',@locationTitles,'
FROM
  (
    SELECT j.JobID,j.JobTitle,r.LocationID,r.RequestID
    FROM Jobs j
    LEFT JOIN Requests r ON r.JobID=j.JobID
  ) q PIVOT(COUNT(RequestID) FOR LocationID IN(',@locationIDs,')) p')

--PRINT @query

EXEC(@query)