多选一个sql语句 [英] multiple select in one sql statement

问题描述

我有一个调查答案表，例如：

I have a table of survey answers, something like:

date     |  q1  | q2 |
12/12/10 | yes | no | 
12/13/10 | no  | no | 

，我想创建一个单独的查询，我设置相关的日期范围。
我以下语句几乎工作：

and I would like to create a single query that will get me the results summary of this table, allowing me to set the relevant date range. I following statement pretty much works:

SELECT ( SELECT Count(*) 
         FROM `survey` 
         WHERE q1='Yes') AS q1_yes, 
       ( SELECT Count(*) 
         FROM `survey` 
         WHERE q1='No') AS q1_no,
       ( SELECT Count(*) 
         FROM `survey` 
         WHERE q2='Yes') AS q2_yes)

但我不知道如果我能做得更好，以及在哪里可以添加日期范围过滤。

But I'm not sure if I can do better than that, and also where to add the date range filtering.

推荐答案

来自spiny norman的第一个查询将给出如下结果：

The first query from spiny norman will give a result like that:

q1    q2    count(*)
no    yes   2
yes   no    1
yes   yes   1

只有两组具有不同的结果。我假设你想要按问题分组是/否的总数。在这种情况下，您必须这样做：

Which only groups couples of distinct results. I assume you want to group the overall number of yes/no by question. In that case, you'd have to do something like that:

SELECT 'q1' as Question, s1.q1 as Answer, count(*) as Count
FROM survey s1
WHERE date>='2010-10-01' AND date<'2010-10-30'
GROUP BY q1
UNION
SELECT 'q2' as Question, q2 as Answer, count(*) as Count
FROM survey
WHERE date>='2010-10-01' AND date<'2010-10-30'
GROUP BY q2

结果：

Question Answer    Count
q1       no        2
q1       yes       2
q2       no        1
q2       yes       3

这篇关于多选一个sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！