mysql 查询中的两个 where 条件 [英] two where conditions in a mysql query

问题描述

我有一张像下面这样的表格

I have a table like below

|date|dom|guid|pid|errors|QA|comm|
|2010-03-22|xxxx.com|jsd3j234j|ab|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|ab|No|xxxxxx||
|2010-03-22|xxxx.com|jsd3j234j|if|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|if|No|xxxxxx||
|2010-03-22|xxxx.com|jsd3j234j|he|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|he|No|xxxxxx||

我想检索引用每个QA"的dom"总数，还需要QA"检测到的错误"计数

I want to retrieve the total count of "dom" referred to each "QA" and also I need the count of "errors" detected by the "QA"

SELECT date, count(dom), QA 
  FROM reports 
  WHERE date="2010-03-22" 
  GROUP BY QA

|2010-03-22|2|ab|
|2010-03-22|2|if|
|2010-03-22|2|he|

SELECT date, count(dom), count(errors), QA 
  FROM reports 
  WHERE errors="Yes" 
  GROUP BY QA

|2010-03-22|1|ab|
|2010-03-22|1|if|
|2010-03-22|1|he|

我想合并以上两个查询，可以吗.

I want to combine the above two queries, is it possible.

如果我使用下面的查询，我没有得到想要的结果.

If I use the below query, I am not getting the desired result.

SELECT date, count(dom), QA, count(errors) 
  FROM reports 
  WHERE date="2010-03-22" 
    AND errors="Yes" 
  GROUP BY QA

我想要下面的输出

|2010-03-22|2|ab|1|
|2010-03-22|2|if|1|
|2010-03-22|2|he|1|

推荐答案

您可以这样做:

SELECT date, COUNT(dom), QA, COUNT(NULLIF(errors, 'No')) FROM reports WHERE date="2010-03-22" GROUP BY QA

解释其工作原理:COUNT 返回非空值的数量.我们可以通过将带有 'No' 的错误转换为 NULL 来利用这一优势，这样 COUNT 就不会计算它们.我们使用 NULLIF 函数执行此操作，如果第一个和第二个参数相等，该函数将返回 NULL.

To explain how this works: COUNT returns the number of non-null values. We can use this to our advantage by turning errors with 'No' into NULL, so COUNTwon't count them. We do this with the NULLIF function, which returns NULL if the first and second arguments are equal.

这篇关于mysql 查询中的两个 where 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！