对于相同的ID,如何将以下两个SQL语句合并成一个?
SELECT COUNT(*), NVL(SUM(A1), 0) FROM T1 WHERE ID = ?
SELECT COUNT(*), NVL(SUM(A2), 0) FROM T1 WHERE ID = ? AND A2 > 0
SELECT COUNT(*), NVL(SUM(A1), 0) FROM T1 WHERE ID = ?
SELECT COUNT(*), NVL(SUM(A2), 0) FROM T1 WHERE ID = ? AND A2 > 0
用union/union all不可以吗?
SELECT COUNT(*), NVL(SUM(A1), 0) FROM T1 WHERE ID = ?
union
SELECT COUNT(*), NVL(SUM(A2), 0) FROM T1 WHERE ID = ? AND A2 > 0
FROM
(
SELECT COUNT(*) AS CT, NVL(SUM(A1), 0) AS SUM_A,1 AS join_column FROM T1 WHERE ID = ?
) R1
FULL OUTER JOIN
(
SELECT COUNT(*) AS CT, NVL(SUM(A2), 0) AS SUM_A,1 AS join_column FROM T1 WHERE ID = ? AND A2 > 0
) R2 ON R1.join_column = R2.join_column
SELECT R1.CT,R1.SUM_A,R2.CT,R2.SUM_A
改为
SELECT NVL(R1.CT,0),NVL(R1.SUM_A,0),NVL(R2.CT,0),NVL(R2.SUM_A,0)
SELECT id,COUNT(*) count, NVL(SUM(A1), 0) anum FROM T1 group by id
union all
SELECT id,COUNT(*) count, NVL(SUM(A2), 0) anum FROM T1 group by id WHERE A2 > 0
) a
where a.ID = ?
select * from (SELECT COUNT(*), NVL(SUM(A1), 0) FROM T1 WHERE ID = ?), (SELECT COUNT(*), NVL(SUM(A2), 0) FROM T1 WHERE ID = ? AND A2 > 0)
,SUM(CASE WHEN A2 > 0 THEN 1 ELSE 0 END )
,NVL(SUM(CASE WHEN A2 > 0 THEN A2 ELSE 0 END ),0)
FROM T1 WHERE ID = ?
SELECT COUNT(*), NVL(SUM(A1), 0) FROM T1 WHERE ID = ?
SELECT COUNT(*), NVL(SUM(A2), 0) FROM T1 WHERE ID = ? AND A2 > 0我的答案是这个!不知是不是楼主要的结果??SELECT COUNT(*),NVL(SUM(A1),0) A1,NVL(SUM(CASE WHEN A2>0 THEN A2 END),0) A2 FROM T1 WHERE ID=?