case..then..oracle函數的研究.......

標籤：

SELECT a.areaname as 二級地名 FROM houses h,houses h2,
areas a WHERE h.areaid=a.areasid AND h2.areaid=a.areasid AND h.finalassessmentid=1 AND
h.houseslevel=2 AND h2.houseslevel=1 AND a.areasid!=a.parentareasid AND a.companysid=2;

select * from areas
select count(h.houseslevel) as 總數,count(h1.houseslevel) as 最新累計成盤,count(h2.houseslevel) as 最新累計必交盤 from houses h,houses h1,houses h2 where h.houseslevel in (1,2) and h1.houseslevel=1 and h2.houseslevel=2;

select count(h.houseslevel) as 總數 from houses h where h.houseslevel in (1,2)
union all
select count(h.houseslevel) as 成盤 from houses h where h.houseslevel=1
union all
select count(h.houseslevel) as 成盤 from houses h where h.houseslevel=2;

 

select
count(h.houseslevel) as 總數,
count(case when h.houseslevel=1 then h.housesid end ) 成盤 ,
count(case when h.houseslevel=2 then h.housesid end ) 比較盤
from houses h,areas a where h.areaid=a.areasid and h.houseslevel in (1,2)

SELECT a.areaname AS 二級地名,
count(a.houseId) AS 最新累計有效待售數,
count(h.houseslevel) AS 最新累計必賣盤,count(h2.houseslevel) AS 最新累計成盤 FROM houses h,houses h2,
areas a WHERE h.areaid=a.areasid AND h2.areaid=a.areasid AND h.finalassessmentid=1 AND h.houseslevel=2 AND h2.houseslevel=1 AND a.areasid!=a.parentareasid AND a.companysid=2;

SELECT a.areaname AS 二級地名 FROM houses h,areas a WHERE h.areaid=a.areasid AND a.areasid!=a.parentareasid

SELECT a.areaname AS 二級地名 FROM areas a where a.areaname=

select a.areaname AS 二級地名 from areas a
where a.areaname in (select b.areaname from areas b group by b.areaname having count(b.areaname) > 1)

select b.areaname as 二級地名,count(b.areaname) as 重複資料量 from areas b group by b.areaname having count(b.areaname) > 1

select h.houseslevel from houses h where h.houseslevel=1;

select a.areaname AS 二級地名 from houses h,areas a where h.areaid=h.areaid and h.houseslevel=1 and a.areasid!=a.parentareasid AND a.companysid=2 and h.finalassessmentid=1;

select a.areaname AS 二級地名,
count(h.houseslevel) as 總數,
count(case when h.houseslevel=1 then h.housesid end ) 成盤 ,
count(case when h.houseslevel=2 then h.housesid end ) 比較盤
from houses h,areas a where h.areaid=a.areasid and h.houseslevel in (1,2) and a.areasid!=a.parentareasid AND a.companysid=2 and h.finalassessmentid=1 and flag1=1 group by a.areaname;

 

case..then..oracle函數的研究.......