请教一下联合语句的写法(用join好像比较难实现啊),请高手帮忙一下

yesjoy 2006-09-28 09:24:40
select sum(NUMBER_OF_PERSONS) as a from T_ES_TEACHEXPERIMENT where type='1'
上面一句查询结果为:
a
30

select sum(USE_HOURS) as b from T_ES_TEACHEXPERIMENT where method in('2','3')
上面一句查询结果为:
b
130

如上两句均是在一个表中统计查询的结果,现在我想将结果合并在一起然后执行
期望能够得到如下的结果:
a b
30 130
请问如何来实现呢?谢谢
...全文
122 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
superman_yc 2006-09-28
  • 打赏
  • 举报
回复
select
(case when type='1' then sum(NUMBER_OF_PERSONS) end) as a,
(case when method in('2','3') then sum(USE_HOURS) end) as b
from T_ES_TEACHEXPERIMENT
冷箫轻笛 2006-09-28
  • 打赏
  • 举报
回复
select
sum(case when type='1' then NUMBER_OF_PERSONS else 0 end) as a,
sum(case when method in('2','3') then USE_HOURS else 0 end) as b
from T_ES_TEACHEXPERIMENT
where year='1996'
allright_flash 2006-09-28
  • 打赏
  • 举报
回复
select * from (select sum(NUMBER_OF_PERSONS) as a from T_ES_TEACHEXPERIMENT where type='1' as a,
select sum(USE_HOURS) as b from T_ES_TEACHEXPERIMENT where method in('2','3') as b) c
yesjoy 2006-09-28
  • 打赏
  • 举报
回复
to coolingpipe(冷箫轻笛):
有问题想请教一下,如果我的两条sql语句中都有相同的一个条件,我是否可以共用呢?
比如下面两句中都有一个条件year='1996:
select sum(NUMBER_OF_PERSONS) as a from T_ES_TEACHEXPERIMENT where type='1'and year='1996'
select sum(USE_HOURS) as b from T_ES_TEACHEXPERIMENT where method in('2','3') and year='1996

那么我要合并的时候是否能够只写一次啊,因为我实际项目中这个共用条件比较长,总不能都这样写吧:
select a,b from
(select sum(NUMBER_OF_PERSONS) as a from T_ES_TEACHEXPERIMENT where type='1' and year='1996)t1,
(select sum(USE_HOURS) as b from T_ES_TEACHEXPERIMENT where method in('2','3') and year='1996)t2

多谢你的答案,用临时表确实是个好办法
九斤半 2006-09-28
  • 打赏
  • 举报
回复
写完了,一个子查询;一般还是用下面这个~~~

coolingpipe(冷箫轻笛) ( ) 信誉:100 Blog 2006-09-28 09:31:00 得分: 0

2.
select
sum(case when type='1' then NUMBER_OF_PERSONS else 0 end) as a,
sum(case when method in('2','3') then USE_HOURS else 0 end) as b
from T_ES_TEACHEXPERIMENT
冷箫轻笛 2006-09-28
  • 打赏
  • 举报
回复
晕,楼上出手这么快!
yesjoy 2006-09-28
  • 打赏
  • 举报
回复
楼上没怎么看明白你给的参考信息噢
是不是没有写完啊
冷箫轻笛 2006-09-28
  • 打赏
  • 举报
回复
1.
select a,b from
(select sum(NUMBER_OF_PERSONS) as a from T_ES_TEACHEXPERIMENT where type='1')t1,
(select sum(USE_HOURS) as b from T_ES_TEACHEXPERIMENT where method in('2','3'))t2

2.
select sum(case type when 1 then NUMBER_OF_PERSONS else 0 end) as a,
sum(case method when '2' then USE_HOURS when '3' then USE_HOURS else 0 end) as b
from T_ES_TEACHEXPERIMENT
九斤半 2006-09-28
  • 打赏
  • 举报
回复
参考:

select max(a) as a,max(b) as b
from
(
select a=30,b=0
union all
select a=0,b=130
)i

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