时间戳

首页 > 解决方案 > mysql聚合查询返回错误结果

mysql - mysql聚合查询返回错误结果

问题描述

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

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 ;

查询有什么问题？

编辑

小提琴手

标签： mysqlsqlcountinner-joinmysql-8.0

解决方案

workPattern我没有看到聚合的意义，因为似乎您在per中只有一行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

推荐阅读