这个SQL怎么写?

Question

表roomid  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 …

总户数
已售
未售
销售比例

Answer 1

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

Answer 2

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',       '已售')
goselect 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 stastusdrop table tb/*
stastus    户型1         户型2         户型3         户型4         户型5         总数          
---------- ----------- ----------- ----------- ----------- ----------- ----------- 
未售         3           0           1           0           2           6
已售         2           1           1           2           0           6（所影响的行数为 2 行）
*/

Answer 3

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
*/

Answer 4

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
*/

Answer 5

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_fun

Answer 6

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 stastusunion 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

Answer 7

我的意思是说当户型有100种的时睺该怎么办，不可能写100个CASE吧，户型情况应该直接从表里读出来

Answer 8

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

Answer 9

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 行）
*/

Answer 10

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 行）
*/

Answer 11

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)