SQL查询结果,如何不用循环movenext方法获得全部值
有一条sql查询语句,通过外部调用
select id from tablename
目前是通过循环的方式获得字段id的值有值
表结果如下:
id bigint 自动编号
Title nvarchar(30)
----------------------------
id Title
1 A
2 B
3 C
4 D
5 E
6 F
-----------------------------
目前是通过
select id from tablename
然后用
do while not rs.movenext
idList = idList & "," & rs("id")
rs.movenext
loop
获得所有的id
有没有更简单的方法,不需要通过循环,不需要通过rs.movenext,直接sql查询完后获得所有的id?
[解决办法]
select stuff ((select ','+id from tablename FOR XML PATH('')),1,1,'')
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-31 10:20:27
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tablename]
if object_id('[tablename]') is not null drop table [tablename]
go
create table [tablename]([id] int,[Title] varchar(1))
insert [tablename]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E' union all
select 6,'F'
--------------开始查询--------------------------
select DISTINCT
stuff((select ','+CONVERT(VARCHAR(max),id) from [tablename] b
--where b.col1=a.col1 and b.col2=a.col2
for xml path('')),1,1,'') [id]
from [tablename]
----------------结果----------------------------
/*
id
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6
*/