• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

请教一个sql语句

tristsesame 杭州快睿 开发组长/高级工程师/技术专家  2004-03-30 03:55:48
表结构如下:
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
...

下午头晕乎乎的,实在是想不出来如何实现.
向大家请教.
...全文
7 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
tristsesame 2004-03-30
to zjcxc & caiyunxia(monkey)

呵呵,多谢你们.

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

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

--测试数据
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 行)
--*/
回复
zjcxc 元老 2004-03-30
--直接生成结果的方法
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 元老 2004-03-30

--用临时表的处理方法
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
回复
caiyunxia 2004-03-30

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,
回复
caiyunxia 2004-03-30
用UPDAUE
回复
tristsesame 2004-03-30
有没有不用函数的方法呢.
而只有select查询语句来实现.

to caiyunxia(monkey)
谢谢.
但是我想尽量避免使用函数.
回复
caiyunxia 2004-03-30
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
回复
caiyunxia 2004-03-30
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(@)
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-03-30 03:55
社区公告
暂无公告