SQL查询结果，如何不用循环movenext方法获得全部值

Question

你用下面的SQL返回的就是所有的ID结果啊select id from tablename

Answer 1

我是在外部sql语句查询，查询得到的结果需要用rs.movenext循环方式获得，希望找到一种可以直接获取结果集的办法。

Answer 2

select stuff ((select ','+id from tablename  FOR XML PATH('')),1,1,'')

Answer 3

那就只能一次把数据，全部加载到数组里面存储，然后就直接操作数组，不用每次在movenext取下一条数据。这样可以节省每次与sql server交互的时间，加快处理速度

Answer 4

表结果如下：
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--------------------------------------我需要获得字段id所有的值，希望通过查询后返回结果如下：
,1,2,3,4,5,6

Answer 5

----------------------------------------------------------------
-- 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
*/

Answer 6

create table tbtest(a int ,b varchar(10))
insert into tbtest VALUES( 1,'A')
insert into tbtest VALUES( 2,'B')
insert into tbtest VALUES( 3,'C')
insert into tbtest VALUES( 4,'D')
insert into tbtest VALUES( 5,'E')SELECT ','+LTRIM(str(a)) from tbtest for xml path('')结果
,1,2,3,4,5

Answer 7

谢谢您！因为我需要处理的数据量非常大，通过这种方式速度非常慢，通过外部SQL查询速度会快很多，所以希望找一种可以通过外部调用SQL查询的方式。

Answer 8

谢谢您！因为我需要处理的数据量非常大，通过这种方式速度非常慢，通过外部SQL查询速度会快很多，所以希望找一种可以通过外部调用SQL查询的方式。

Answer 9

直接改sql才最快，集合操作比外部调用更快

Answer 10

你测过很慢吗？对外部调用产生的问题，sqlserver很难控制得到，所以对集合操作，还是放在sql端比较好

Answer 11

谢谢您！因为我需要处理的数据量非常大，通过这种方式速度非常慢，通过外部SQL查询速度会快很多，所以希望找一种可以通过外部调用SQL查询的方式。
如果資料弄到外部，那麼不是增加IO嗎？

Answer 12

谢谢您！这种方式可以通过外部调用然后返回给指定变量吗？另外Stuff好像对长度有限制，刚才通过查询编辑器测试了一下这种方法，效果是很好的，但长度有限制，最长是43680，由于需要处理的数据非常庞大，实际需要的长度不止这个。

Answer 13

是的，就是因为太慢了，所以才改用外部方式，之前发过的贴子：
http://bbs.csdn.net/topics/390680857
http://bbs.csdn.net/topics/390680794

Answer 14

SSMS只是显示有限制，实际数据存储没限制

Answer 15

我当时处理36万行，不超过30秒.....外部调用也不见得好啊，CLR你会不？

Answer 16

关于用内部存储操作慢的说明：
1、实际上用到2个表，表1的记录在6万条左右，以后还会增加；表2的记录在200万条左右，以后还会增加；
之前有发过贴子：http://bbs.csdn.net/topics/390680857
2、经实际测试，内部处理的速度在每分钟才5条记录左右，但不占CPU，不占内存；
外部处理的速度在1秒1条左右，速度较快，但非常占CPU，个人认为这个速度还是偏慢。

Answer 17

不会用CLR，我目前是通过VBS+ADODB.Connection的方式来处理，但是1秒只能处理1条，速度还是偏慢，而且非常占CPU。

Answer 18

需要如何做？你用的是vb是吧，那就定义数组，然后连接上数据库,先把数据加载到内存，然后再循环处理，也就是就不需要再次从sql server取数据了：dim array_col1 as int
dim array_col2 as string
dim i inti = 1do while not rs.movenext
    array_col1(i) =  rs("id")
    array_col2(i) =  rs("xx")
    rs.movenext

Answer 19

update [MainDataBase].[dbo].[MainTable] 
set SubId =cast([MainDataBase].[dbo].[MainTable].SubId as nvarchar) + (select ','+ CAST(t.ID as nvarchar) from [SubDataBase].[dbo].[SubTable] t
                  where t.Description like '%'+[MainDataBase].[dbo].[MainTable].Title+'%'
                  for xml path('')
           ) Where SubId = '0'以下是执行计划的图

Answer 20

update的时候做这种转换会很慢的，你不如把需要处理的结果集放入一个临时表，加个索引，然后在关联UPDATE

Answer 21

