将多个查询合并为一个查询
问题描述:
我正在尝试将多个查询合并为一个查询。有人有主意吗?将多个查询合并为一个查询
SELECT In_year,count(In_year)AS Num_Of_Year FROM INTERNSHIP GROUP BY In_year;
SELECT In_qtr,计数(In_qtr)AS Num_Of_Qtr FROM INTERNSHIP GROUP BY In_Qtr;
SELECT In_location,count(In_location)AS Num_Of_Location FROM INTERNSHIP GROUP BY In_location;
如果我自己运行这些查询中的每一个,我就会得到我在查找的结果。我的问题是我无法弄清楚如何将这3个查询合并为1,我试图梳理它们,但最终得到错误的输出。谢谢!
答
可能UNION子句和组织这一点可以做的伎俩:
SELECT 'Year' As Topic, In_year As Value, count(In_year) AS Count
FROM INTERNSHIP
GROUP BY In_year
UNION ALL
SELECT 'Qtr' As Topic, In_qtr As Value, count(In_qtr) AS Count
FROM INTERNSHIP
GROUP BY In_Qtr
UNION ALL
SELECT 'Location' As Topic, In_location As Value, count(In_location) AS Count
FROM INTERNSHIP
GROUP BY In_location;
这表明它在单个查询的唯一途径,因为这三个SELECT都会有不同的行数,所以你不能添加自然形式的柱子。
答
我们可以用实习表作为驱动表“按组” SQLS其他连接:
select
t1.In_year, y.Num_Of_Year,
t1.In_qtr, q.Num_Of_Qtr,
t1.In_location, l.Num_Of_Location
FROM
(select distinct In_year, In_qtr, In_location from INTERNSHIP) t1
join (SELECT In_year, count(In_year) AS Num_Of_Year FROM INTERNSHIP GROUP BY In_year) y on t1.In_year = y.In_year
join (SELECT In_qtr, count(In_qtr) AS Num_Of_Qtr FROM INTERNSHIP GROUP BY In_Qtr) q on t1.In_Qtr = q.In_Qtr
join (SELECT In_location, count(In_location) AS Num_Of_Location FROM INTERNSHIP GROUP BY In_location) l on t1.In_location = l.In_location
谢谢!我明白你的答案是最好的。 – mpeytonfan18