[sql]
select cast(month(CheckTime) as nvarchar(4)) as [month] ,'1次'=sum(case when count(d.BuyID) = 1 then 1 else 0 end),
'2次'=sum(case when count(d.BuyID) = 2 then 1 else 0 end),
'3-5次'=sum(case when count(d.BuyID) between 3 and 5 then 1 else 0 end),
'5次以上'=sum(case when count(d.BuyID) > 5 then 1 else 0 end)from Deals d inner join Houses c on c.HouseID=d.HouseID
where c.Address like '%'+@term+'%' and d.CheckTime between ''+cast(@starttime as nvarchar(10))+'' and ''+cast(@endtime as nvarchar(10))+'' and c.FirstHandID = 2
group by month(CheckTime)
[/sql]
就是按月算出 出现 1次,2次,3-5次,5次以上 的数目。这样写好象不对,应该怎么修改。
谢谢
select cast(month(CheckTime) as nvarchar(4)) as [month] ,'1次'=sum(case when count(d.BuyID) = 1 then 1 else 0 end),
'2次'=sum(case when count(d.BuyID) = 2 then 1 else 0 end),
'3-5次'=sum(case when count(d.BuyID) between 3 and 5 then 1 else 0 end),
'5次以上'=sum(case when count(d.BuyID) > 5 then 1 else 0 end)from Deals d inner join Houses c on c.HouseID=d.HouseID
where c.Address like '%'+@term+'%' and d.CheckTime between ''+cast(@starttime as nvarchar(10))+'' and ''+cast(@endtime as nvarchar(10))+'' and c.FirstHandID = 2
group by month(CheckTime)
[/sql]
就是按月算出 出现 1次,2次,3-5次,5次以上 的数目。这样写好象不对,应该怎么修改。
谢谢
'1次'=sum(case when count(d.BuyID) = 1 then 1 else 0 end),
'2次'=sum(case when count(d.BuyID) = 2 then 1 else 0 end),
'3-5次'=sum(case when count(d.BuyID) between 3 and 5 then 1 else 0 end),
'5次以上'=sum(case when count(d.BuyID) > 5 then 1 else 0 end)
这里
'1次'=sum(case when count(d.BuyID) = 1 then 1 else 0 end),
'2次'=sum(case when count(d.BuyID) = 2 then 1 else 0 end),
'3-5次'=sum(case when count(d.BuyID) between 3 and 5 then 1 else 0 end),
'5次以上'=sum(case when count(d.BuyID) > 5 then 1 else 0 end) --这部分不能这样写,看看你的表结构
--不知道楼主想干吗。如果光从楼主的语句去改。
--try:'1次'=sum(case when (select count(1) from deals where 主键=d.主键) = 1 then 1 else 0 end), 其他类似这样改。试看看
-----------
HouseID
BuyID (出现次数)
CheckTime表 Houses
----------
HouseID
Address需要按月算出 BuyID 出现 1次,2次,3-5次,5次以上 的数目.
declare @term varchar(100),
@starttime datetime,
@endtime datetime,
@sql varchar(8000)select @starttime='',@endtime='' ---初始化变量set @sql='select cast(month(CheckTime) as nvarchar(4)) as [month], ''1次''=sum(case when count(d.BuyID) = 1 then 1 else 0 end),
''2次''=sum(case when count(d.BuyID) = 2 then 1 else 0 end),
''3-5次''=sum(case when count(d.BuyID) between 3 and 5 then 1 else 0 end),
''5次以上''=sum(case when count(d.BuyID) > 5 then 1 else 0 end) from Deals d inner join Houses c on c.HouseID=d.HouseID
where
c.Address like ''%'''+@term+'''%'' and
d.CheckTime between cast('+@starttime+' as nvarchar(10)) and cast('+@endtime+' as nvarchar(10))
and c.FirstHandID = 2
group by month(CheckTime)'exec (@sql)
表Deals
----------- -------------------
HouseID BuyID CheckTime
1 1 2007-2-1
2 2 2007-3-1
3 1 2007-2-2
4 3 2007-4-4表 House
-----------------------------
HouseID Address
1 AAA
2 BBB
3 A
4 AA
估计这样得结果 不考虑条件
--------------------------------------------------
[month] 1次 2次 3-5次 5次以上
2 0 1(1) 0 0
3 1(2) 0 0 0
4 1(3) 0 0 0
create table deals(houseid int,buyid int,checktime datetime)
insert into deals select 1,1,'2007-2-1'
insert into deals select 2,2,'2007-3-1'
insert into deals select 3,1,'2007-2-2'
insert into deals select 4,3,'2007-4-4'select month(a.checktime) [month],
'1次'=sum(case when cnt=1 then 1 else 0 end),
'2次'=sum(case when cnt=2 then 1 else 0 end),
'3-5次'=sum(case when cnt>2 and cnt<6 then 1 else 0 end),
'5次以上'=sum(case when cnt>5 then 1 else 0 end)
from deals a ,(select month(checktime) checktime,buyid,count(1)cnt from deals group by month(checktime),buyid)b
where a.buyid=b.buyid
group by month(a.checktime)