需要如何做？你用的是vb是吧，那就定义数组，然后连接上数据库,先把数据加载到内存，然后再循环处理，也就是就不需要再次从sql server取数据了：dim array_col1 as int
dim array_col2 as string
dim i inti = 1do while not rs.movenext
    array_col1(i) =  rs("id")
    array_col2(i) =  rs("xx")
    rs.movenext
谢谢！这种方式不可行的，数量太宠大了。

Answer 22

需要如何做？你用的是vb是吧，那就定义数组，然后连接上数据库,先把数据加载到内存，然后再循环处理，也就是就不需要再次从sql server取数据了：dim array_col1 as int
dim array_col2 as string
dim i inti = 1do while not rs.movenext
    array_col1(i) =  rs("id")
    array_col2(i) =  rs("xx")
    rs.movenext
谢谢！这种方式不可行的，数量太宠大了。因为一般的like '%xx%'没办法用索引，要是能用索引，就能提高速度了。那就试试用全文检索技术，速度还是非常快的。

Answer 23

步骤1：
select cast([MainDataBase].[dbo].[MainTable].SubId as nvarchar) + (select ','+ CAST(t.ID as nvarchar) subid into #t
from [SubDataBase].[dbo].[SubTable] t
                  where t.Description like '%'+[MainDataBase].[dbo].[MainTable].Title+'%' for xml path('')
步骤2：update [MainDataBase].[dbo].[MainTable] 
set [MainTable].SubId =#t.subid
from #t Where [MainTable].subid=#t.subid [MainTable].SubId = '0'试试这个

Answer 24

select into的速度很快的，百万数据也就几秒钟，加上处理可能分钟级别。然后再update会快很多

Answer 25

消息 156，级别 15，状态 1，第 1 行
关键字 'into' 附近有语法错误。

Answer 26

SELECT  CAST([MainDataBase].[dbo].[MainTable].SubId AS NVARCHAR)
        + ( SELECT  ',' + CAST(t.ID AS NVARCHAR)
            FROM    [SubDataBase].[dbo].[SubTable] t
            WHERE   t.Description LIKE '%'
                    + [MainDataBase].[dbo].[MainTable].Title + '%'
          FOR
            XML PATH('')
          )  INTO #t

Answer 27

是的，目前是遍历循环的方式，1秒1条左右，非常占CPU

Answer 28

是的，目前是遍历循环的方式，1秒1条左右，非常占CPU因为要运算，所以CPU下不来，以前我改过的存储过程，原有逻辑就是循环拼接ID，和你的类似，数据量30多万，监控了一个小时，大概预估要50多个小时才能处理完，后来我用上面拼接的方法，不是我插入临时表那个，最后36秒。

Answer 29

无法绑定由多个部分组成的标识符 "[MainDataBase].[dbo].[MainTable].SubId"

Answer 30

SELECT  CAST([MainDataBase].[dbo].[MainTable].SubId AS NVARCHAR)
        + ( SELECT  ',' + CAST(t.ID AS NVARCHAR)
            FROM    [SubDataBase].[dbo].[SubTable] t
            WHERE   t.Description LIKE '%'
                    + [MainDataBase].[dbo].[MainTable].Title + '%'
          FOR
            XML PATH('')
          ) as  subid INTO #t
我刚才说少了的，你没加？

Answer 31

这种方法也可以实现，但效率与我原来的方法是一样的，也差不多1秒1条，同样非常占CPU。

Answer 32

SELECT  CAST([MainDataBase].[dbo].[MainTable].SubId AS NVARCHAR)
        + ( SELECT  ',' + CAST(t.ID AS NVARCHAR)
            FROM    [SubDataBase].[dbo].[SubTable] t
            WHERE   t.Description LIKE '%'
                    + [MainDataBase].[dbo].[MainTable].Title + '%'
          FOR
            XML PATH('')
          ) 
你这句能执行不？给我看看结果，从你UPDATE中抽出来的

Answer 33

换一个方式，你看看这种会不会快一点--> 测试数据：[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'   declare @S nvarchar(100)
    select @S=isnull(@S+',','')+CAST([id] AS VARCHAR) id from [tablename]
SELECT @S
/*
id
----------------------------------------------------------------------------------------------------
1,2,3,4,5,6
*/

Answer 34

消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "MainDataBase.dbo.MainTable.SubId"。
消息 4104，级别 16，状态 1，第 5 行
无法绑定由多个部分组成的标识符 "MainDataBase.dbo.MainTable.Title"。