代码之家 › 专栏 › 技术社区 › Muhammad Faizan Khan

SQL查询,用于在多个列中显示列的分组结果

sql mysql

Muhammad Faizan Khan · 技术社区 · 9 年前

尝试生成生成此类结果的查询

EmpId,  EmpSickLeave,EmpCasualLeave, EmpAnnualLeave, TotalLeave
1,       1,           0,                0            1
2,       0,           0,                2            2
3,       0,           1,                0            1
4,       0,           1,                0            1  
5,       1,           0,                0            1

而我有两张桌子

表EmpLeave::包括列EmpId、Date、EmpLeaveTypeID

表EmpLeaveType::包括EmpLeaveType ID和LeaveName

DATA表格

EmpLeaveType Table
EmpLeaveTypeID , LeaveName
1,              Sick Leave
2,              Annual
3,              Casual

EmpLeave Table
column EmpId, Date, EmpLeaveTypeID
            1, 2015-07-01, 1
            3, 2015-07-02, 2
            5, 2015-07-04, 1
            4, 2015-07-04, 2
            2, 2015-07-05, 2
            2, 2015-07-07, 2

我在揪头发,无法带来这种结果。这可能吗?

  EEmpId,  EmpSickLeave,EmpCasualLeave, EmpAnnualLeave, TotalLeave
1,       1,           0,                0            1
2,       0,           0,                2            2
3,       0,           1,                0            1
4,       0,           1,                0            1  
5,       1,           0,                0            1

这是我尝试过的错误的事情!

    SELECT * count(EmpLeaveTypeID) FROM `EmpLeaveType`
WHERE SwitchDate Between '2015-07-01' AND '2015-07-28'
group by EmpLeaveType, EmpId

4 回复 | 直到 4 年前

DhruvJoshi 9 年前

请尝试下面的SELECT查询。它没有考虑到休假类型可能有一天会改变。 SQL fiddle链接 http://sqlfiddle.com/#!9/0fd13/2 查询只需执行一个条件聚合来计算每个员工的休假。

SELECT 
EmpId, 
SUM(CASE WHEN EmpLeaveTypeID=1 THEN 1 ELSE 0 END) as EmpSickLeave,
SUM(CASE WHEN EmpLeaveTypeID=2 THEN 1 ELSE 0 END) as EmpCasualLeave,
SUM(CASE WHEN EmpLeaveTypeID=3 THEN 1 ELSE 0 END) as EmpAnnualLeave,
COUNT(1) as TotalLeave
FROM `EmpLeave`
WHERE Date Between '2015-07-01' AND '2015-07-28'
group by EmpId

lc. 9 年前

这看起来是一个条件计数,您只想对某些行进行计数。为此,可以使用 SUM 用一个 CASE 声明:

select EmpId as EEmpId,
    sum(case when EmpLeaveTypeID = 1 then 1 else 0 end) as EmpSickLeave,
    sum(case when EmpLeaveTypeID = 2 then 1 else 0 end) as EmpCasualLeave,
    sum(case when EmpLeaveTypeID = 3 then 1 else 0 end) as EmpAnnualLeave,
    count(*) as TotalLeave
from EmpLeave
where ...
group by EmpId

这样做的目的是添加 0 或 1 对于每一行,基于 EmpLeaveTypeID 比赛。然后可以使用 COUNT 获取 TotalLeave 正如我上面所展示的。

SQL Fiddle demo

Yathish Manjunath 9 年前

请检查以下内容:

 SELECT 
 temp.EmpId , 
 temp.EmpSickLeave, 
 temp.EmpCasualLeave, 
 temp.EmpAnnualLeave,
 (temp.EmpSickLeave + temp.EmpCasualLeave + temp.EmpAnnualLeave ) 
 as  TotalLeave 
 FROM 
( SELECT 
 EmpId, 
 SUM( CASE WHEN EmpLeaveTypeID=1 THEN 1 ELSE 0 END ) as EmpSickLeave,
 SUM( CASE WHEN EmpLeaveTypeID=2 THEN 1 ELSE 0 END ) as EmpCasualLeave,
 SUM( CASE WHEN EmpLeaveTypeID=3 THEN 1 ELSE 0 END ) as EmpAnnualLeave
 FROM EmpLeave 
 WHERE Date Between '2015-07-01' AND '2015-07-28'
 group by EmpId ) temp

DhruvJoshi 9 年前

我省略了 date between ,因为这很简单 where 附加

请尝试以下SQL查询:

select a.EmpId, count(Date), LeaveName from EmpLeave as a, mpLeaveType as b
where a.EmpLeaveTypeID = b.id
group by a.EmpId, a.EmpLeaveTypeID

检查 sqlfiddle