导航
  • 主页
  • Oracle 基础和管理
  • Oracle 高级技术
  • Oracle 认证与考试
  • 职位交流
  • 问答

多结果返回

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;
...全文
52 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
社区公告
暂无公告