34,576
社区成员
发帖
与我相关
我的任务
分享这个SQL怎么写?
表room
id roomstru stastus
1 户型1 未售
2 户型2 已售
3 户型3 已售
4 户型1 已售
5 户型5 未售
6 户型3 未售
7 户型1 未售
8 户型1 已售
9 户型1 未售
10 户型4 已售
11 户型5 未售
12 户型4 已售
请问怎样统计出每种户型未售、已售状态的个数？谢谢！
户型 户型1 户型2 户型3 …
总户数
已售
未售
销售比例
id roomstru stastus
1 户型1 未售
2 户型2 已售
3 户型3 已售
4 户型1 已售
5 户型5 未售
6 户型3 未售
7 户型1 未售
8 户型1 已售
9 户型1 未售
10 户型4 已售
11 户型5 未售
12 户型4 已售
请问怎样统计出每种户型未售、已售状态的个数？谢谢！
户型 户型1 户型2 户型3 …
总户数
已售
未售
销售比例
...全文
请发表友善的回复…
发表回复
jianlicd79 2007-11-09
- 打赏
- 举报
aa
hhwy_007 2007-11-08
- 打赏
- 举报
create table room(id int,roomstru varchar(10),stastus varchar(10))
insert into room values(1 , '户型1', '未售')
insert into room values(2 , '户型2', '已售')
insert into room values(3 , '户型3', '已售')
insert into room values(4 , '户型1', '已售')
insert into room values(5 , '户型5', '未售')
insert into room values(6 , '户型3', '未售')
insert into room values(7 , '户型1', '未售')
insert into room values(8 , '户型1', '已售')
insert into room values(9 , '户型1', '未售')
insert into room values(10, '户型4', '已售')
insert into room values(11, '户型5', '未售')
insert into room values(12, '户型4', '已售')
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
set @sql1=''
set @sql2=''
set @sql3=''
select @sql1=@sql1+',SUM(case when roomstru='''+roomstru+''' then 1 ELSE 0 end ) as ['+roomstru+']'
from room group by roomstru
select @sql2=@sql2+',SUM(case when stastus=''已售'' AND roomstru='''+roomstru+''' then 1 ELSE 0 end )*100/SUM(case when roomstru='''+roomstru+''' then 1 ELSE 0 end ) as ['+roomstru+']'
from room group by roomstru
order by roomstru
SET @sql3=N'select isnull(stastus,''总数'')'+@sql1+' from room group by stastus with cube'+
' union all select ''销售比例'''+@sql2+' from room '
--print @sql1
print @sql3
exec(@sql3)
insert into room values(1 , '户型1', '未售')
insert into room values(2 , '户型2', '已售')
insert into room values(3 , '户型3', '已售')
insert into room values(4 , '户型1', '已售')
insert into room values(5 , '户型5', '未售')
insert into room values(6 , '户型3', '未售')
insert into room values(7 , '户型1', '未售')
insert into room values(8 , '户型1', '已售')
insert into room values(9 , '户型1', '未售')
insert into room values(10, '户型4', '已售')
insert into room values(11, '户型5', '未售')
insert into room values(12, '户型4', '已售')
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
set @sql1=''
set @sql2=''
set @sql3=''
select @sql1=@sql1+',SUM(case when roomstru='''+roomstru+''' then 1 ELSE 0 end ) as ['+roomstru+']'
from room group by roomstru
select @sql2=@sql2+',SUM(case when stastus=''已售'' AND roomstru='''+roomstru+''' then 1 ELSE 0 end )*100/SUM(case when roomstru='''+roomstru+''' then 1 ELSE 0 end ) as ['+roomstru+']'
from room group by roomstru
order by roomstru
SET @sql3=N'select isnull(stastus,''总数'')'+@sql1+' from room group by stastus with cube'+
' union all select ''销售比例'''+@sql2+' from room '
--print @sql1
print @sql3
exec(@sql3)
jianlicd79 2007-11-08
- 打赏
- 举报
用动态的话报错
“不能使用空白的对象或列名。如果必要，请使用一个空格。”
“不能使用空白的对象或列名。如果必要，请使用一个空格。”
dawugui 2007-11-08
- 打赏
- 举报
create table tb(id int,roomstru varchar(10),stastus varchar(10))
insert into tb values(1 , '户型1', '未售')
insert into tb values(2 , '户型2', '已售')
insert into tb values(3 , '户型3', '已售')
insert into tb values(4 , '户型1', '已售')
insert into tb values(5 , '户型5', '未售')
insert into tb values(6 , '户型3', '未售')
insert into tb values(7 , '户型1', '未售')
insert into tb values(8 , '户型1', '已售')
insert into tb values(9 , '户型1', '未售')
insert into tb values(10, '户型4', '已售')
insert into tb values(11, '户型5', '未售')
insert into tb values(12, '户型4', '已售')
go
--静态SQL
select isnull(stastus,'总户数') stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus with rollup
union all
select stastus = '销售比例',cast(t1.户型1/t2.户型1 as decimal(18,2)) 户型1,cast(t1.户型2/t2.户型2 as decimal(18,2)) 户型2,
cast(t1.户型3/t2.户型3 as decimal(18,2)) 户型3,cast(t1.户型4/t2.户型4 as decimal(18,2)) 户型4,
cast(t1.户型5/t2.户型5 as decimal(18,2)) 户型5,cast(t1.总数*1.0/t2.总数 as decimal(18,2)) 总数
from
(
select
sum(case roomstru when '户型1' then 1.0 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1.0 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1.0 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1.0 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1.0 else 0 end) 户型5,
总数 = count(*)
from tb where stastus = '已售'
) t1,
(
select
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
) t2
/*
--动态SQL
declare @sql varchar(8000)
set @sql = 'select isnull(stastus,''总户数'') stastus'
select @sql = @sql + ' , sum(case roomstru when ''' + roomstru + ''' then 1 else 0 end) [' + roomstru + ']'
from (select distinct roomstru from tb) as a
set @sql = @sql + ',总数 = count(*) from tb group by stastus with rollup'
exec(@sql)
*/
drop table tb
/*
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
未售 3.00 .00 1.00 .00 2.00 6.00
已售 2.00 1.00 1.00 2.00 .00 6.00
总户数 5.00 1.00 2.00 2.00 2.00 12.00
销售比例 .40 1.00 .50 1.00 .00 .50
（所影响的行数为 4 行）
*/dawugui 2007-11-08
- 打赏
- 举报
create table tb(id int,roomstru varchar(10),stastus varchar(10))
insert into tb values(1 , '户型1', '未售')
insert into tb values(2 , '户型2', '已售')
insert into tb values(3 , '户型3', '已售')
insert into tb values(4 , '户型1', '已售')
insert into tb values(5 , '户型5', '未售')
insert into tb values(6 , '户型3', '未售')
insert into tb values(7 , '户型1', '未售')
insert into tb values(8 , '户型1', '已售')
insert into tb values(9 , '户型1', '未售')
insert into tb values(10, '户型4', '已售')
insert into tb values(11, '户型5', '未售')
insert into tb values(12, '户型4', '已售')
go
--静态SQL
select isnull(stastus,'总户数') stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus with rollup
union all
select stastus = '销售比例',t1.户型1/t2.户型1 户型1,t1.户型2/t2.户型2 户型2,
t1.户型3/t2.户型3 户型3,t1.户型4/t2.户型4 户型4,
t1.户型5/t2.户型5 户型5,t1.总数*1.0/t2.总数 总数
from
(
select
sum(case roomstru when '户型1' then 1.0 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1.0 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1.0 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1.0 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1.0 else 0 end) 户型5,
总数 = count(*)
from tb where stastus = '已售'
) t1,
(
select
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
) t2
/*
--动态SQL
declare @sql varchar(8000)
set @sql = 'select isnull(stastus,''总户数'') stastus'
select @sql = @sql + ' , sum(case roomstru when ''' + roomstru + ''' then 1 else 0 end) [' + roomstru + ']'
from (select distinct roomstru from tb) as a
set @sql = @sql + ',总数 = count(*) from tb group by stastus with rollup'
exec(@sql)
*/
drop table tb
/*
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------
未售 3.000000 .000000 1.000000 .000000 2.000000 6.000000000000
已售 2.000000 1.000000 1.000000 2.000000 .000000 6.000000000000
总户数 5.000000 1.000000 2.000000 2.000000 2.000000 12.000000000000
销售比例 .400000 1.000000 .500000 1.000000 .000000 .500000000000
（所影响的行数为 4 行）
*/
samfeng_2003 2007-11-08
- 打赏
- 举报
CREATE TABLE T
(
id INT,
roomstru VARCHAR(20),
stastus VARCHAR(20)
)
INSERT INTO T
SELECT 1,'户型1','未售' UNION ALL
SELECT 2,'户型2','已售' UNION ALL
SELECT 3,'户型3','已售' UNION ALL
SELECT 4,'户型1','已售' UNION ALL
SELECT 5,'户型5','未售' UNION ALL
SELECT 6,'户型3','未售' UNION ALL
SELECT 7,'户型1','未售' UNION ALL
SELECT 8,'户型1','已售' UNION ALL
SELECT 9,'户型1','未售' UNION ALL
SELECT 10,'户型4','已售' UNION ALL
SELECT 11,'户型5','未售' UNION ALL
SELECT 12,'户型4','已售'
DECLARE @sql VARCHAR(8000),@sql1 VARCHAR(8000)
SET @sql = 'SELECT stastus = CASE WHEN GROUPING(stastus) = 1 THEN ''总套数'' ELSE stastus END'
SELECT @sql = @sql + ',['+roomstru+']=RTRIM(SUM(CASE WHEN roomstru = '''+roomstru+''' THEN 1 ELSE 0 END))'
FROM T GROUP BY roomstru
SELECT @sql = @sql+' FROM T GROUP BY stastus WITH ROLLUP UNION ALL ', @sql1 ='SELECT ''销售比例'''
SELECT @sql1 = @sql1 + ',['+roomstru+']=RTRIM(SUM(CASE WHEN roomstru = '''+roomstru+''' AND stastus =''未售'' THEN 1 ELSE 0 END))+'':''
+LTRIM(SUM(CASE WHEN roomstru = '''+roomstru+''' AND stastus =''已售'' THEN 1 ELSE 0 END))'
FROM T GROUP BY roomstru
exec(@sql+@sql1 + ' FROM T')
DROP TABLE T
stastus 户型1 户型2 户型3 户型4 户型5
-------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
未售 3 0 1 0 2
已售 2 1 1 2 0
总套数 5 1 2 2 2
销售比例 3:2 0:1 1:1 0:2 2:0
jianlicd79 2007-11-08
- 打赏
- 举报
我的意思是说当户型有100种的时睺该怎么办，不可能写100个CASE吧，户型情况应该直接从表里读出来
窝抓了个羊 2007-11-08
- 打赏
- 举报
当户型不确定的时候,用无枪的狙击手的方法则可。
窝抓了个羊 2007-11-08
- 打赏
- 举报
select stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus
union
select '销售比例',
sum(case roomstru when '户型1' then 1 else 0 end)/count(*) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end)/count(*) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end)/count(*) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end)/count(*) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end)/count(*) 户型5,
1
from tb
group by stastus
jianlicd79 2007-11-08
- 打赏
- 举报
当户型不确定的时候喃？
fcuandy 2007-11-08
- 打赏
- 举报
搜'行转列'
-狙击手- 2007-11-08
- 打赏
- 举报
create table tb (id int,roomstru varchar(10), stastus varchar(10))
go
insert tb
select 1,'户型1','未售' union all
select 2,'户型2','已售' union all
select 3,'户型3','已售' union all
select 4,'户型1','已售' union all
select 5,'户型5','未售' union all
select 6,'户型3','未售' union all
select 7,'户型1','未售' union all
select 8,'户型1','已售' union all
select 9,'户型1','未售' union all
select 10,'户型4','已售' union all
select 11,'户型5','未售' union all
select 12,'户型4','已售'
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
as
declare @exec varchar(8000)
create table #tmp (col varchar(255))
set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''
select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then 1 else 0 end ) as ['+col+'],'
from #tmp
set @exec=left(@exec,len(@exec)-1)
set @exec='select CASE WHEN (GROUPING(stastus) = 1) THEN '''+'总计'+'''
ELSE ISNULL(stastus, '''+'UNKNOWN'+''')
END AS stastus,
'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+'] WITH CUBE'
exec(@exec)
go
exec make_fun 'tb','stastus','roomstru','id','sum'
/*
stastus 户型1 户型2 户型3 户型4 户型5
---------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2
已售 2 1 1 2 0
总计 5 1 2 2 2
*/
drop table tb
drop proc make_fundawugui 2007-11-08
- 打赏
- 举报
create table tb(id int,roomstru varchar(10),stastus varchar(10))
insert into tb values(1 , '户型1', '未售')
insert into tb values(2 , '户型2', '已售')
insert into tb values(3 , '户型3', '已售')
insert into tb values(4 , '户型1', '已售')
insert into tb values(5 , '户型5', '未售')
insert into tb values(6 , '户型3', '未售')
insert into tb values(7 , '户型1', '未售')
insert into tb values(8 , '户型1', '已售')
insert into tb values(9 , '户型1', '未售')
insert into tb values(10, '户型4', '已售')
insert into tb values(11, '户型5', '未售')
insert into tb values(12, '户型4', '已售')
go
--静态SQL
select isnull(stastus,'总户数') stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus with rollup
--动态SQL
declare @sql varchar(8000)
set @sql = 'select isnull(stastus,''总户数'') stastus'
select @sql = @sql + ' , sum(case roomstru when ''' + roomstru + ''' then 1 else 0 end) [' + roomstru + ']'
from (select distinct roomstru from tb) as a
set @sql = @sql + ',总数 = count(*) from tb group by stastus with rollup'
exec(@sql)
drop table tb
/*
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ----------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2 6
已售 2 1 1 2 0 6
总户数 5 1 2 2 2 12
（所影响的行数为 3 行）
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ----------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2 6
已售 2 1 1 2 0 6
总户数 5 1 2 2 2 12
*/dawugui 2007-11-08
- 打赏
- 举报
create table tb(id int,roomstru varchar(10),stastus varchar(10))
insert into tb values(1 , '户型1', '未售')
insert into tb values(2 , '户型2', '已售')
insert into tb values(3 , '户型3', '已售')
insert into tb values(4 , '户型1', '已售')
insert into tb values(5 , '户型5', '未售')
insert into tb values(6 , '户型3', '未售')
insert into tb values(7 , '户型1', '未售')
insert into tb values(8 , '户型1', '已售')
insert into tb values(9 , '户型1', '未售')
insert into tb values(10, '户型4', '已售')
insert into tb values(11, '户型5', '未售')
insert into tb values(12, '户型4', '已售')
go
--静态SQL
select stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus
--动态SQL
declare @sql varchar(8000)
set @sql = 'select stastus'
select @sql = @sql + ' , sum(case roomstru when ''' + roomstru + ''' then 1 else 0 end) [' + roomstru + ']'
from (select distinct roomstru from tb) as a
set @sql = @sql + ',总数 = count(*) from tb group by stastus'
exec(@sql)
drop table tb
/*
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ----------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2 6
已售 2 1 1 2 0 6
（所影响的行数为 2 行）
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ----------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2 6
已售 2 1 1 2 0 6
*/jianlicd79 2007-11-08
- 打赏
- 举报
户型1 户型2 户型3 …
总户数
已售
未售
销售比例
我要得出以上表的形式该怎么写？
总户数
已售
未售
销售比例
我要得出以上表的形式该怎么写？
dawugui 2007-11-08
- 打赏
- 举报
create table tb(id int,roomstru varchar(10),stastus varchar(10))
insert into tb values(1 , '户型1', '未售')
insert into tb values(2 , '户型2', '已售')
insert into tb values(3 , '户型3', '已售')
insert into tb values(4 , '户型1', '已售')
insert into tb values(5 , '户型5', '未售')
insert into tb values(6 , '户型3', '未售')
insert into tb values(7 , '户型1', '未售')
insert into tb values(8 , '户型1', '已售')
insert into tb values(9 , '户型1', '未售')
insert into tb values(10, '户型4', '已售')
insert into tb values(11, '户型5', '未售')
insert into tb values(12, '户型4', '已售')
go
select stastus,
sum(case roomstru when '户型1' then 1 else 0 end) 户型1,
sum(case roomstru when '户型2' then 1 else 0 end) 户型2,
sum(case roomstru when '户型3' then 1 else 0 end) 户型3,
sum(case roomstru when '户型4' then 1 else 0 end) 户型4,
sum(case roomstru when '户型5' then 1 else 0 end) 户型5,
总数 = count(*)
from tb
group by stastus
drop table tb
/*
stastus 户型1 户型2 户型3 户型4 户型5 总数
---------- ----------- ----------- ----------- ----------- ----------- -----------
未售 3 0 1 0 2 6
已售 2 1 1 2 0 6
（所影响的行数为 2 行）
*/
chuifengde 2007-11-08
- 打赏
- 举报
select
roomstru,
未出售=sum(case when stastus='未售' then 1 else 0 end),
已出售=sum(case when stastus='已售' then 1 else 0 end)
from room
group by roomstru
