ALTER PROCEDURE App_Floor_Analyse
@year nvarchar(8) = null,
@term nvarchar(64) = null,
@cishu nvarchar(4) = null
AS
create table #table_avg
(
[month] nvarchar(8),
[count] float,
avg_price int,
avg_totalprice int,
avg_area int
)
declare @sql nvarchar(1000)
set @sql = 'select Convert(nvarchar(2),d.CheckTime,101) as [month],count(*) as [count],avg(d.Price) as avg_price,
avg(d.TotalPrice) as avg_totalprice,avg(h.MianJi) as avg_area from Deals as d inner join Houses as h on d.HouseID = h.HouseID'
set @sql = @sql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
set @sql = @sql + ' and h.FirstHandID = '''+@cishu+''' '
if @cishu = '1'
begin
if @term <> ''
set @sql = @sql + ' and h.XiangMu like ''%'+@term+'%'' '
end
else
begin
if @term <> ''
set @sql = @sql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%'' )'
end
set @sql = @sql + ' group by Convert(nvarchar(2),d.CheckTime,101)'
insert into #table_avg exec sp_executesql @sql
create table #table_cishu
(
[month] nvarchar(8),
one int,
two int,
three int,
five int
)
declare @strsql nvarchar(1000)
set @strsql = 'SELECT [month],sum([count]) FROM (SELECT Convert(nvarchar(2),d.CheckTime,101) as [month] ,COUNT(D.BuyID) as [count]
FROM Deals as D INNER JOIN Houses as H ON D.HouseID = H.HouseID '
set @strsql = @strsql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
set @strsql = @strsql + ' and h.FirstHandID = '''+@cishu+''' '
if @cishu = '1'
begin
if @term <> ''
set @strsql = @strsql + ' and h.XiangMu like ''%'+@term+'%'' '
end
else
begin
if @term <> ''
set @strsql = @strsql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%'' )'
end
declare @sql1 nvarchar(1000)
set @sql1 = @strsql + ' group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) = 1) AS 临时表 group by [month]'
declare @sql2 nvarchar(1000)
set @sql2 = @strsql + ' group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) = 2) AS 临时表 group by [month]'
declare @sql3 nvarchar(1000)
set @sql3 = @strsql + ' group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having (count(D.BuyID) = 3 or count(D.BuyID) = 4 or count(D.BuyID) = 5)) AS 临时表 group by [month]'
declare @sql4 nvarchar(1000)
set @sql4 = @strsql + ' group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) > 5) AS 临时表 group by [month]'
create table #table1
(
[month] nvarchar(8),
[count] int
)
insert into #table1 exec sp_executesql @sql1 --1次
create table #table2
(
[month] nvarchar(8),
[count] int
)
insert into #table2 exec sp_executesql @sql2 --2次
create table #table3
(
[month] nvarchar(8),
[count] int
)
insert into #table3 exec sp_executesql @sql3 --3-5次
create table #table4
(
[month] nvarchar(8),
[count] int
)
insert into #table4 exec sp_executesql @sql4 --5次
insert into #table_cishu select t1.[month] ,isnull(t1.[count],0) as one,isnull(t2.[count],0) as two,isnull(t3.[count],0) as three,isnull(t4.[count],0) as five
from #table1 t1 left join #table2 t2 on t1.[month] = t2.[month]
left join #table3 t3 on t1.[month] = t3.[month] left join #table4 t4 on t1.[month] = t4.[month] create table #table_people
(
[month] nvarchar(8),
sh int,
wd int,
hw int,
gw int
)
declare @str_sql nvarchar(1000)
set @str_sql = 'select [month] , sum([count]) from ( select Convert(nvarchar(2),d.CheckTime,101) as [month], count(*) as [count]
from Deals as d inner join Houses as h on d.HouseID = h.HouseID inner join Peoples as p on p.PeopleID in (d.BuyID,d.SellID) inner join Country as c on p.CountryID = c.CountryID '
set @str_sql = @str_sql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
set @str_sql = @str_sql + ' and h.FirstHandID = '''+@cishu+''' '
if @cishu = '1'
begin
if @term <> ''
set @str_sql = @str_sql + ' and h.XiangMu like ''%'+@term+'%'' '
end
else
begin
if @term <> ''
set @str_sql = @str_sql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%'' )'
end
--上海
declare @sql_1 nvarchar(1000)
set @sql_1 = @str_sql + ' group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID = 2) as 临时表 group by [month]'
--外地
declare @sql_2 nvarchar(1000)
set @sql_2 = @str_sql + ' and p.CountryID =2 group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID <> 2) as 临时表 group by [month]'
--国外
declare @sql_3 nvarchar(1000)
set @sql_3 = @str_sql + ' group by p.CountryID,Convert(nvarchar(2),d.CheckTime,101) having p.CountryID = 5) as 临时表 group by [month]'
--海外华人
declare @sql_4 nvarchar(1000)
set @sql_4 = @str_sql + ' group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID = 6) as 临时表 group by [month]'
create table #table_1
(
[month] nvarchar(8),
[count] int
)
insert into #table_1 exec sp_executesql @sql_1
create table #table_2
(
[month] nvarchar(8),
[count] int
)
insert into #table_2 exec sp_executesql @sql_2
create table #table_3
(
[month] nvarchar(8),
[count] int
)
insert into #table_3 exec sp_executesql @sql_3
create table #table_4
(
[month] nvarchar(8),
[count] int
)
insert into #table_4 exec sp_executesql @sql_4
insert into #table_people select t_1.[month] ,isnull(t_1.[count],0) as one,isnull(t_2.[count],0) as two,isnull(t_3.[count],0) as three,isnull(t_4.[count],0) as five
from #table_1 t_1 left join #table_2 t_2 on t_1.[month] = t_2.[month]
left join #table_3 t_3 on t_1.[month] = t_3.[month] left join #table_4 t_4 on t_1.[month] = t_4.[month]
select t_a.[month] ,t_a.[count] ,cast(t_a.avg_price as nvarchar(8)) + '元' as avg_price ,cast((t_a.avg_totalprice/10000) as nvarchar(8)) + '万',
cast(t_a.avg_area as nvarchar(8)) + '㎡', cast((convert(numeric(8,0),round(t_c.one/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as one,
cast((convert(numeric(8,0),round(t_c.two/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as two,cast((convert(numeric(8,0),round(t_c.three/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as three,
cast((convert(numeric(8,0),round(t_c.five/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as five,cast((convert(numeric(8,0),round(t_p.sh/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as sh,
cast((convert(numeric(8,0),round(t_p.wd/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as wd,cast((convert(numeric(8,0),round(t_p.hw/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as hw,
cast((convert(numeric(8,0),round(t_p.gw/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as gw
from #table_avg as t_a left join #table_cishu as t_c on t_a.[month] = t_c.[month] left join #table_people as t_p on t_a.[month] = t_p.[month]
帮忙优化下，谢谢. 如果改成几个存储过程输出速度会提高吗？
month count avg_price Column1 Column2 one two three five sh wd hw gw
-------- ------------------------- ---------- ---------- ---------- --------- --------- --------- --------- --------- --------- --------- ---------
01 18 9185元 163万 561㎡ 56% 44% 0% 0% 72% 33% 28% 0%
02 5 11421元 229万 201㎡ 100% 0% 0% 0% 60% 20% 0% 0%
03 3 12247元 246万 201㎡ 100% 0% 0% 0% 100% 0% 0% 0%
(19 行受影响)
(返回 3 行)
@RETURN_VALUE = 0
完成 [dbo].[App_Floor_Analyse] 运行。
用分支语句，静态的select可以生成更好的执行计划，也就是效率会提高