create table 房 (客户 varchar(22),房型 varchar(200))
insert into 房
select 'user1','1,2,' union all
select 'user2','0,1,3,' union all
select 'user3','' union all
select 'user4','1,2,3,4,'--查询
declare @s varchar(max) set @s=''
select @s=replace(@s+房型+',',',,',',') from 房
if len(@s)>0 set @s=left(@s,len(@s)-1)
select a as 房型,count(*)as 人数 from f_split(@s,',') group by a order by a/*
-------------------
0 1
1 3
2 2
3 2
4 1
*/--送你split函数
create function f_split(@str varchar(8000),@strseprate varchar(1))
returns @temp table(id int identity(1,1),a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@strseprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为stuff,可以自己试着改写一下
set @i=charindex(@strseprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end
insert into 房
select 'user1','1,2,' union all
select 'user2','0,1,3,' union all
select 'user3','' union all
select 'user4','1,2,3,4,'--查询
declare @s varchar(max) set @s=''
select @s=replace(@s+房型+',',',,',',') from 房
if len(@s)>0 set @s=left(@s,len(@s)-1)
select a as 房型,count(*)as 人数 from f_split(@s,',') group by a order by a/*
-------------------
0 1
1 3
2 2
3 2
4 1
*/--送你split函数
create function f_split(@str varchar(8000),@strseprate varchar(1))
returns @temp table(id int identity(1,1),a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@strseprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为stuff,可以自己试着改写一下
set @i=charindex(@strseprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end
楼主 我认识 PER 分我点分吧