这样一个问题，题目说不清楚

Question

这样的问题，比如有个表叫房屋表
id    房号    门洞号        门牌号
1     1         1           101
2     1         1           102
3     1         2           101
4     1         2           102
5     2         1           101
6     2         1           102
7     2         2           101
8     2         2           102 要查出这样的结果
门洞号    房号    套数
1,2       1       4
1,2       2       4请问怎末写查询语句呢   

Answer 1

合并列值
原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳表结构，数据如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc(2 行受影响)
*/

Answer 2

create function f_str(@fh int)
returns varchar(100)
as
begin
   declare @s varchar(100)
   select @s = isnull(@s+',','')+ltrim(门洞号)
   from ta
   where 房号 = @fh
   return @s
end
go
select 门洞号 = dbo.f_str(房号),房号,count(1)=套数
from ta
group by 房号

Answer 3

create table tb(id int, 房号 int, 门洞号 int, 门牌号 int)
insert into tb values(1 , 1 , 1 , 101 )
insert into tb values(2 , 1 , 1 , 102 )
insert into tb values(3 , 1 , 2 , 101 )
insert into tb values(4 , 1 , 2 , 102 )
insert into tb values(5 , 2 , 1 , 101 )
insert into tb values(6 , 2 , 1 , 102 )
insert into tb values(7 , 2 , 2 , 101 )
insert into tb values(8 , 2 , 2 , 102 )
go--创建一个合并的函数
create function f_hb(@房号 int)
returns varchar(8000)
as
begin
 declare @str varchar(8000)
 set @str = ''
 select @str = @str + ',' + cast(门洞号 as varchar) from (select distinct 房号,门洞号 from tb) t where 房号 = @房号
 set @str = right(@str , len(@str) - 1)
 return(@str)
End
go--调用自定义函数得到结果：
select n.门洞号, m.* from 
(select 房号 , count(*) 套数 from tb group by 房号) m,
(select distinct 房号 ,dbo.f_hb(房号) as 门洞号 from (select distinct 房号,门洞号 from tb) t) n
where m.房号 = n.房号drop table tb
drop function dbo.f_hb/*
门洞号  房号  套数 
------ ----  -----
1,2    1     4
1,2    2     4（所影响的行数为 2 行）
*/

Answer 4

--建立测试环境
set nocount on
create table test(id varchar(20),房号 varchar(20),门洞号 varchar(20),门牌号 varchar(20))
insert into test select '1','1','1','101'
insert into test select '2','1','1','102'
insert into test select '3','1','2','101'
insert into test select '4','1','2','102'
insert into test select '5','2','1','101'
insert into test select '6','2','1','102'
insert into test select '7','2','2','101'
insert into test select '8','2','2','102'
go
create function f_get(@房号 varchar(10))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret=''
select @ret=@ret+门洞号+',' from (select distinct 房号,门洞号 from test)a  where 房号=@房号
set @ret=left(@ret,len(@ret)-1)
return @ret
end
go
--测试
select 房号,dbo.f_get(房号),count(*) from test
group by 房号
--删除测试环境
drop table test
drop function f_get
 set nocount off

Answer 5

create table tb(id int, 房号 int, 门洞号 int, 门牌号 int) 
insert tb select 1, 1       ,  1  ,         101 
union all select 2,     1      ,   1,           102 
union all select 3 ,    1     ,    2 ,          101 
union all select 4  ,   1    ,     2  ,         102 
union all select 5   ,  2   ,      1   ,        101 
union all select 6    , 2  ,       1    ,       102 
union all select 7     ,2 ,        2     ,      101 
union all select 8     ,2,         2      ,     102  select 门洞号=convert(varchar(32), stuff((select distinct ','+rtrim(门洞号) from tb t where 房号=tb.房号 for xml path('')),1,1,''))
,房号, 套数=count(id)
from tb
group by 房号/*
门洞号                              房号          套数
-------------------------------- ----------- -----------
1,2                              1           4
1,2                              2           4(2 row(s) affected)
*/
drop table tb

Answer 6

create table tb(id int, 房号 int, 门洞号 int, 门牌号 int)
insert into tb values(1 , 1 , 1 , 101 )
insert into tb values(2 , 1 , 1 , 102 )
insert into tb values(3 , 1 , 2 , 101 )
insert into tb values(4 , 1 , 2 , 102 )
insert into tb values(5 , 2 , 1 , 101 )
insert into tb values(6 , 2 , 1 , 102 )
insert into tb values(7 , 2 , 2 , 101 )
insert into tb values(8 , 2 , 2 , 102 )
go--创建一个合并的函数
create function f_hb(@房号 int)
returns varchar(8000)
as
begin
 declare @str varchar(8000)
 set @str = ''
 select @str = @str + ',' + cast(门洞号 as varchar) from (select distinct 房号,门洞号 from tb) t where 房号 = @房号
 set @str = right(@str , len(@str) - 1)
 return(@str)
End
go--调用自定义函数得到结果：
select distinct 房号 ,dbo.f_hb(房号) as 门洞号 , count(*) from tb group by 房号drop table tb
drop function dbo.f_hb/*
房号 门洞号   套数 
---- ------- -----
1    1,2     4
2    1,2     4（所影响的行数为 2 行）
*/

Answer 7

如果楼主用的是SQL２００５，可以用７楼的代码，不需要自定义函数。

Answer 8

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,房号 int,门洞号 int,门牌号 int)
insert into #T
select 1,1,1,101 union all
select 2,1,1,102 union all
select 3,1,2,101 union all
select 4,1,2,102 union all
select 5,2,1,101 union all
select 6,2,1,102 union all
select 7,2,2,101 union all
select 8,2,2,102select
门洞号=(stuff((select distinct ','+ltrim(门洞号) from #T where 房号=a.房号 for xml path('')),1,1,'')),
房号,
套数=count(1)
from #T a group by 房号/*
门洞号    房号    套数
1,2       1       4
1,2       2       4
*/

Answer 9

create table tb(id int, 房号 int, 门洞号 int, 门牌号 int)
insert into tb values(1 , 1 , 1 , 101 )
insert into tb values(2 , 1 , 1 , 102 )
insert into tb values(3 , 1 , 2 , 101 )
insert into tb values(4 , 1 , 2 , 102 )
insert into tb values(5 , 2 , 1 , 101 )
insert into tb values(6 , 2 , 1 , 102 )
insert into tb values(7 , 2 , 2 , 101 )
insert into tb values(8 , 2 , 2 , 102 )
go--创建一个合并的函数
create function wsp(@房号 int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
select @sql = isnull(@sql+',','') + ltrim(门洞号) from (select distinct 房号,门洞号 from tb where 房号 = @房号)a 
return @sql 
End
goselect distinct 门洞号=dbo.wsp(房号),房号,套数=(select count(1) from tb where 房号=a.房号)
from tb a