Sql查询帮助解决结构混乱

Question

我必须与blow结构表

• meetingID
• meetingDate
• meetingTitle

meetingsion 参加会议的人

• id
• meetingID
• personID
• IsPresent位

，下面是测试

的示例行

会议表行

• meetingID:1—2010/05/05—MeetingX
• meetingID:2—2010/05/07—MeetingY

MeetingPerson Table Rows

MeetingID:2
PersonID:1
IsPresent:true

MeetingID:2
PersonID:2
IsPresent:true
MeetingID:2
PersonID:3
IsPresent:false
MeetingID:2
PersonID:4
IsPresent:null

我需要查询显示在报告中，我需要有这个结果与样本日期，报告的标题

MeetingTitle | NumberOfPresent | NumberOfAbsents

示例数据的结果应该是这样的

Title:MeetingX | NumberOfPresent:0 | NumberOfAbsents:0
Title:MeetingY | NumberOfPresent:2 | NumberOfAbsents:1

Answer 1

Try

SELECT m.MeetingTitle,
       SUM(CASE WHEN p.IsPresent = 1 THEN 1 ELSE 0 END) NumberOfPresent,
       SUM(CASE WHEN p.IsPresent = 0 THEN 1 ELSE 0 END) NumberOfAbsents
  FROM Meeting m LEFT JOIN MeetingPerson p
    ON m.meetingID = p.meetingID
 GROUP BY m.meetingID

输出:

| MEETINGTITLE | NUMBEROFPRESENT | NUMBEROFABSENTS |
----------------------------------------------------
|     MeetingX |               0 |               0 |
|     MeetingY |               2 |               1 |

这里是SQLFiddle demo

Answer 2

请尝试一下。

SELECT M.MEETINGTITLE, SUM(CASE WHEN ISPRESENT ISNULL THEN 0 ELSE 1) NumberOfPresent , SUM(CASE WHEN ISPRESENT ISNULL THEN 1 ELSE 0) AS NOOFABSENTEES
FROM MEETING M INNER JOIN MEETINGPERSON P
WHERE M.MEETINGID=P.MEETINGID
GROUP BY M.MEETINGTITLE

谢谢。

Answer 3

您也可以使用以下动态查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when `IsPresent` = ',
      `IsPresent`,
      ' then 1 ELSE 0 END) AS `IsPresent_',
      `IsPresent`, '`'
    )
  ) INTO @sql
FROM MeetingPerson;
SET @sql = CONCAT('SELECT meetingTitle, ', @sql, '
                  FROM Meeting 
             LEFT JOIN MeetingPerson  
                    ON Meeting.meetingID = MeetingPerson.MeetingID
              GROUP BY meetingTitle 
                  ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果:

╔══════════════╦═════════════╦═════════════╗
║ MEETINGTITLE ║ ISPRESENT_1 ║ ISPRESENT_0 ║
╠══════════════╬═════════════╬═════════════╣
║ MeetingX     ║           0 ║           0 ║
║ MeetingY     ║           2 ║           1 ║
╚══════════════╩═════════════╩═════════════╝

查看SQLFiddle

Answer 4

SELECT
m.meetingtitle
,SUM(CASE WHEN i.ispresent = 1 THEN 1 ELSE 0 END) AS numberofabsents
,SUM(CASE WHEN i.ispresent = 0 THEN 1 ELSE 0 END) AS numberofabsents
FROM 
meeting m
LEFT OUTER JOIN meetingperson i on i.meetingid = m.meetingid
GROUP BY
m.meetingtitle