当前位置：首页 → 问答吧 → sql case when then 求助

sql case when then 求助

时间：2011-11-24

来源：互联网

SQL code

select count(*),sum(f_typearea)+sum(f_sharearea) from preroom where f_purpos in('车位','车库')  and f_prehousecode in(select f_prehousecode from prehouse where f_state='1')
select count(*),sum(f_typearea)+sum(f_sharearea) from preroom where f_purpos='住宅' and f_prehousecode in(select f_prehousecode from prehouse where f_state='1')
select count(*),sum(f_typearea)+sum(f_sharearea) from preroom where f_purpos='商业用房' and f_prehousecode in(select f_prehousecode from prehouse where f_state='1')
select count(*),sum(f_typearea)+sum(f_sharearea) from preroom where f_purpos not in('住宅','车库','车位','商业用房') and f_prehousecode in(select f_prehousecode from prehouse where f_state='1')

select count(*) from prehouse where f_state='1'

select sum(f_totalpay) from prehouse where f_state='1'

我想把上面这些select语句用case when then 来写，请大家帮忙看看！谢谢！！！

作者: yangjinju 发布时间: 2011-11-24

SQL code


select sum(case when f_purpos in('车位','车库') then 1 else 0 end) oneCount,
       sum(case when f_purpos ='住宅' then 1 else 0 end) twoCount,
       ...,
       sum(case when f_purpos in('车位','车库') then isnull(f_typearea,0)+isnull(f_sharearea,0) else 0 end) sumOne,
       ...
from preroom
where f_prehousecode in(select f_prehousecode from prehouse where f_state='1')


select count(*) cnt,sum(f_totalpay) sumNum from prehouse where f_state='1'

作者: AcHerat 发布时间: 2011-11-24

SQL code


select
 count(*),
 sum(case when f_purpos in('车位','车库') then f_typearea else 0 end)+
 sum(case when f_purpos in('车位','车库') then f_sharearea else 0 end) 
from 
  preroom 
where 
  f_prehousecode in(select f_prehousecode from prehouse where f_state='1')

作者: fredrickhu 发布时间: 2011-11-24

參照改

SQL code

SELECT 
    sum(CASE WHEN f_purpos in('车位','车库')  THEN 1 ELSE 0 END) AS con1,
    sum(CASE WHEN f_purpos in('车位','车库')  THEN f_typearea ELSE 0 END)+sum(CASE WHEN f_purpos in('车位','车库')  THEN f_sharearea ELSE 0 END) AS sum1
    ...............
    
from preroom w
where f_purpos not in('住宅','车库','车位','商业用房')
AND EXISTS(SELECT 1 FROM prehouse WHERE f_prehousecode=w.f_prehousecode)

作者: roy_88 发布时间: 2011-11-24

where 放在when
SQL code

SELECT 
    sum(CASE WHEN f_purpos in('车位','车库')  THEN 1 ELSE 0 END) AS con1,
    sum(CASE WHEN f_purpos in('车位','车库')  THEN f_typearea ELSE 0 END)+sum(CASE WHEN f_purpos in('车位','车库')  THEN f_sharearea ELSE 0 END) AS sum1
    ...............
    sum(CASE WHEN f_purpos not in('住宅','车库','车位','商业用房')   THEN 1 ELSE 0 END) AS conX,
    sum(CASE WHEN f_purpos not in('住宅','车库','车位','商业用房')  THEN f_typearea ELSE 0 END)+sum(CASE WHEN f_purpos not in('住宅','车库','车位','商业用房')  THEN f_sharearea ELSE 0 END) AS sumX

from preroom w
WHERE  EXISTS(SELECT 1 FROM prehouse WHERE f_prehousecode=w.f_prehousecode)

作者: roy_88 发布时间: 2011-11-24

SQL code


select sum(case when f_purpos in('车位','车库') then 1 else 0 end),
       sum(case when f_purpos in('车位','车库') then f_typearea else 0 end)+sum(case when f_purpos in('车位','车库') then f_sharearea else 0 end)
       sum(case when f_purpos in('住宅') then 1 else 0 end),
       sum(case when f_purpos in('住宅') then f_typearea else 0 end)+sum(case when f_purpos in('住宅') then f_sharearea else 0 end),
       ..
from preroom
where f_prehousecode in(select f_prehousecode from prehouse where f_state='1')
group by f_purpos

作者: pengxuan 发布时间: 2011-11-24

引用 2 楼 fredrickhu 的回复:
SQL code

select
count(*),
sum(case when f_purpos in('车位','车库') then f_typearea else 0 end)+
sum(case when f_purpos in('车位','车库') then f_sharearea else 0 end)
from
preroom
where
f_……

作者: szstephenzhou 发布时间: 2011-11-24

sql case when then 求助

热门阅读

热门下载