ALTER PROCEDURE App_Unit_Price
@time int
--@qu nchar(4)
AS
create table #table1
(
区域 nchar(4),
成交数 int
)--declare @temp table(区域 nvarchar(15) ,成交数量 int)insert into #table1 select top 5 h.Qu as '数据内容' ,count(d.HouseID) as '成交數量'from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time
Group by h.Qu order by Count(d.HouseID) desc --上季成交量
declare @shjiliang float
select @shjiliang = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses ) and
convert(nvarchar(2) , d.CheckTime , 101) = @time - 1 and h.Qu in (select 区域 from #table1)
--总成交量
declare @total float
select @total = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time--区域成交量
declare @quliang float
select @quliang = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses ) and
convert(nvarchar(2) , d.CheckTime , 101) = @time and h.Qu in (select 区域 from #table1)
declare @bili float
select @bili = @quliang / @total
declare @tongbi float
select @tongbi = (@quliang - @shjiliang) / @shjiliang * 100
select top 5 h.Qu as '数据内容', count(d.HouseID) as '成交数量' , @bili as '比例',@tongbi as '同上月增长'
from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time
Group by h.Qu order by Count(d.HouseID) desc数据内容 成交数量 比例 同上月增长
------ ----------- ------------------------- -------------------------
闵行区 27 0.9 170
长宁区 10 0.9 170
浦东新区 7 0.9 170
松江区 6 0.9 170
虹口区 4 0.9 170 我希望一个区算自己的,?但是老是出来只算一个的。
@time int
--@qu nchar(4)
AS
create table #table1
(
区域 nchar(4),
成交数 int
)--declare @temp table(区域 nvarchar(15) ,成交数量 int)insert into #table1 select top 5 h.Qu as '数据内容' ,count(d.HouseID) as '成交數量'from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time
Group by h.Qu order by Count(d.HouseID) desc --上季成交量
declare @shjiliang float
select @shjiliang = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses ) and
convert(nvarchar(2) , d.CheckTime , 101) = @time - 1 and h.Qu in (select 区域 from #table1)
--总成交量
declare @total float
select @total = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time--区域成交量
declare @quliang float
select @quliang = count(d.HouseID) from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses ) and
convert(nvarchar(2) , d.CheckTime , 101) = @time and h.Qu in (select 区域 from #table1)
declare @bili float
select @bili = @quliang / @total
declare @tongbi float
select @tongbi = (@quliang - @shjiliang) / @shjiliang * 100
select top 5 h.Qu as '数据内容', count(d.HouseID) as '成交数量' , @bili as '比例',@tongbi as '同上月增长'
from Houses h
inner join Deals d on h.HouseID = d.HouseID
where h.houseID in (select HouseID from Houses) and
convert(nvarchar(2) , d.CheckTime , 101) = @time
Group by h.Qu order by Count(d.HouseID) desc数据内容 成交数量 比例 同上月增长
------ ----------- ------------------------- -------------------------
闵行区 27 0.9 170
长宁区 10 0.9 170
浦东新区 7 0.9 170
松江区 6 0.9 170
虹口区 4 0.9 170 我希望一个区算自己的,?但是老是出来只算一个的。
解决方案 »
- gridview
- 调div宽度问题 我怎么调都不行看看代码
- Cookies的问题。这个东东保存在哪?
- adapter.Update的问题???
- 关于HtmlTextBoxControl
- 第二次问了,之前竟然没有人回答正确!关于DataGrid的页眉问题,100分!!!
- ASP.NET关于GRIDVIEW控件问题。。
- 在文本框的onkeypress事件中怎么判断出输入的是小于号
- 为什么部分按钮的事件处理程序不能中断???急用!!!!这是VS的bug吗???
- 我已经等了两天了,也花了近300两的银子,可是仍没有彻底解决,斑竹们,快救命呀!!!!!!!!!!
- 字符串替换
- 救命啊,大家进来看看,怎么会有这样的问题出现呢,怪了!谢谢
没明白.