如何内部查询UNION与GROUP BY和WHERE条件

Question

My sqlfiddle

my DB schema and query online

查询1

SELECT pk_video_master_id AS _id,
video_title AS title1,
COUNT(fk_video_master_id) AS sub_title_1,
SUM(video_detail_length) AS sub_title_2,
-1007 AS module 
FROM tbl_video_master 
LEFT JOIN tbl_video_detail ON fk_video_master_id = pk_video_master_id 
WHERE title1 LIKE '%a%' GROUP BY _id

查询2

 SELECT pk_surveys_id AS _id ,
 surveys_title AS title1,
 surveys_end_date AS sub_title_1,
 COUNT(fk_survey_detail_survey_id) AS sub_title_2, 
-1006 AS module
 FROM tbl_surveys 
 LEFT JOIN tbl_survey_detail ON fk_survey_detail_survey_id = pk_surveys_id 
 WHERE title1 LIKE '%a%' GROUP BY _id

我想联合这2查询，我也创建了

SELECT pk_video_master_id AS _id,
video_title AS title1,COUNT(fk_video_master_id) AS sub_title_1,
SUM(video_detail_length) AS sub_title_2,-1007 AS module 
FROM tbl_video_master 
LEFT JOIN tbl_video_detail ON fk_video_master_id = pk_video_master_id 
UNION 
SELECT pk_surveys_id AS _id ,surveys_title AS title1,
surveys_end_date AS sub_title_1,
COUNT(fk_survey_detail_survey_id) AS sub_title_2, 
-1006 AS module
FROM tbl_surveys 
LEFT JOIN tbl_survey_detail ON fk_survey_detail_survey_id = pk_surveys_id 
WHERE title1 LIKE '%a%' GROUP BY module

但是它只返回2个记录

我想要的输出是搜索两个表TITLE1字段

所以请帮助我

Answer 1

从query1中删除了GROUP BY。大多数其他DBMS都会抛出一个错误，但是SQLite只返回一条带有随机信息的记录。顺便说一下，您还删除了WHERE子句。

因此，将complete查询1粘合到complete查询2。并且使用UNION ALL而不是UNION，因为无论如何都没有需要删除的重复项。

SELECT pk_video_master_id AS _id,
video_title AS title1,COUNT(fk_video_master_id) AS sub_title_1,
SUM(video_detail_length) AS sub_title_2,-1007 AS module 
FROM tbl_video_master 
LEFT JOIN tbl_video_detail ON fk_video_master_id = pk_video_master_id 
WHERE title1 LIKE '%a%' GROUP BY _id
UNION ALL
SELECT pk_surveys_id AS _id ,surveys_title AS title1,
surveys_end_date AS sub_title_1,
COUNT(fk_survey_detail_survey_id) AS sub_title_2, 
-1006 AS module
FROM tbl_surveys 
LEFT JOIN tbl_survey_detail ON fk_survey_detail_survey_id = pk_surveys_id 
WHERE title1 LIKE '%a%' GROUP BY module;

这是你的SQL文件:http://sqlfiddle.com/#!5/03ecc/14.