如何将两个SQL语句合并成一个？

ThirstyCrow 2009-06-30 08:45:57

对于相同的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

...全文

207 21 打赏收藏转发到动态举报

写回复

用AI写文章

21 条回复

切换为时间正序

请发表友善的回复…

发表回复

dllxf1 2009-07-01

打赏
举报

于相同的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) A1,NVL(SUM(CASE WHEN A2>0 THEN A2 END),0) A2 FROM T1 WHERE ID=?

bestlea 2009-07-01

打赏
举报

同意7楼的

cab1225 2009-07-01

打赏
举报

学习了！！！！！

welyngj 2009-06-30

打赏
举报

还是不懂楼主的含义，楼主向怎么合并？达到什么目的？

wen_long2008 2009-06-30

打赏
举报

我是新手，大家多指点啊

inthirties 2009-06-30

打赏
举报

直接连接就可以

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)

smallbear923 2009-06-30

打赏
举报

[Quote=引用 13 楼 HEROWD 的回复:]
咋样哥们很牛吧。哈哈。
[/Quote]

学习了。

HEROWD 2009-06-30

打赏
举报

咋样哥们很牛吧。哈哈。

HEROWD 2009-06-30

打赏
举报

select sum(count),sum(anum) from (
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 = ?

阿三 2009-06-30

打赏
举报

不懂，加上union或union也是一个呀，只不是多个子查询后形成了一个sql

楼主给点实际的数据，你想显示的数据结果是什么样的？

ThirstyCrow 2009-06-30

打赏
举报

[Quote=引用 9 楼 welyngj 的回复:]
楼主的意思是只用一个select?
[/Quote]
正是

welyngj 2009-06-30

打赏
举报

楼主的意思是只用一个select?

suncrafted 2009-06-30

打赏
举报

如果不允许空值，
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)

suncrafted 2009-06-30

打赏
举报

SELECT R1.CT,R1.SUM_A,R2.CT,R2.SUM_A

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