SQL Server联接表语句

Question

我可以帮助您翻译以下内容：

我能否将以下三个语句合并以在一行中显示sick_total_day，other_total_day和other_total_day（相同的where子句）？

SELECT staff_key, 
       sum(days_applied) as sick_total_day 

from Tleave_sick_leave 

where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';


SELECT staff_key, 
       sum(days_applied) as other_total_day 
from Tleave_other_leave 
where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';


SELECT staff_key, 
       sum(days_applied) as other_total_day 

from Tleave_vleave 
where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';

- Joe Yan

---

您是想将这些记录合并成一个联合记录，还是想对这些表中所有记录的“days_applied”字段求和？ - Talha Ahmed Khan

Answer 1

这将解决重复的where子句问题，但会带来性能损失。 更好的解决方案可能是EMP提出的。规范化您的表以去除重复数据。

SELECT  staff_key
        , SUM(CASE WHEN type = 'Sick' THEN days_applied ELSE 0 END) as sick_total_day       
        , SUM(CASE WHEN type = 'Othert' THEN days_applied ELSE 0 END) as othert_total_day       
        , SUM(CASE WHEN type = 'Otherv' THEN days_applied ELSE 0 END) as otherv_total_day       
FROM    (
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , [Type] = 'Sick'
            FROM    Tleave_sick_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , 'Othert'
            FROM    Tleave_other_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , 'Otherv'
            FROM    Tleave_vleave
        )
WHERE   staff_key = 131 
        AND from_date>='2011/4/1 00:00:00' 
        AND to_date<='2011/4/30 00:00:00';
GROUP BY
        staff_key

- Lieven Keersmaekers

Answer 2

您可以将这三张表使用UNION连接起来，从而获得一个数据集。然后，您可以使用PIVOT将SUM函数转置为列。

这应该是有效的。虽然我没有用任何数据进行过测试。

SELECT
    [Tleave_sick_leave],
    [Tleave_other_leave],
    [Tleave_vleave]
FROM
(
    SELECT
        'Tleave_sick_leave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_sick_leave]
    UNION ALL
    SELECT
        'Tleave_other_leave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_other_leave]
    UNION ALL
    SELECT
        'Tleave_vleave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_vleave]
) [PivotData]
PIVOT
(
    SUM([days_applied])
FOR
    [Table]
IN
    (
        [Tleave_sick_leave],
        [Tleave_other_leave],
        [Tleave_vleave]
    )
) [Data]
WHERE
    [staff_key] = 131
AND
    [from_date] >= '2011/4/1 00:00:00' 
AND
    [to_date] <= '2011/4/30 00:00:00'

注意：这是一个针对SQL Server 2005及以上版本的方法。如果您使用的是2000或更低版本，则需要采用Lieven的答案，该答案实际上执行了相同的操作。

- Robin Day

Answer 3

你可能可以用一条语句完成它，但是你仍然需要3个单独的WHERE子句，因为每个表中的from_date和to_date列完全是独立的，尽管它们具有相同的名称。

听起来你在表设计上遇到了问题。你有3个非常相似的信息表。如果你可以将它们合并成一个表，那么你可能只需要一条查询语句。

- EMP

Answer 4

对Lieven提供的代码进行了一点修改，现在它可以工作了...

SELECT  a.staff_key
        , SUM(CASE WHEN a.type = 'Sick' THEN 
                        a.days_applied ELSE 0 END) as sick_total_day       
        , SUM(CASE WHEN a.type = 'Othert' THEN
                        a.days_applied ELSE 0 END) as othert_total_day       
        , SUM(CASE WHEN a.type = 'Otherv' THEN 
                        a.days_applied ELSE 0 END) as otherv_total_day       
FROM    
(
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , type = 'Sick'
            FROM    Tleave_sick_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    ,type = 'Othert'
            FROM    Tleave_other_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    ,type = 'Otherv'
            FROM    Tleave_vleave
) a
WHERE staff_key = '131' AND 
      from_date>='2011/4/1 00:00:00' AND 
      to_date<='2011/4/30 00:00:00'
GROUP BY staff_key

- Joe Yan