mysql - 使用 3 个表并进行状态检查的棘手 SQL 语句

Question

也许有人可以阐明我一整天都在努力解决的问题 :)

我有3张 table

假期

holidayID | userID | dateFrom   | dateTo
1         |      1 | 2012-01-01 | 2012-01-01
2         |      1 | 2012-01-15 | 2012-01-20

状态

statusID | holidayID | statusText
1        |         1 | accepted
2        |         2 | declined

用户设置

id | userID | HolidaysAllowed
1  |      1 | 20

我想做的是得到以下结果

结果

HolidaysAllowed | HolidaysLeft (Allowed - Taken) | HolidaysTaken (Sum of Holidays)
20              |                             19 |      1

如果我不声明状态，我可以获得所有三列 - 这是我得到的最接近的列

SELECT 
IFNULL(SUM( IF(  h.dateTo = h.dateFrom, 1, DATEDIFF( h.dateTo, h.dateFrom ) ) ), 0) AS holidaysTaken, 
IFNULL(us.HolidaysAllowed - ( SUM(  IF(  h.DateTo = h.DateFrom, 1, DATEDIFF( h.DateTo, h.dateFrom ) ) ) ), 0) AS holidaysLeftover, 
us.HolidaysAllowed
FROM userSettings us 
LEFT JOIN holiday h 
ON h.userID = 1 
JOIN status s 
ON s.holidayID = h.holidayID AND s.statusID = 1 
WHERE 
us.userID = 1
GROUP BY h.userID;

主要问题是，如果没有 statusID = 2 的假期，则结果列 1 和 2 为 0(这是正确的)但列 3 (HolidaysAllowed) 为 NULL(不正确，因为它需要始终从表 UserSettings)。当至少有 1 个记录(假期)具有正确的状态时，上面的查询只会返回正确的响应......我做错了什么？ :)

这让我抓狂 :) 在此先感谢您的帮助!非常感谢!

更新

感谢大家评论...预期结果如下获取所有状态为 1(即已接受)的假期，然后根据允许的假期计算假期统计数据。即

总共 20 天，接受 1 个假期 = 还剩 19 天

再次感谢

Answer 1

SELECT u.userID, u.HolidaysAllowed,
u.holidaysAllowed - 
COALESCE( 
    (SELECT SUM( DATEDIFF( h1.dateTo, h1.dateFrom) + 1) 
         FROM holiday h1 
         INNER JOIN status s1
         ON s1.holidayID = h1.holidayID
         WHERE h1.userID = u.userID
         AND s1.statusID = 1
     ), 0) AS HolidaysLeft,
COALESCE( 
    (SELECT SUM( DATEDIFF( h2.dateTo, h2.dateFrom) + 1) 
         FROM holiday h2 
         INNER JOIN status s2
         ON s2.holidayID = h2.holidayID
         WHERE h2.userID = u.userID
         AND s2.statusID = 1
     ), 0) AS HolidaysTaken
FROM userSettings u
;