一个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屋!