有如下两张表 表结构如下:(数据量都比较大 都当有1千万把)
tab1
-------------------------------------------------
id card name type intime
1 1001 jacke a100 20050401
2 1001 jackie a100 20050401
3 1002 anti a200 20050401
4 1002 anti a200 20050401
5 1003 wack a300 20050401
6 1004 waee a400 20050402
7 00 tt a500 20050401
8 00 tt a500 20050401
.. .. .. .. ........
--------------------------------------------
tab2
--------------------------------------------------
type typename other1 other2
a100 import test1 test2
a200 import test3 test4
a300 noimport test5 test6
a400 noimport test7 test8
a500 import test9 test10
.. .. .. ...
--------------------------------现在要做如下查询 在Tab1 中card 出现次数大于1的记录并且时间字段(intime)为指定一具体值(比如20060501),需要显示 card ,name ,type ,intime,typename, other1,other2 这些字段 两张表的关联是 type 这一字段 card 的长度小于4位 不予理睬 不做显示!由于数据两表数据比较大 我做的查询速度慢!我只这么做的
先建立一张试图(view1)
select card ,name ,type intime,typename from tabl where card in(select card from tab1 where intime='20050401' group by card having count(card)>1) and intime='20050401'
然后 view1 有 tab2 在做关联(我做的暂不考虑card的长度) 因为速度太慢 希望大家给我一点建议!!不甚感激!!!
按照要求查询结果应该如下:
1001 jacke a100 20050401 import test1 test2
1001 jacke a100 20050401 import test3 test4
1002 anti a200 20050401 noimport test5 test6
1002 anti a200 20050401 noimport test7 test8
============================================================================
tab1
-------------------------------------------------
id card name type intime
1 1001 jacke a100 20050401
2 1001 jackie a100 20050401
3 1002 anti a200 20050401
4 1002 anti a200 20050401
5 1003 wack a300 20050401
6 1004 waee a400 20050402
7 00 tt a500 20050401
8 00 tt a500 20050401
.. .. .. .. ........
--------------------------------------------
tab2
--------------------------------------------------
type typename other1 other2
a100 import test1 test2
a200 import test3 test4
a300 noimport test5 test6
a400 noimport test7 test8
a500 import test9 test10
.. .. .. ...
--------------------------------现在要做如下查询 在Tab1 中card 出现次数大于1的记录并且时间字段(intime)为指定一具体值(比如20060501),需要显示 card ,name ,type ,intime,typename, other1,other2 这些字段 两张表的关联是 type 这一字段 card 的长度小于4位 不予理睬 不做显示!由于数据两表数据比较大 我做的查询速度慢!我只这么做的
先建立一张试图(view1)
select card ,name ,type intime,typename from tabl where card in(select card from tab1 where intime='20050401' group by card having count(card)>1) and intime='20050401'
然后 view1 有 tab2 在做关联(我做的暂不考虑card的长度) 因为速度太慢 希望大家给我一点建议!!不甚感激!!!
按照要求查询结果应该如下:
1001 jacke a100 20050401 import test1 test2
1001 jacke a100 20050401 import test3 test4
1002 anti a200 20050401 noimport test5 test6
1002 anti a200 20050401 noimport test7 test8
============================================================================
try:select T.card ,T.name ,T.type, T.intime,T.typename from tabl T
where exists (select 1 from tab1 where intime='20050401'and T.card=card group by card having count(card)>1) and T.intime='20050401'
(select T.card ,T.[name], T.type, T.intime
from tabl T
where exists (select 1 from (select card from tabl group by card having count(1)>1) A where A.card=T.card)) T1,tab2 T2
where T2.type=T1.card and T1.intime='20050401'
select a.cart,b.name ,b.type,b.intime,c,typename,c.other1,c.other2 from
(select cart,count(*) colcount from tab1 where len(cart)>=4 and intime='20050401'
group by cart having count(*)>1) a left join tab1 b on a.cart=b.cart
left join tab2 c on c.type=b.type
我没有SQL,所以没有调试,不知是否可以。
tab1按照 card 建非聚焦索引
tab2按照 type建非聚焦索引
create nonclustered index idx_name on tablename()
按照scmail81(琳·风の狼(修罗)) ( ) 信誉:100 的写法
select T1.card ,T1.[name], T1.type, T1.intime ,T2.typename,T2.other1,T2.other2 from
(select T.card ,T.[name], T.type, T.intime
from tabl T
where exists (select 1 from (select card from tabl where len(card) >= 4 group by
--增加len(card) >= 4
card having count(1)>1 ) A where A.card=T.card)) T1,tab2 T2
where T2.type=T1.card and T1.intime='20050401'应该可以了