SQLSERVER 查询百万级数据的表 

 1 /*b━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━*/
 2 --While 循环
 3 -----------------------------------------------------------------------------------------------------------------------------------
 4 IF(SELECT OBJECT_ID('tempdb..#tmp_Ret')) IS NOT NULL
 5     DROP TABLE tempdb..#tmp_Ret  ;
 6     
 7 CREATE TABLE #tmp_Ret(
 8 Id INT IDENTITY(1,1),
 9 TbName nvarchar(300),
10 TbRowCount INT,
11 TbRowCountLen INT
12 );
13 
14 IF(SELECT OBJECT_ID('tempdb..#Temp_Lists')) IS NOT NULL
15     DROP TABLE tempdb..#Temp_Lists  ;
16 -- 获取待处理的数据记录到临时表
17 -- 字段说明：REFID：记录行号 / DealFlg：行处理标识
18 SELECT  REFID = IDENTITY(INT , 1, 1), DealFlg = 0, ORDERID=CAST(name AS NVARCHAR(400))
19 INTO #Temp_Lists
20 FROM sysobjects
21 WHERE xtype='U';
22 
23 DECLARE @REFID            INT
24         ,@ORDERID      NVARCHAR(300)
25         ,@LXH_SQLStr    NVARCHAR(4000)
26         ,@LXH_Name        NVARCHAR(300)
27         ,@LXH_Count        INTEGER
28         ,@LXH_Len        INTEGER;
29 
30 -- 获取临时表数据的最小行号
31 SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0;
32         
33 -- 若最小行号不为空（有需要处理的数据）
34 WHILE @REFID IS NOT NULL
35 BEGIN
36     -- 获取当前处理行的信息
37     SELECT @ORDERID = ORDERID FROM  #Temp_Lists WHERE REFID = @REFID;
38     
39     /*     
40     此处编写对当前行数据的业务逻辑处理代码        
41     */
42      SET @LXH_SQLStr = 'SELECT  @Curr_Count=COUNT(*),@Curr_Len=LEN( CAST(COUNT(*) AS NVARCHAR)) FROM '+@ORDERID+' WITH(NOLOCK)'                         
43      EXEC sp_executesql @LXH_SQLStr, N'@Curr_Count INTEGER OUT,@Curr_Len INTEGER OUT',@LXH_Count OUT, @LXH_Len OUT;  
44      
45      IF( PATINDEX('%[0-9]%',@ORDERID)=0)
46         INSERT INTO #tmp_Ret(TbName, TbRowCount,TbRowCountLen )
47         VALUES  (@ORDERID,@LXH_Count,@LXH_Len);
48     
49     -- 标识当前行已处理完毕
50     UPDATE #Temp_Lists SET DealFlg = 1 WHERE REFID = @REFID
51     
52     -- 选择下一行号
53     SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 AND REFID > @REFID
54 END
55 SELECT * FROM #tmp_Ret WHERE TbRowCountLen IN(7,8,9,10,11,12) ORDER BY  TbRowCountLen DESC,TbRowCount DESC,TbName
56 
57 SELECT 'Num'=COUNT(*),TbRowCountLen FROM #tmp_Ret WHERE TbRowCountLen IN(7,8,9,10,11,12) 
58 GROUP BY TbRowCountLen
59 ORDER BY TbRowCountLen DESC;
60 /*e━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━*/
View Code

 

posted @ 2017-08-29 16:07  haikuang  阅读(532)  评论(0)    收藏  举报