mysql聚合查询返回错误结果

Bisoux 发表于 Dev

吻

我有两个表中的数据，我需要加入并返回其中一个表中记录的出现次数，

Employee表中的数据看起来像，

empId  workpatternId
    1        20

workPattern 表中的数据看起来像，

workpatternId  monday tuesday wednesday thursday friday saturday sunday
   20           ALL     ALL     ALL       ALL     NULL   NULL     ALL

以下查询应返回 5，即 ALL 的计数，但返回 7，

SELECT empId,b.workingPatternId, COUNT(monday='ALL') +
 COUNT(tuesday='ALL') + COUNT(wednesday='ALL')+ COUNT(thursday='ALL') + 
    COUNT(friday='ALL')+ COUNT(saturday='ALL')+ COUNT(sunday='ALL') AS COUNT
      FROM workPattern b 
 join Employee e on (e.workpatternId = b.workpatternId) and e.empId = 1
         GROUP BY empId ;

查询有什么问题？

编辑

数据库小提琴

专卖店

我没有看到聚合的意义，因为似乎workPattern每个empId. 你可以这样写：

SELECT e.empId, wp.workingPatternId, 
      (wp.monday    = 'ALL') 
    + (wp.tuesday   = 'ALL') 
    + (wp.wednesday = 'ALL') 
    + (wp.thursday  = 'ALL') 
    + (wp.friday    = 'ALL') 
    + (wp.saturday  = 'ALL') 
    + (wp.sunday    = 'ALL') cnt
FROM workPattern wp
INNER Employee e on e.workpatternId = wp.workpatternId 
WHERE e.empId = 1

如果您出于某种原因需要聚合，那么您想要sum()而不是count()：后者计算所有非空值，而错误条件被评估为0（不是null，因此在您的查询中将其考虑在内）：

SELECT 
    SUM(
          (wp.monday    = 'ALL') 
        + (wp.tuesday   = 'ALL') 
        + (wp.wednesday = 'ALL') 
        + (wp.thursday  = 'ALL') 
        + (wp.friday    = 'ALL') 
        + (wp.saturday  = 'ALL') 
        + (wp.sunday    = 'ALL')
    ) cnt
FROM workPattern wp
INNER JOIN Employee e on e.workpatternId = wp.workpatternId 
WHERE e.empId = 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。