表名:Tg_HouseInfo
字段如下:
ID BuildID HouseCode HouseType SaleType GenelFloor
1 1 4 1 1 11
2 2 5 1 1 7
3 3 1 1 2 11
4 3 2 2 4 6
5 4 4 1 1 12
6 4 11 1 3 6
...............................................
其中:ID主建,BuildID楼盘ID,HouseCode楼房编号比如3幢,
HouseType房屋类型:1,住宅;2,商用房
SaleType销售状态:1,可售;2,待售;3,已售;4,预订
GenelFloor为楼房总层数
希望统计出如下形式(其中要求去除楼房编号重复项):
楼房编号 房屋类型 楼房总层数 可售数 待售数 已售数 预订数
字段如下:
ID BuildID HouseCode HouseType SaleType GenelFloor
1 1 4 1 1 11
2 2 5 1 1 7
3 3 1 1 2 11
4 3 2 2 4 6
5 4 4 1 1 12
6 4 11 1 3 6
...............................................
其中:ID主建,BuildID楼盘ID,HouseCode楼房编号比如3幢,
HouseType房屋类型:1,住宅;2,商用房
SaleType销售状态:1,可售;2,待售;3,已售;4,预订
GenelFloor为楼房总层数
希望统计出如下形式(其中要求去除楼房编号重复项):
楼房编号 房屋类型 楼房总层数 可售数 待售数 已售数 预订数
HouseCode,HouseType,GenelFloor,
KSale=SUM(Case When SaleState=1 Then 1 Else 0 End),
DSale=SUM(Case When SaleState=2 Then 1 Else 0 End),
YSale=SUM(Case When SaleState=3 Then 1 Else 0 End),
YDSale=SUM(Case When SaleState=4 Then 1 Else 0 End)
from Tg_HouseInfo
Group By HouseCode,HouseType,GenelFloor
这样统计的话会出现HouseCode楼盘编号有重复的
1 1 4 1 1 11
2 2 5 1 1 7
3 3 1 1 2 11
4 3 2 2 4 6
5 4 4 1 1 12
6 4 11 1 3 6
1 1 4 1 1 11
2 2 5 1 1 7
3 3 1 1 2 11
4 3 2 2 4 11
5 4 4 1 1 12
6 4 11 1 3 12
...............................................
字段如下:
ID BuildID HouseCode HouseType SaleType GenelFloor
1 1 4 1 1 11
2 2 5 1 1 7
3 3 1 1 2 11
4 3 2 2 4 11
5 4 4 1 1 12
6 4 11 1 3 12
...............................................
其中:ID主建,BuildID楼盘ID,HouseCode楼房编号比如3幢,
HouseType房屋类型:1,住宅;2,商用房
SaleType销售状态:1,可售;2,待售;3,已售;4,预订
GenelFloor为楼房总层数
希望统计出如下形式(其中要求去除楼房编号重复项):
楼房编号 房屋类型 楼房总层数 可售数 待售数 已售数 预订数
1 1 5 1 0 0 0
10 1 5 0 2 0 0
11 1 0 0 1 0 0
12 1 6 0 4 0 0
13 1 11 0 1 0 0
13 1 2 0 1 0 0
13 1 6 0 6 0 0
14 1 4 0 1 0 0
14 1 6 0 5 0 0
15 1 6 0 3 0 0
19 1 11 0 7 0 0
19 1 8 0 1 0 0
2 1 0 0 2 0 0
20 1 11 0 11 0 0
20 1 3 0 1 0 0
20 1 4 0 1 0 0
20 1 8 0 2 0 0
21 1 4 0 3 0 0
22 1 4 0 4 0 0
23 1 4 0 3 0 0
24 1 4 0 3 0 0
25 1 4 0 3 0 0
26 1 11 0 8 0 0
27 1 11 0 9 0 0
28 1 11 0 9 0 0
3 1 0 0 2 0 0
30 1 0 0 1 0 0
33 2 3 1 0 0 0
33 2 44 1 0 0 0
4 1 0 0 2 0 0
4 2 33 1 0 0 0
5 1 0 0 1 0 0
6 1 0 0 1 0 0
6 1 5 0 2 0 0
8 1 5 0 1 0 0
8 1 8 0 1 0 0
85 1 11 0 1 0 0
9 1 0 0 1 0 0
HouseCode,HouseType,GenelFloor,
KSale=SUM(Case When SaleState=1 Then 1 Else 0 End),
DSale=SUM(Case When SaleState=2 Then 1 Else 0 End),
YSale=SUM(Case When SaleState=3 Then 1 Else 0 End),
YDSale=SUM(Case When SaleState=4 Then 1 Else 0 End)
from Tg_HouseInfo
Group By HouseCode,HouseType,GenelFloor
上面是按照这样统计出来的,HouseCode有重复项,
其中:ID主建,BuildID楼盘ID,HouseCode楼房编号比如3幢,
HouseType房屋类型:1,住宅;2,商用房
SaleType销售状态:1,可售;2,待售;3,已售;4,预订
GenelFloor为楼房总层数
楼房编号 房屋类型 楼房总层数 可售数 待售数 已售数 预订数
select main.HouseCode,main.HouseType,main.GenelFloor,a.qui,b.qui,c.qui,d.qui
from (select HouseCode,HouseType,GenelFloor) main
left join (select HouseCode,count(*) as qui from Tg_HouseInfo where SaleType=1 group by HouseCode) a on main.HouseCode=a.HouseCode
left join (select HouseCode,count(*) as qui from Tg_HouseInfo where SaleType=2 group by HouseCode) b on main.HouseCode=b.HouseCode
left join (select HouseCode,count(*) as qui from Tg_HouseInfo where SaleType=3 group by HouseCode) c on main.HouseCode=c.HouseCode
left join (select HouseCode,count(*) as qui from Tg_HouseInfo where SaleType=4 group by HouseCode) d on main.HouseCode=d.HouseCode