在一个答案下加入两个不同的查询
问题描述:
我有两个不同的查询产生了正确的结果,但我希望让他们在一个表中生成答案。我怎么做?在一个答案下加入两个不同的查询
这里是我的代码:
SELECT count(distinct ID) as NoOfEmployees
FROM Table_Name
WHERE date<= '2012-05-31';
select count(subA.ID) as EmployeesChanged from (
SELECT A.ID
FROM Table_Name A
WHERE A.date < '2012-06-01'
GROUP BY 1
HAVING COUNT(A.Service_type) > 1) subA
目前,我有以下的输出:
Number of Employees x
Employees Changed x
如何使它
Number of Employees | Employees Changed | (Number of employees - number changed) x | x | x
答
我不知道你使用什么数据库。但是,对于某些数据库,你可以尝试:
select q1.Value, q2.Value, q1.Value - q2.Value from
(SELECT count(distinct ID) as Value FROM Table_Name
WHERE date<= '2012-05-31') q1,
(select count(subA.ID) as Value from
(SELECT A.ID FROM Table_Name A
WHERE A.date < '2012-06-01' GROUP BY 1
HAVING COUNT(A.Service_type) > 1) subA) q2
答
您可以使用CROSS JOIN
:
SELECT a.*, b.*, a.NoOfEmployees - b.EmployeesChanged
FROM
(
SELECT count(distinct ID) as NoOfEmployees
FROM Table_Name
WHERE date<= '2012-05-31'
) a
CROSS JOIN
(
SELECT count(subA.ID) as EmployeesChanged
FROM
(
SELECT A.ID
FROM Table_Name A
WHERE A.date < '2012-06-01'
GROUP BY 1
HAVING COUNT(A.Service_type) > 1
) subA
) b
编辑:
您可能能够通过使用条件的聚集,而不是大大优化查询执行两个单独的查询:
SELECT a.NoOfEmployees, a.EmployeesChanged, a.NoOfEmployees - a.EmployeesChanged
FROM
(
SELECT
COUNT(DISTINCT CASE WHEN date <= '2012-05-31' THEN ID END) as NoOfEmployees,
COUNT(DISTINCT CASE WHEN date < '2012-06-01' AND COUNT(Service_type) > 1 THEN ID END) AS EmployeesChanged
FROM Table_Name
GROUP BY ID
) a
答
如果日期< ='2012-05-31'与A.date <'2012-06-01'相同?
SELECT COUNT(1) AS NoOfEmployees,
SUM(CASE WHEN STCount > 0 then 1 else 0 end) as HasChange,
SUM(CASE WHEN STCount = 0 then 1 else 0 end) as NoChange
FROM
(SELECT ID,
COUNT(A.Service_type) STCount
FROM Table_Name
WHERE date<= '2012-05-31'
GROUP BY ID) AS Data
非常感谢。 *。*究竟意味着什么?那是查询结果吗? – DatWunGuy102 2012-07-12 19:51:45
@ user1519731,是的。由于选择中只有一列,所以使用通配符“*”更简洁。 – 2012-07-12 19:52:47