一千萬個為什麽

搜索

與where子句相矛盾的問題

我試圖展示每個用戶在一周內所花費的時間(無論是內部工作還是外部工作),但時間都在表格上的同一列上,是否可以將其分成2個不同的列,並且仍然有它所以它每次只顯示每個用戶一次不是每次輸入時間,可能在整個星期內多次。

下面的SQL給我每個用戶跟蹤一周的時間,但不同行的內部和外部。

SELECT SUM(FilteredMag_Time.mag_hoursspent) AS Time, 
       FilteredSystemUser.fullname, 
       FilteredMag_project.mag_typename
  FROM FilteredSystemUser 
INNER JOIN FilteredMag_Task 
INNER JOIN FilteredMag_project ON FilteredMag_Task.mag_projectid = FilteredMag_project.mag_projectid 
INNER JOIN FilteredMag_Time ON FilteredMag_Task.mag_taskid = FilteredMag_Time.mag_taskid 
                            ON FilteredSystemUser.systemuserid = FilteredMag_Time.createdby
     WHERE (FilteredMag_Time.mag_starttime BETWEEN DATEADD(dd, - (DATEPART(dw, GETDATE()) - 1), GETDATE()) 
                                               AND DATEADD(dd, - (DATEPART(dw, GETDATE()) - 7), GETDATE()))
GROUP BY FilteredSystemUser.fullname, FilteredMag_project.mag_typename
ORDER BY FilteredSystemUser.fullname

這是當前輸出的一個例子。

Time                fullname             mag_typename
------------------ --------------------- -------------------------
1.2500000000        David Sutton        External
8.2500000000        Gayan Perera        External
9.0000000000        Paul Nieuwelaar     Internal
14.8700000000       Roshan Mehta        External
6.0000000000        Roshan Mehta        Internal
2.7800000000        Simon Phillips      External
4.6600000000        Simon Phillips      Internal

最佳答案

您可以使用SQL Server PIVOT

就像是

DECLARE @Table TABLE(
        userID INT,
        typeID VARCHAR(20),
        TimeSpent FLOAT
)

INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 2, 'INTERNAL', 1
INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 1, 'INTERNAL', 1
INSERT INTO @Table SELECT 2, 'EXTERNAL', 3
INSERT INTO @Table SELECT 1, 'EXTERNAL', 3

SELECT  *
FROM 
(
    SELECT  userID, typeID, TimeSpent
    FROM    @Table
) s
PIVOT   (SUM(TimeSpent) FOR typeID IN ([INTERNAL],[EXTERNAL])) pvt

輸出:

userID      INTERNAL               EXTERNAL
----------- ---------------------- ----------------------
1           3                      3
2           1                      3

轉載註明原文: 與where子句相矛盾的問題