昨天发的复杂SQL查询问题效率太低，有朋友可以解决一下吗？

Question

原帖请见：
http://topic.csdn.net/u/20100311/12/dfd2e87f-49c9-4892-86b2-6356c6ac4167.htmlCREATE TABLE [dbo].[table4Item](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [str] [varchar](50)  
 
)  
 
GO
CREATE TABLE [dbo].[table6Item](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [str] [varchar](50)  ) GO
 --插入测试数据insert into table4Item(str) values('01,09,11,44')
insert into table4Item(str) values('07,14,55,42')
insert into table4Item(str) values('01,09,11,27')
insert into table4Item(str) values('07,14,55,16')
insert into table4Item(str) values('01,09,22,44')
insert into table4Item(str) values('07,14,99,42')
insert into table4Item(str) values('01,33,11,44')
insert into table4Item(str) values('07,66,55,42')
insert into table4Item(str) values('88,99,11,44')
insert into table4Item(str) values('69,59,55,42')
insert into table4Item(str) values('79,09,39,44')
insert into table4Item(str) values('91,21,42,44')
insert into table6Item(str) values('01,02,01,09,11,44')
insert into table6Item(str) values('55,02,03,04,05,06')
insert into table6Item(str) values('01,66,03,04,05,22')
insert into table6Item(str) values('77,02,03,04,05,11')
insert into table6Item(str) values('88,02,03,04,05,06')
insert into table6Item(str) values('99,02,03,04,05,55')
insert into table6Item(str) values('12,07,14,55,16,06')
insert into table6Item(str) values('13,02,03,04,05,06')
insert into table6Item(str) values('14,02,03,04,05,47')
insert into table6Item(str) values('07,14,99,42,05,83')
insert into table6Item(str) values('15,02,03,04,05,06')
insert into table6Item(str) values('16,02,03,04,05,06')
insert into table6Item(str) values('17,02,88,99,11,44')
insert into table6Item(str) values('18,02,03,04,05,06')
insert into table6Item(str) values('30,02,03,04,05,06')
insert into table6Item(str) values('50,02,03,04,05,06')
insert into table6Item(str) values('49,02,01,09,11,27')
insert into table6Item(str) values('48,02,03,04,05,06')
insert into table6Item(str) values('47,02,03,04,05,06')
insert into table6Item(str) values('33,02,69,59,55,42')
insert into table6Item(str) values('84,02,03,04,05,06')
insert into table6Item(str) values('85,02,07,14,55,42')
insert into table6Item(str) values('86,02,03,04,05,06')
insert into table6Item(str) values('44,02,91,21,42,62')
insert into table6Item(str) values('71,02,03,04,05,06')
insert into table6Item(str) values('70,02,03,04,05,06')
insert into table6Item(str) values('69,02,03,04,05,29')
insert into table6Item(str) values('43,02,03,04,05,06')
insert into table6Item(str) values('23,02,03,04,05,17')
insert into table6Item(str) values('69,79,09,39,44,29')
insert into table6Item(str) values('43,02,91,21,42,44')
insert into table6Item(str) values('23,69,59,55,42,17')go
create function f_sort_str(@s varchar(1000))
returns varchar(1000)
as
begin
    declare @t table(N int)
    declare @ret varchar(1000)
    declare @tt table(col varchar(10))
    insert into @t SELECT top 26 number  
    FROM master..spt_values where type = 'p' order by number
    insert into @tt select substring(@s,n,charindex(',',@s+',',n)-n)
    from @t
    where substring(','+@s,n,1)=','
    select @ret = isnull(@ret+',','')+col from @tt
    return @ret
end
goselect a.*
from table6Item a,table4Item b
where charindex(','+dbo.f_sort_str(b.str)+',',','+dbo.f_sort_str(a.str)+',')>0
/*
id          str
----------- --------------------------------------------------
1           01,02,01,09,11,44
22          85,02,07,14,55,42
17          49,02,01,09,11,27
7           12,07,14,55,16,06
10          07,14,99,42,05,83
13          17,02,88,99,11,44
20          33,02,69,59,55,42
32          23,69,59,55,42,17
30          69,79,09,39,44,29
31          43,02,91,21,42,44(10 行受影响)
*/
drop table table6Item,table4Item
drop function f_sort_str
这是其中一位朋友写的SQL,还有另外2位朋友的，我都测试过，小数据没问题
当table4item有2万条记录，table6item有10万条记录的时候就会执行无法完成，执行了1个小时，结果把C盘空间弄满了，tempdb.mdf这个文件整整多了28GB的大小，SQL报错说是tempdb无法分配了，空间不够用了。
请问下有什么解决方案吗？
2万记录对应5万记录查询对比，差不多要运算2万*5万次吧，这样算普通计算机都运算不过来。

