MySQL查询优化：移除重复DAY列并将NULL值转为0

修改后的完整SQL语句 #

set 
  @userid = '97c73a4d-11b2-4492-XXXX-XXXXXXXXXXXX';
set 
  @lastdate = '2024-08-31';
SET 
  @sql = NULL;
SELECT 
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'IFNULL(Count(CASE WHEN Checkedby = ''', 
      IFNULL(Checkedby, 0), 
      ''' THEN IFNULL(Checkedby,0) END), 0) AS ''', 
      CONCAT(FirstName, ' ', LastName), 
      ''''
    )
  ) INTO @sql 
FROM 
  Bookings B 
  JOIN USERINFO UI ON B.CheckedBy = UI.ID 
  left join tours T on T.TourID = B.TourID 
  left join Providers P on P.ProviderID = T.ProviderID;
SET 
  @sql = CONCAT (
    'SELECT  Y.Day, Y.DayName, IFNULL(X.Total, 0) as Total, ', 
    @sql, '
 FROM (SELECT ifnull(F.DAY,0) as DAY, IFNULL(Count(B.Checkedby),0) as Total, ', 
    @sql, ' FROM ORDERS O inner join BOOKINGS B on O.ORDERID = B.OrderID 
    left JOIN 
  (SELECT day(ifnull(N.Checkin,0)) as DAY, Count(N.Checkin) as Total FROM 
  Bookings N group by day(ifnull(N.Checkin,0))) F on F.DAY = day(B.Checkin)
 WHERE year(B.Checkin)*1 = year(@lastdate) and month(B.Checkin)*1 = month(@lastdate)  and 
 (B.UserID = @userid) 
 GROUP BY F.DAY) X RIGHT JOIN (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day , DAYNAME(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as DayName
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y ON X.DAY=Y.DAY order by Y.DAY'
  );
PREPARE stmt 
FROM 
  @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键修改说明 #

• 移除重复DAY列：将原主查询的SELECT Y.*,X.*替换为明确指定列的写法，只保留Y表的Day和DayName，去掉X表的DAY列，避免重复。
• 全局NULL转0：
  1. 生成动态用户计数列时，把Count(...)用IFNULL(..., 0)包裹，确保RIGHT JOIN后无对应数据的用户计数显示为0。
  2. 主查询中对X.Total用IFNULL(X.Total, 0)包裹，确保当天无预订记录时Total字段显示为0。
  3. 保留原X子查询中对Total的IFNULL处理，补全外部关联后的NULL值覆盖。

内容的提问来源于stack exchange，提问作者TemNyilv