[求助]如何实现这样的查询结果？ - 编程论坛

#2

purana2007-09-11 19:02

参考参考.呵呵.
if exists(select name from sysobjects
where name='procTest3' and type='p')
drop proc procTest3
go
create proc procTest3
As
declare @t_id int
declare @t_tm varchar(100)
declare @temp varchar(10)
--create table #t(id int,tm varchar(100))
declare cur1 cursor for (select [id] from table1 group by [id])
select @t_tm=''
open cur1
fetch next from cur1 into @t_id
while @@fetch_status=0
begin
declare cur2 cursor for (select tm from table1 where [id]=@t_id)
open cur2
fetch next from cur2 into @temp
while @@fetch_status=0
begin
select @t_tm=@t_tm+@temp+','
fetch next from cur2 into @temp
end
if right(@t_tm,1)=','
select @t_tm=left(@t_tm,len(@t_tm)-1)
print cast(@t_id as varchar(8))+' '+@t_tm
select @t_tm=''
close cur2
deallocate cur2
fetch next from cur1 into @t_id
end
close cur1
deallocate cur1

#3

西风独自凉2007-09-11 19:11

ls方法不錯。。

#4

purana2007-09-11 19:15

呵呵..
感觉写得很烂..

#5

西风独自凉2007-09-11 19:32

就是感覺還有更好的方法，但是又想不出來。。
這種方法比較死板一點

#6

purana2007-09-11 19:47

对的.
我刚学.
不知道能否可以一条select来做到.

#7

西风独自凉2007-09-11 19:48

正在想。。。。。。。。。不知道可以不？？。。。

#8

bygg2007-09-11 19:51

棉花糖在前面发过.

#9

西风独自凉2007-09-11 19:53

棉花糖那頭牛。怎麼這麼久沒看到他上。。。。

#10

purana2007-09-11 19:55

给个url

#11

西风独自凉2007-09-11 20:42

還有一種方法，但要在表中加入一個字段才可以實現。。。。
表t_test
id name orderid
1001 aa 1
1001 bb 2
1001 cc 3
1005 aa 1
1005 bb 2
1005 cc 3
1005 dd 4

SELECT k.id, isnull(A.name,'')+','+isnull(B.name,'')+','+isnull(C.name,'') as name
FROM
(SELECT DISTINCT id FROM t_test ) AS k
left JOIN
(Select id, name From t_test Where Orderid = 1 ) AS A
ON k.id = A.id
LEFT JOIN
(Select id, name From t_test Where Orderid = 2 ) AS B
ON k.id = B.id
LEFT JOIN
(Select id, name From t_test Where Orderid =3 ) AS C
ON k.id = C.id
....................
LEFT JOIN
(Select id, name From t_test Where Orderid =n ) AS n
ON k.id = n.id

[此贴子已经被作者于2007-9-11 20:49:29编辑过]

#12

purana2007-09-11 20:52

刚学,看不太懂.
如果这样做
那如果id号为1001的有5000.有50000.有5000000000
那不写死?

[此贴子已经被作者于2007-9-11 20:53:16编辑过]

#13

xinzheng2007-09-11 23:00

你们刚学就这么厉害啊，我都管SQL4年了，连你们写的什么意思都看不懂，诶

#14

西风独自凉2007-09-12 08:14

回复：（purana）刚学,看不太懂.如果这样做那如果id号...

是啊。。就是這裡不太好，還需進一步修改

[此贴子已经被作者于2007-9-12 8:52:43编辑过]

#15

purana2007-09-12 09:48

棉花糖.
kendy123456
这两个高手都不来了.

#16

bygg2007-09-12 10:30

https://bbs.bc-cn.net/viewthread.php?tid=120669&star=at#
看看这个吧

#17

purana2007-09-12 10:56

看不懂.

#18

心中有剑2007-09-12 18:56

楼上谦虚了

#19

purana2007-09-12 19:04

没有.数据库确实初学.菜鸟一名.

#20

shuzai19852007-09-14 23:53

if object_id('fn_合并') is not null drop function fn_合并
go
create function fn_合并(
@id varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=''
select @r=@r+' '+ tm from tablename where id=@id
return @r
return @r=stuff(@r,1,1,'')

end
go
--调用
select id,dbo.fn_合并(id) as tm from tablename
group by id
drop function fn_合并
drop table tablename
这样吧...................