仅查找一条记录的分组查询(employee_id = 1 或 null) [英] Grouped query for find only one record (employee_id = 1 or null)

问题描述

我的数据库信息是

My db info is

如果employee_id 具有该记录的值，我不会获取每个工作日的唯一记录，否则employee_id 为空.我试试这个查询

I wont to get for each weekday unique record if employee_id has value that record, else employee_id is Null. I try this query

SELECT 
  * 
FROM
  `calendar` 
WHERE `employee_id` = 1 
  OR `employee_id` IS NULL 
GROUP BY `weekday` 

结果是

但我希望

你能帮我吗?谢谢

推荐答案

试试这个:

SELECT 
  b.calendar_id,
  a.employee_id,
  a.weekday 
FROM
  (SELECT 
    `weekday`,
    MAX(employee_id) AS employee_id 
  FROM
    calendar 
  WHERE employee_id = 1 
    OR employee_id IS NULL 
  GROUP BY `weekday`) a 
  LEFT JOIN calendar b 
    ON a.weekday = b.weekday 
    AND a.employee_id = b.employee_id ;

如有任何疑问，请告诉我.

Let me know in case of any clarifications.

这篇关于仅查找一条记录的分组查询(employee_id = 1 或 null)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！