Answer 1

select Aid,Bid,cstr=max(cstr) 
from(
select     Aid=a.id,Bid=c.id,g=charindex(substring(a.str,b.number,charindex(',',a.str+',',b.number)-b.number),c.str),cstr=c.str
from table4Item a,spt_values b,table6Item c
where b.number<=len(a.str) and type='p' and number>0
and substring(','+a.str,b.number,1)=',' 
)t
group by Aid,Bid
having sum(case when g>0 then 1 else 0 end)>=4
order by Bid,Aid/*
Aid         Bid         cstr
----------- ----------- --------------------------------------------------
1           1           01,02,01,09,11,44
4           7           12,07,14,55,16,06
6           10          07,14,99,42,05,83
9           13          17,02,88,99,11,44
3           17          49,02,01,09,11,27
10          20          33,02,69,59,55,42
2           22          85,02,07,14,55,42
12          24          44,02,91,21,42,62
11          30          69,79,09,39,44,29
12          31          43,02,91,21,42,44
10          32          23,69,59,55,42,17(11 行受影响)*/这位朋友的也试过，一样会把C盘搞满，空间不够用，不知道有没有什么解决方案？

Answer 2

如果是这样的数据分割,除了如下的方法,貌似没其他好办法.
/*
标题：数据拆分1
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005) 
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 3

相对来说 2000用临时表 2005用CTE应该效率高一点 但是那都是相对的 要想效率高 就需要把表结构改成
id     name
1        a
1        b
2        a
3        a
3        b这样的

Answer 4

表结构不能变，因为每天的数据格式是这样固定的，一个表2万条记录(table4Item)，一个表1万-8万条记录(table6Item)，要按4项匹配那个条件去查询对比，有什么方法可以在半个小时内运算完成吗？无论用什么办法，无论用什么程序，只要能运算出来就好。

Answer 5

用2005的CTE 加索引试下

Answer 6

这位朋友可以告诉我一下具体做法吗？
我没有用过CTE，我机器有安装sql server 2005

Answer 7

确实没有更好的办法，只能一个一个比对。 修改结构吧，来的痛快些。 dawugui的拆行很好用的。 

Answer 8

--直接这样跑跑看？
select * from item6 as A
where 
exists(select top 1 1 from item4 
      where charindex(','+parsename(replace([str],',','.'),1)+',' , ','+rtrim(A.[str])+',')>0 
        and charindex(','+parsename(replace([str],',','.'),2)+',' , ','+rtrim(A.[str])+',')>0 
        and charindex(','+parsename(replace([str],',','.'),3)+',' , ','+rtrim(A.[str])+',')>0
        and charindex(','+parsename(replace([str],',','.'),4)+',' , ','+rtrim(A.[str])+',')>0
        )

Answer 9

用05的xml方法应该是最快了，比cte和临时表都快，你可以试试，就是乌龟2楼写的

Answer 10

select a.id,b.*
from  [dbo].[table4Item] a,[dbo].[table6Item] b
where charindex(','+left(a.str,2)+',',','+b.str+',')>0 
and charindex(','+substring(a.str,4,2)+',',','+b.str+',')>0 
and charindex(','+substring(a.str,7,2)+',',','+b.str+',')>0 
and charindex(','+right(a.str,2)+',',','+b.str+',')>0           id          id str
----------- ----------- --------------------------------------------------
          1           1 01,02,01,09,11,44
          2          22 85,02,07,14,55,42
          3          17 49,02,01,09,11,27
          4           7 12,07,14,55,16,06
          6          10 07,14,99,42,05,83
          9          13 17,02,88,99,11,44
         10          20 33,02,69,59,55,42
         10          32 23,69,59,55,42,17
         11          30 69,79,09,39,44,29
         12          24 44,02,91,21,42,62
         12          31 43,02,91,21,42,44(11 行受影响)

Answer 11

老弟 XML的效率最高?? 你TRY一下

Answer 12

我不明白2楼这样做要怎么实现啊？好像跟我的那个不一样啊，有<v>这样的标签

Answer 13

直接跑差不多，C盘空间越来越小，tempdb一直在增，不知道什么时候能跑完，等会儿时候长又会报错了

Answer 14

用微软的.Net控制台应用程序来跑，时间也很长，跑了几个小时都跑不出结果来。
dawugui (爱新觉罗.毓华) 
这位兄弟能告诉我具体做法吗？或者你帮我写一下，我这样直接用2楼的代码不太会，谢谢。
using System;
using System.Collections.Generic;
using System.Text;namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //listSource这个列表元素大概在1万-2万之间，每个元素里面的子项都以“,”分割开。每个元素里面固定4个子项。
            List<String> listSource = new List<String>();
            //listDynamic这个列表元素大概在5万-10万之间，每个元素里面的子项都以“,”分割开。每个元素里面固定6个子项。
            List<String> listDynamic = new List<String>();
            //随便添加一些测试数据，实际上数据都是从数据库取出来的，动态变化的
            listSource.Add("01,05,09,10");
            listSource.Add("01,05,09,11");
            listSource.Add("01,03,02,44");
            listSource.Add("02,01,03,28");
           
            listDynamic.Add("01,02,03,07,09,27");
            listDynamic.Add("01,02,03,07,09,28");
            listDynamic.Add("01,02,03,07,09,44");
            listDynamic.Add("01,02,03,07,09,10");
            listDynamic.Add("01,02,03,07,09,55");
            listDynamic.Add("01,02,03,07,09,77");
            listDynamic.Add("01,02,03,07,09,08");            
            //定义个结果列表，存放满足条件的行
            List<String> listResults = new List<String>();
            foreach (String a in listSource)
            {
                foreach (String b in listDynamic)
                {
                    if (than(a, b, 4, 4))
                    {
                        listResults.Add(b);
                    }
                }
            }            foreach (String result in listResults)
            {
                Console.WriteLine(result);
            }
            Console.ReadKey();
        }
        /// <summary>
        /// 比较筛选
        /// </summary>
        /// <param name="stText">字符串（行/元素）</param>
        /// <param name="stRed">字符串（行/元素）</param>
        /// <param name="iStart">元素里面子项最少包含的数字，这个可以变</param>
        /// <param name="iEnd">元素里面子项最多包含的数字，这个可以变</param>
        /// <returns></returns>
        static bool than(String stText, String stRed, int iStart, int iEnd)
        {
            int iCount = 0;
            String[] arText = stText.Split(',');
            String[] arRed = stRed.Split(',');
            for (int i = 0; i < arText.Length; i++)
            {
                for (int j = 0; j < arRed.Length; j++)
                {
                    if (arText[i] == arRed[j])
                    {
                        iCount++;
                    }
                }
            }
            if (iCount >= iStart && iCount <= iEnd)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
//输出
/*
01,02,03,07,09,27
01,02,03,07,09,28
*/

Answer 15

把tempdb移到空间大的磁盘区，

Answer 16

不知道你的server是什么配置，我觉得我#17的写法，应该能跑出来吧？
没有数据测试，不多说了

Answer 17

感觉用SQL05的 CTE可能会快点

Answer 18

跑出来了，我笔记本电脑配置太低了，我用朋友的高速电脑，1个小时跑出来的。
而用 .NET 应用程序只需要25分钟，我决定用程序处理，不用SQL，SQL空间、资源都耗费太多。结贴

Answer 19

xys_777 感谢您的参与，我是做C#网站程序的，实在没办法才这样。能实现就好了，我告诉大家吧，我做个算法是用来算彩票啊，双色球号码太多，不好计算，过滤的软件也不能随心所欲，所以自己想个方法提高一下庄家选择我的号码作为开奖号的概率。高概率 + 运气 = 中奖！没准儿那天庄家就选择我的号码作为开奖号，我会来感谢大家的！