如何将不同的查询结果合并到单个查询结果表中 [英] How to combine different queries results into a single query result table

问题描述

这是一件事.我试图找到一个可以包含所有3个结果的查询.但是我只知道如何查询其中一个.

Here is a thing.I am trying to find a query which can include all there 3 results. But I I only know how to do the query for one of them each.

问题: 对于至少有200名成员参与的每个调查，请提供以下信息: 1)调查ID和调查说明 2)开始调查的会员数 3)已经完成的成员数.

Questions: For each survey that has had at least 200 members participate, provide the following information: 1）Survey ID and Survey Description 2）Number of members that started the survey 3) Number of members that already finished.

查询参与人数至少为200的调查ID和调查说明

query for survey ID and survey description which have at least 200 participations

1)

Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
from  Tbl_survey,Tbl_member_participation
where Tbl-survey.member_id = Tbl_member_participation.member_id
group by survey_id, Survey_desc
having totalnumber >= 200

2)查询已开始但尚未结束的成员数.

2) query for number of members that started but not finished.

select count(survey_id)
from  Tbl_survey, 
where survey_id exists ( Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
                       from  Tbl_survey,Tbl_member_participation
                       where Tbl-survey.member_id = Tbl_member_participation.member_id
                       group by survey_id, Survey_desc
                       having totalnumber >= 200) and
     finishi_date is **null**   
group by survey_id

3)查询已经完成的成员数.

3) query for number of members that already finished.

select count(survey_id)
from  Tbl_survey, 
where survey_id exists ( Select survey_id, Survey_desc, count(Tbl_survey.member_id) as totalnumber
                       from  Tbl_survey,Tbl_member_participation
                       where Tbl-survey.member_id = Tbl_member_participation.member_id
                       group by survey_id, Survey_desc
                       having totalnumber >= 200) and
     finishi_date is ***not null***   
group by survey_id

所以我只想一种方法可以将这三个结果结合起来 调查ID，调查描述，开始人员，完成人员..仅在一个查询表中.

SO I just want a way can combine these three results like Survey Id, Survey desc, start people, finish people. in a only one query table.

推荐答案

首先，您应该正确查询.

First of all, you should get your queries right.

1)您的第一个查询是可以的，但您不应使用以逗号分隔的过时联接语法.改用显式联接(二十年前在SQL标准中引入的显式联接，btw).

1) Your first query is okay, but you should not use the out-dated comma-separated join syntax. Use explicit joins instead (that where introduced in SQL standard some twenty years ago, btw).

select s.survey_id, s.survey_desc, count(*) as totalnumber
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id, s.survey_desc
having count(*) >= 200;

2,3)您的第二个和第三个查询在语法上已损坏.您有点把EXISTS和IN混在一起.此外，finishi_date可能是tbl_member_participation的成员，因此您必须再次加入该表.这是更正查询2的方法:

2,3) Your second and third queries are syntactically broken. You are kind of mixing EXISTS and IN. Moreover finishi_date is likely to be a member of tbl_member_participation, so you must join this table again. Here is query 2 corrected:

select s.survey_id, count(*)
from  tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
where survey_id in
(
  select s.survey_id
  from tbl_survey s
  join tbl_member_participation mp on s.member_id = mp.member_id
  group by s.survey_id
  having count(*) >= 200
)
and mp.finishi_date is null
group by s.survey_id;

要结合所有这三个，您不必使用EXISTS或IN.查询1中已经提供了所有需要的数据.看，我如何修改查询1以获得更简单的查询2:

In order to combine all three, you don't have to use EXISTS or IN. All the data needed is available in query 1 already. Look, how I modify query 1 to get to a much simpler query 2:

select 
  s.survey_id, 
  sum(case when mp.finishi_date is null then 1 else 0 end) as count_unfinished
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id
having count(*) >= 200;

话虽如此，您的最终查询是这样:

Having said this, your final query is this:

select 
  s.survey_id, 
  s.survey_desc, 
  sum(case when mp.finishi_date is null then 1 else 0 end) as count_unfinished,
  sum(case when mp.finishi_date is not null then 1 else 0 end) as count_finished,
  count(*) as totalnumber
from tbl_survey s
join tbl_member_participation mp on s.member_id = mp.member_id
group by s.survey_id, s.survey_desc
having count(*) >= 200;

这篇关于如何将不同的查询结果合并到单个查询结果表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！