none
关于游标中参数使用的问题 RRS feed

  • 问题

  • 问题是这样子的,数据库中有多个表含有IP地址信息,我想统计每个表中间,含有非零IP的记录数占整个表记录的百分比。

    我的想法是这样,用游标在sys.tables中将需要的表名一个一个提取出来,然后计算每个表中含有IP地址的条数的百分比并输出。

    单个表的百分比我可以用以下脚本计算出百分比:

    declare @IP    numeric(12,2)

    declare @rows numeric(12,2)

    declare @para nvarchar(100)

    set @IP = (select count(0) as num from table_name where IP >0)

    set @rows = (select rows from sys.sysindexes where id = object_id('table_name') and indid in (0,1))

    set @para = (select convert(numeric(4,2),@IP / @rows*100) )

    print @para

    但是在游标中我无法用类似@table的变量替代table_name 

    不知道各位有什么好方法可以完成这个功能,多谢指导~~~~Thx!


    凡人有庸俗的快乐,智者有高尚的痛苦
    2010年8月18日 14:28

答案

  • CREATE TABLE #re(
    	object_name sysname,
    	ip int,
    	rows int
    );
    
    INSERT #re
    EXEC sp_msforeachtable
    	@command1 = N'
    SELECT 
    	object_name = N''?'',
    	ip = (
    			select count(0) as num from ?
    			where IP > 0
    		),
    	rows = (
    			select rows from sys.sysindexes 
    			where id = object_id(N''?'')
    				and indid in (0,1))
    		)
    ',
    	@whereand = N'
    AND EXISTS(
    		SELECT * FROM syscolumns COL
    		WHERE COL.id = O.id
    			AND COL.name = N''IP''
    	)
    ';
    SELECT
    	*,
    	para = (select convert(numeric(4,2),IP / rows*100) )
    FROM #re
    
    • 已标记为答案 Vincent-Z 2010年8月20日 1:49
    2010年8月19日 4:40

全部回复

  • CREATE TABLE t1(ip numeric(12,2))
    CREATE TABLE t2(ip numeric(12,2))
    CREATE TABLE t3(noip numeric(12,2))
    GO
    INSERT t1 SELECT 2 UNION ALL SELECT 0 UNION ALL SELECT 3
    INSERT t2 SELECT 4 UNION ALL SELECT 0 UNION ALL SELECT 0
    INSERT t3 SELECT 1
    GO
    DECLARE @s VARCHAR(8000)
    SET @s='';
    SELECT @s=@s+','+object_name(object_id) FROM sys.columns c WHERE name='ip';
    SET @s=replace(@s,',',' SELECT CAST((sum(CASE WHEN ip>0 THEN 1 End)*1.0/count(*)*100) AS DECIMAL(18,2)) as 输出百分比 from ')
    
    exec (@s) 
    
    
    2010年8月18日 14:52
  • 推荐用动态SQL的办法

    动态SQL实现如下:

    Declare @SQL Varchar(200);
    SET @SQL='SELECT count(0) as num FROM ' + @table_name +' WHERE IP>0 ';
    EXEC(@SQL); 
    

    如果是想实现一个返回一个表的话,就用

    INSERT INTO #临时表
    SELECT ......... FROM ..... WHERE ....
    
    
    的格式去组织动态SQL

    2010年8月18日 14:58
  • 多谢 我想把帖子留2天结贴  想看看有没有什么其他的方法
    凡人有庸俗的快乐,智者有高尚的痛苦
    2010年8月19日 1:46
  • CREATE TABLE #re(
    	object_name sysname,
    	ip int,
    	rows int
    );
    
    INSERT #re
    EXEC sp_msforeachtable
    	@command1 = N'
    SELECT 
    	object_name = N''?'',
    	ip = (
    			select count(0) as num from ?
    			where IP > 0
    		),
    	rows = (
    			select rows from sys.sysindexes 
    			where id = object_id(N''?'')
    				and indid in (0,1))
    		)
    ',
    	@whereand = N'
    AND EXISTS(
    		SELECT * FROM syscolumns COL
    		WHERE COL.id = O.id
    			AND COL.name = N''IP''
    	)
    ';
    SELECT
    	*,
    	para = (select convert(numeric(4,2),IP / rows*100) )
    FROM #re
    
    • 已标记为答案 Vincent-Z 2010年8月20日 1:49
    2010年8月19日 4:40
  • 多谢指导
    凡人有庸俗的快乐,智者有高尚的痛苦
    2010年8月20日 1:49