多结果返回

delectation 2009-12-29 02:05:35
在Oracle数据库orders是一张表,请问如何一个语句,可以返回下面五条语句的结果

select count(*) num1 from orders;
select count(*) num1 from orders where state=1;
select Sum(Price) num2 from orders where state=1;
select count(*) num3 from orders where state=0;
select Sum(Price) num4 from orders where state=0;
...全文
70 8 打赏 收藏 举报
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ojuju10 2009-12-29
[Quote=引用 4 楼 abstruct 的回复:]
select count(1) as num,
      count(case when state = 1 then '1' end) as num1,
      sum(case when state = 1 then price end) as num2,
      count(case when state = 0 then '1' end) as num3,
      sum(case when state = 0 then price end) as num4
  from orders
[/Quote]

正解
  • 打赏
  • 举报
回复
fuyou001 2009-12-29
[Quote=引用 3 楼 sql_xep 的回复:]
用union
[/Quote]
效率会高些
  • 打赏
  • 举报
回复
crazylaa 2009-12-29
楼上都对
  • 打赏
  • 举报
回复
Phoenix_99 2009-12-29
select count(*) num,sum(decode (state,1,1,0)) num1,sum(decode (state,1,Price,0)) num2,sum(decode (state,0,1,0)) num3,sum(decode (state,0,Price,0)) from ordrs
  • 打赏
  • 举报
回复
安特矮油 2009-12-29
select count(1) as num,
count(case when state = 1 then '1' end) as num1,
sum(case when state = 1 then price end) as num2,
count(case when state = 0 then '1' end) as num3,
sum(case when state = 0 then price end) as num4
from orders
  • 打赏
  • 举报
回复
sql_xeppp 2009-12-29
用union
  • 打赏
  • 举报
回复
qq82296344 2009-12-29
select count(*) num,sum(decode (state,1,1,0)) num1,sum(decode (state,1,Price,0)) num2,sum(decode (state,0,1,0)) num3,sum(decode (state,0,Price,0)) num4 from ordrs
  • 打赏
  • 举报
回复
qq82296344 2009-12-29
select count(*) num1 from orders;
select count(*) num1 from orders where state=1;
select Sum(Price) num2 from orders where state=1;
select count(*) num3 from orders where state=0;
select Sum(Price) num4 from orders where state=0;

select count(*) num,sum(decode (state,1,1,0)) num1,sum(decode (state,1,Price,0)) num2,sum(decode (state,0,1,0)) num3,sum(decode (state,0,Price,0)) from ordrs
  • 打赏
  • 举报
回复
发帖
Oracle

1.6w+

社区成员

Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2009-12-29 02:05
社区公告
暂无公告