代码之家  ›  专栏  ›  技术社区  ›  Moeez

不提供正确结果的case计数

case count sql mysql
  •  0
  • Moeez  · 技术社区  · 7 年前

    我正在使用 query 生成计数。下面是我的疑问 

    SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Zong No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Mobilink No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Ufone No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Telenor No Signal'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE NULL END) AS 'Wall'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE NULL END) AS 'PC Pole'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE NULL END) AS 'Structure pole'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE NULL END) AS 'Spon pole'
 ,sd.`sub_div_code` AS 'SD Code',  
 sd.`name` AS 'SD Name', 
 sd.`circle_name` AS 'Circle Name', 
 sd.`division_name` AS 'Division Name'
 FROM `survey` sur 
 INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
 sd.`sub_div_code`
 INNER JOIN `survey_networks` sn ON sur.`id` = sn.`survey_id`
 WHERE sur.`customer_id` IN ('37010185878',
'37010718785',
'37010718759',
'37010357911',
'37010673539',
'37010673796',
'37010672166',
'37010672162')
 GROUP BY sd.`name`

    所有计数均正确,但以下部分的值不正确 

    ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE NULL END) AS 'Wall'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE NULL END) AS 'PC Pole'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE NULL END) AS 'Structure pole'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE NULL END) AS 'Spon pole'

    他们的输出是 10 ,则, 4 ,则, 24 和 0 。但实际计数是 4. ,则, 1 ,则, 7 和 0

    示例输出为

    enter image description here

    最后一个值 spon pole 是 0 对于某些记录,但并非所有记录,因此其计数也不正确。

    如何获得这些值的正确计数?我也试过了 = 登录替换 LIKE 但它仍然不能给我正确的结果。我也看到了这个 solution

    任何帮助都将不胜感激

    2 回复  |  直到 7 年前
        1
  •  1
  •   D-Shih    7 年前

    您可以使用 SUM 而不是 Count 和 ELSE 设置 0 

    SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
,sur.`sub_division`
FROM `survey` sur

    如果要区分 sur.sub_division 只需添加 group by by sur.sub_division 

    SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
,sur.`sub_division`
FROM `survey` sur 
GROUP BY sur.`sub_division`

    编辑

    我想问题出在 Group by filed 你可以试试这个。 

    SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Zong No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Mobilink No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Ufone No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Telenor No Signal'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
     ,sd.`sub_div_code` AS 'SD Code',  
     sd.`name` AS 'SD Name', 
     sd.`circle_name` AS 'Circle Name', 
     sd.`division_name` AS 'Division Name'
 FROM `survey` sur 
 INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
 sd.`sub_div_code`
 INNER JOIN `survey_networks` sn ON sur.`id` = sn.`survey_id`
 WHERE sur.`customer_id` IN 
 ('37010185878',
'37010718785',
'37010718759',
'37010357911',
'37010673539',
'37010673796',
'37010672166',
'37010672162')
 GROUP BY 
     sd.`sub_div_code`, 
     sd.`name`,
     sd.`circle_name`, 
     sd.`division_name`

    SQLFiddle

        2
  •  0
  •   Moeez    7 年前

    因此,经过大量搜索,我能够找到正确的查询,从而得到正确的结果 

    SELECT SUM(z.Survey_Done) AS 'Survey Done',SUM(Zong) AS 'Zong No Signal',SUM(Mobilink) AS 'Mobilink No Signal',SUM(Ufone) AS 'Ufone No Signal',SUM(Telenor) AS 'Telenor No Signal'
,SUM(Wall) AS Wall,SUM(PC_Pole) AS 'PC Pole',SUM(Structure_pole) AS 'Structure Pole',SUM(Spon_pole) AS 'Spon Pole',SDCode
,sd.`name` AS 'SD Name' 
,sd.`circle_name` AS 'Circle Name'
,sd.`division_name` AS 'Division Name'
FROM (
SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey_Done',
0 AS 'Zong',
0 AS 'Mobilink',
0 AS 'Ufone',
0 AS 'Telenor'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC_Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure_pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon_pole'
,sd.`sub_div_code` AS 'SDCode'

FROM `survey` sur 
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
sd.`sub_div_code`

WHERE sur.`customer_id` IN ()
GROUP BY sd.`sub_div_code`, sd.`name`, sd.`circle_name`, sd.`division_name`
UNION

SELECT 
0 AS 'Survey_Done',
SUM(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No 
Signal' THEN 1 ELSE 0 END) AS 'Zong'
,SUM(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Mobilink'
,SUM(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Ufone'
,SUM(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Telenor'
,0 AS 'Wall'
,0 AS 'PC Pole'
,0 AS 'Structure pole'
,0 AS 'Spon pole'
,sd.`sub_div_code` AS 'SDCode'
FROM  `survey_networks` sn 
INNER JOIN `survey` sur ON sur.`id` = sn.`survey_id`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
sd.`sub_div_code`

WHERE sur.`customer_id` IN ()
GROUP BY sd.`sub_div_code`
) z
INNER JOIN `survey_hesco_subdivision` sd ON sd.`sub_div_code`=SDCode
GROUP BY sd.`name`

    我用过 UNION 在上述查询中。

    推荐文章
    Badal Solanki  ·  如何在MySQL中对字符串进行算术运算?
    1 年前
    Saleh Mehdiyev  ·  我想重构我的Laravel代码以遵守SOLID原则
    1 年前
    hello_programmers  ·  Mysql从其他表输出一列
    1 年前
    Community wiki  ·  这个MySQL语句出了什么问题?
    1 年前
    Community wiki  ·  优化从同一表中提取的多列的查询
    1 年前
    IJustSentYouAPackage  ·  MySQL错误:引用的表中缺少约束的列[已关闭]
    1 年前
    Popo  ·  Sql查询:返回数据库中不可用的where条件
    1 年前
    Roberts  ·  DATE_FORMAT和STR_TO_DATE不工作
    1 年前
    Hamdan Nuramdani  ·  对账单中一周内不同表中的数据求和
    1 年前
    Kugelfisch  ·  用php为数据库加密数据
    1 年前
    关于  
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号