如何将SQL/MySQL查询输出划分为多个表？

Question

我有以下查询：

SELECT COUNT (*) AS Total, Program, Status 
FROM APP_PGM_CHOICE 
WHERE Program IN ('EX', 'IM') 
AND APP_PGM_REQ_DT >= '20150101' 
AND APP_PGM_REQ_DT <= '20150131' 
AND Status IN ('PE','DN','AP') 
GROUP BY Program, Status 
ORDER BY Program, Status 

，输出是：

Total Program Status 
12246 "EX"  "AP" 
13963 "EX"  "DN" 
21317 "EX"  "PE" 
540  "IM"  "AP" 
2110 "IM"  "DN" 
7184 "IM"  "PE" 

而且我要像输出：

Total1 Program1 Total2 Program2 Status 
12246  EX  540  IM  AP 
13963  EX  2110  IM  DN 
21317  EX  7184  IM  PE 

我能做II？如果是最新的方式？

Answer 1

是的，你能做到这样简单的方法：

Select T1.Total Total1, T1.Program Program1, T2.Total Total2, T2.Program Program2, T1.Status 

From 
(SELECT COUNT (*) AS Total, Program, Status 
FROM APP_PGM_CHOICE 
WHERE Program = 'EX' 
AND APP_PGM_REQ_DT >= '20150101' 
AND APP_PGM_REQ_DT <= '20150131' 
AND Status IN ('PE','DN','AP') 
GROUP BY Program, Status 
ORDER BY Program, Status) T1 

INNER JOIN 

(SELECT COUNT (*) AS Total, Program, Status 
FROM APP_PGM_CHOICE 
WHERE Program = 'IM' 
AND APP_PGM_REQ_DT >= '20150101' 
AND APP_PGM_REQ_DT <= '20150131' 
AND Status IN ('PE','DN','AP') 
GROUP BY Program, Status 
ORDER BY Program, Status) T2 on T1.Status = T2.Status 

Answer 2

您可以用UNION查询和一些简单的选择做到这一点

SELECT GROUP_CONCAT(total1) as total1, GROUP_CONCAT(proram1) as program1, GROUP_CONCAT(total2) as total2, GROUP_CONCAT(program2) as program2 
FROM 
(SELECT total AS total1, program AS program1, null AS total2, null AS program2 
WHERE program = 'EX' 
UNION 
SELECT null AS total1, null AS program1, total AS total2, program AS program2 
WHERE program = 'IM') t 

这是枢行转换成列

Answer 3

在SQL温度表是非常有用和简单的基金..

简单的方法来获得输出。

选择Total，PROG，状态 进入#TempAA 从CGT 其中PROG = 'EX' 集团的地位，道达尔，PROG

从CGT选择Total，PROG，状态 进入#TempBB 其中Prog ='IM' 按状态分组，总计，编程

选择A.Total为[Total1]，A.Prog为[Program1]，B.Total为[Total2]，B.Prog为[Program2] ，A.Status 来自#TempAA A 内部连接#TempBB B在b.Status = A.status