请教一个sql语句

[tristsesame]

表结构如下:
id name1 name2
1 1 1
2 1 2
3 1 3
4 1 4
5 2 8
6 3 9
7 3 10
8 3 11
9 4 12
......

现在我想select这样一个结果集出来
name1 name2
1 1,2,3... (如果有重复的,只列出前三条,如果多于三条,则加上...显示)
2 8
3 9,10,11 (如果不多于三条,则全部列出)
4 12
...

下午头晕乎乎的,实在是想不出来如何实现.
向大家请教.

[caiyunxia]

CREATE FUNCTION dbo.f_string(@name1 varchar(10))
RETURNS varchar(2000)---返回值
as

declare @ varchar(2000)
set @=''
select @=@+name2+',' from table1 where name1=@name1
set @=left(@,len(@)-1)
RETURN(@)

[caiyunxia]

drop table table1
create table table1(id int, name1 varchar(10), name2 varchar(20))
insert table1 values(1, 1, 1)
insert table1 values(2, 1, 2)
insert table1 values(3, 1, 3)
insert table1 values(4, 1, 4)
insert table1 values(5, 2, 8)
insert table1 values(6, 3, 9)
insert table1 values(7, 3, 10)
insert table1 values(8, 3, 11)
insert table1 values(9, 4, 12)

CREATE FUNCTION dbo.f_string(@name1 varchar(10))
RETURNS varchar(2000)---返回值
as
begin
declare @ varchar(2000)
set @=''
select @=@+name2+',' from table1 where name1=@name1
set @=left(@,len(@)-1)
RETURN(@)
end

select name1, dbo.f_string(name1) from (select distinct name1 from table1) a
name1
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3,4
2 8
3 9,10,11
4 12

[tristsesame]

有没有不用函数的方法呢.
而只有select查询语句来实现.

to caiyunxia(monkey)
谢谢.
但是我想尽量避免使用函数.

[caiyunxia]

用UPDAUE

[caiyunxia]

create table table1(id int, name1 varchar(10), name2 varchar(20))
insert table1 values(1, 1, 1)
insert table1 values(2, 1, 2)
insert table1 values(3, 1, 3)
insert table1 values(4, 1, 4)
insert table1 values(5, 2, 8)
insert table1 values(6, 3, 9)
insert table1 values(7, 3, 10)
insert table1 values(8, 3, 11)
insert table1 values(9, 4, 12)


SELECT DISTINCT NAME1,NAME2,CAST('' AS VARCHAR(2000)) NEW_NAME INTO # FROM TABLE1
DECLARE @NAME varchar(10),@n varchar(2000)
set @n=''
update # set @n=case when name1<>@NAME then name2+',' else @n+name2+',' end,@NAME=name1,NEW_NAME=@n
select name1,max(NEW_NAME) from # group by name1
drop table table1,#
name1
1 1,2,3,4,
2 8,
3 10,11,9,
4 12,

[zjcxc]

--用临时表的处理方法
select name1,name2,sid=(
select sum(1) from 表 where name1=a.name1 and id<=a.id)
into #t from 表 a
where (select sum(1) from 表 where name1=a.name1 and id<=a.id)<=4

select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then ''
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then ''
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' end

from(
select name1,name2 from #t where sid=1
)a left join(
select name1,name2 from #t where sid=2
)b on a.name1=b.name1 left join(
select name1,name2 from #t where sid=3
)c on a.name1=c.name1 left join(
select name1,name2 from #t where sid=4
)d on a.name1=d.name1

[zjcxc]

--直接生成结果的方法
select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then ''
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then ''
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' end
from(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=1
)a left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=2
)b on a.name1=b.name1 left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=3
)c on a.name1=c.name1 left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=4
)d on a.name1=d.name1

[zjcxc]

--测试

--测试数据
create table 表(id int,name1 int,name2 int)
insert 表 select 1,1,1
union all select 2,1,2
union all select 3,1,3
union all select 4,1,4
union all select 5,2,8
union all select 6,3,9
union all select 7,3,10
union all select 8,3,11
union all select 9,4,12
go

--直接生成结果的方法
select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then ''
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then ''
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' end
from(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=1
)a left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=2
)b on a.name1=b.name1 left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=3
)c on a.name1=c.name1 left join(
select name1,name2 from 表 a
where (
select sum(1) from 表
where name1=a.name1 and id<=a.id
)=4
)d on a.name1=d.name1

go

--删除测试环境
drop table 表

/*--测试结果
name1 name2
----------- ---------------
1 1,2,3...
2 8
3 9,10,11
4 12

（所影响的行数为 4 行）
--*/

[tristsesame]

to zjcxc & caiyunxia(monkey)

呵呵,多谢你们.

今天头晕脑胀的,也不知是不是天气关系.
无心写代码,在网上瞎转了半天,
总算下班了:)

仔细考虑了一下,觉得这样大量的select的话,效率太低
还是打算用
select id,name1,name from table order by name1
这样来做,
然后自己在代码中判断name1是否相同.
这样感觉应该速度上应该快点.