表table1:
┍--------------------------┐
| ID | name |
|--------------------------|
| 1 | A |
| 2 | B |
| 3 | AE |
| 4 | CD |
| 5 | BW |
| 6 | CT |
| 7 | EN |
| 8 | AY |
| . | . |
| . | . |
| . | . |
└--------------------------┚
查询结果是:
┍--------------------------┐
| ID | name |
|--------------------------|
| 1 | A,AE,AY··· |
| 2 | B,BW··· |
| 3 | CD,CT··· |
| 4 | EN··· |
| . | . |
| . | . |
| . | . |
└--------------------------┚
用一条SQL语句查询出来;
后来他提示用几个函数就行了。
求解中……
不想猜.
这个问题第1个回答:
这个问题第2个回答:
这个问题第3个回答:
-
SQL code
--------------------------------- -- Author: liangCK 小梁 -- Date : 2008-11-17 19:43:45 --------------------------------- --> 生成测试数据: @T DECLARE @T TABLE (ID INT,name VARCHAR(2)) INSERT INTO @T SELECT 1,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'AE' UNION ALL SELECT 4,'CD' UNION ALL SELECT 5,'BW' UNION ALL SELECT 6,'CT' UNION ALL SELECT 7,'EN' UNION ALL SELECT 8,'AY' --SQL查询如下: SELECT ROW_NUMBER() OVER(ORDER BY A.name) AS Rid, REPLACE(B.name,' ',',') AS name FROM ( SELECT LEFT(name,1) AS name FROM @t GROUP BY LEFT(name,1) ) AS A CROSS APPLY( SELECT name=( SELECT name AS [data()] FROM @T WHERE LEFT(name,1)=A.name FOR XML PATH('') ) ) AS B /* Rid name -------------------- --------------------------- 1 A,AE,AY 2 B,BW 3 CD,CT 4 EN (4 行受影响) */
这个问题第4个回答:
-
SQL code
go if object_id('F_Str') is not null drop function F_Str go create function F_Str(@name nvarchar(100)) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+',','')+name from Tb where name like left(@name,1)+'%' return @S end go select distinct dbo.f_str(name) from tb A,AE,AY B,BW CD,CT EN
这个问题第5个回答:
declare @table table (name varchar(50),express varchar(50) --定义表变量
insert into @table select substring(name,1,1),' 'from table1 group by substring(name,1,1)
declare @n int
set @n=1
while @n <=(select count(*) from table1) --用循环查找
begin
update @table set express=express+','+(select name from table1 where id=@n)
where name =(select substring(name,1,1) from table1 where id=@n)
set @n=@n+1
end
select * from @table
------------------------------
a ,a,ae,ay
b ,b,bw
c ,cd,ct
e ,en
这个问题第6个回答:
用到一个table1表 和你的table1表数据一致