与 where 子句相矛盾的问题

Question

我试图显示每个用户本周花时间做什么（内部或外部工作），但时间都在表格的同一列上，是否可以将其分成 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

Answer 1

您可以使用 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

Answer 2

假设 FilteredMag_project.mag_typename 是“INTERNAL”或“EXTERNAL”，请尝试以下操作：

SELECT SUM(CASE FilteredMag_project.mag_typename 
                WHEN 'INTERNAL' THEN FilteredMag_Time.mag_hoursspent
                ELSE 0 END) AS InternalTime, 
       SUM(CASE FilteredMag_project.mag_typename 
                WHEN 'EXTERNAL' THEN FilteredMag_Time.mag_hoursspent
                ELSE 0 END) AS ExternalTime, 
       FilteredSystemUser.fullname
  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
ORDER BY FilteredSystemUser.fullname