求一简单的查询语句！

[yuweiwei]

表tb
ID A B
1 2009 AA
2 2009 BB
3 2008 CC
4 2007 XX
5 2009 HH
6 2008 DD
7 2007 SS
8 2006 GG

我想要这种结果：

2009 AA、BB、HH
2008 CC、DD
2007 XX、SS
2006 GG

[xuejie09242]

字段连接，看以前的帽子吧，有很多呢。也不怕把专家累着，哈哈。
发贴前先看看有没有相同或类似的帽子。

[guguda2008]

我想写但我知道我不如他们快，所以我就不写了

[昵称被占用了]

合并，论坛很多，自己搜搜

[水族杰纶]

精華帖~~

[guguda2008]

求一简单的查询语句
这个名字应该是论坛出现最多的帖名了吧

[soft_wsx]

CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO

[SQL77]

字符合并函数

[SQL77]

create table mail(id int  identity,email varchar(20),mjstart int,mjstop int,keyword char(10))
insert into mail select 
'[e-mail]',20,50 ,'上海'union all select
'[e-mail]',50,100,'北京'


create table house (id  int identity,htype char(10),mj int,money int,title char(10))
insert into house select 
'出租',30,500,'上海' union all select 
'出租',70,500,'北京' union all select 
'出租',70,500,'北京' union all select 
'出租',30,500,'上海' union all select 
'出租',70,500,'上海'


select px=(case when title='北京' then 1 else 2 end),h.*,email into ntabl from mail join house h on mail.keyword=h.title 

CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = '<'
    SELECT @r = @r + ''+rtrim(title)+''
    FROM ntabl
    WHERE px=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO

select (select top 1 email from ntabl n where n.px=px) as 邮箱, value = dbo.f_str(px) from ntabl group by px

/*
邮箱                   value                                                                                                                                                                                                                                                            
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
[e-mail]              北京北京
[e-mail]              上海上海上海

（所影响的行数为 2 行）

*/

帖代码了,累

[soft_wsx]

select a,dbo.f_str(a) from tb group by a
、这样就可以了

[soft_wsx]

把函数中相应的字段改了就可以了，相当简单

[lihan6415151528]

create table tb (a varchar(10),b varchar(10))
insert tb
 select '2009', 'AA' union all 
 select '2009', 'BB' union all 
 select '2008', 'CC' union all 
 select '2007', 'XX' union all 
 select '2009', 'HH' union all 
 select '2008', 'DD' union all 
 select '2007', 'SS' union all 
 select '2006', 'GG' 
go
select * from tb


CREATE FUNCTION dbo.f_str(@col1 varchar(10)) 
RETURNS varchar(100) 
AS 
BEGIN 
    DECLARE @re varchar(100) 
    SET @re='' 
    SELECT @re=@re+','+CAST(b as varchar) 
    FROM tb 
    WHERE a=@col1 
    RETURN(STUFF(@re,1,1,'')) 
END 
GO

select a,dbo.f_str(a)[b] from tb group by a 

a          b                                   
---------- -------------------
2006       GG
2007       XX,SS
2008       CC,DD
2009       AA,BB,HH

（所影响的行数为 4 行）

[gw6328]

学习

[程序小海]

学习中！