这个SQL语句，怎么写性能最高？

[baobao]

select top 1 table1.col1 ,table1.colo2
from table1 ,table2
where
'name1' NOT IN (SELECT userid FROM table1 where ...)
AND 'name2' NOT IN (SELECT userid FROM table1 where ...)
AND 'name3' NOT IN (SELECT userid FROM table1 where ...)
AND 'name4' NOT IN (SELECT userid FROM table1 where ...)

这个语句的效率实在太低，怎么能把集合先算出来。也就是把SELECT userid FROM table1 where ...先算出来为一个常量。然后再进行查询
如：
SELECT userid FROM table1 where ... 作为 Arr
然后
select top 1 table1.col1 ,table1.colo2
from table1 ,table2
where
'name1' NOT IN Arr
and 'name2' NOT IN Arr
and 'name3' NOT IN Arr
and 'name4' NOT IN Arr

另外能不能实现类似 'name1','name2','name3','name4' not in Arr（并在一起）这样的写法？

[jlandzpa]

not in 效率很低，想办法避免！

[KingSunSha]

select top 1 table1.col1 ,table1.colo2
from table1 ,table2
where not exists (SELECT 'x' FROM table1 where ...)
....

[Hanson_bati_zhu]

改表结构吧

[ajoo]

table2? typo? you are not using table2 in where clause and select clause at all!

I'll assume you only want table1.

and since you are using sql server
try this:

select top 1 t.col1, t.col2
from table1 t left join
(
SELECT userid into #usr
FROM table1 where ...
)u
on u.userid in(t.name1, t.name2, t.name3, t.name4)
where (this is your original where clause......)
and u.userid is null

you may also use temporary table to speed up the query further.

[ajoo]

sorry, it should be:

select top 1 t.col1, t.col2
from table1 t left join
(
SELECT userid into #usr
FROM table1 where ...
)u
on u.userid in(t.name1, t.name2, t.name3, t.name4)
where u.userid is null

[ajoo]

faint, I always make mistake, it should be:

select top 1 t.col1, t.col2
from table1 t left join
(
SELECT userid
FROM table1 where ...
)u
on u.userid in(t.name1, t.name2, t.name3, t.name4)
where u.userid is null

[superjj2002]

exists or left join

关键是不要做table scan，走索引相对快点。

[baobao]

我把问题说的再清楚一些，
table1里面放的是用户打牌的信息,cardNO,userID,cardinfo ----userID和cardNO组成联合主键。
一局牌会有4条记录生成，即：
cardNO(比如=1),'userId1'，'cardinfo'
cardNO(1),'userId2'，'cardinfo'
cardNO(1),'userId3'，'cardinfo'
cardNO(1),'userId4'，'cardinfo'

table2里放的是 cardNO(主键),CardInfo

现在有四个用户要打牌，必须在table1中找一付他们4个人谁也没打过的牌，得到cardinfo和cardNO，怎样才能使效率最高，用我上面的方法table1里的记录达到10万以上的时候会明显感到慢。

[baobao]

补充一下：俺正在做一个打牌游戏。发排时要从牌库表里找一付他们4人谁也没打过的牌。

table1里面放的是用户打牌的信息,cardNO,userID,cardinfo ----userID和cardNO组成联合主键。
一局牌会有4条记录生成，即：
cardNO(比如=1),'userId1'，'cardinfo'
cardNO(1),'userId2'，'cardinfo'
cardNO(1),'userId3'，'cardinfo'
cardNO(1),'userId4'，'cardinfo'

table2里放的是所有打过的牌的 cardNO(主键),CardInfo

现在有四个用户('name1','name2','name3','name4')要打牌，必须在table1中找一付他们4个人谁也没打过的牌，得到cardinfo和cardNO，怎样才能使效率最高，用我的方法table1里的记录达到10万以上的时候会明显感到慢。

我的方法：
select top 1 t2.cardNO,t2.cardinfo from table1 as t1,table2 as t2
where t1.cardNO=t2.cardNO
and 'name1' not in (select userid from table1 where cardNO=t2.cardNO )
and 'name2' not in (select userid from table1 where cardNO=t2.cardNO )
and 'name3' not in (select userid from table1 where cardNO=t2.cardNO )
and 'name4' not in (select userid from table1 where cardNO=t2.cardNO )

请大家帮忙！

[superjj2002]

就sql效率来讲，2楼和4楼已说过

[ajoo]

select top 1 t2.cardno, t2.cardinfo from table2 t2
inner join table1 t1
on t1.cardno = t2.cardno
left join
(select cardno from table1 where userid in ('name1', 'name2', 'name3', name4')
)x
on t1.cardno = x.cardno
where x.cardno is null

But seems you already have the cardinfo in table1. If that's true(you probably did some denormalization), you don't need to join table2 at all. the following query will do:

select top 1 t1.cardno, t1.cardinfo from table1 t1
left join
(select cardno from table1 where userid in ('name1', 'name2', 'name3', name4')
)x
on t1.cardno = x.cardno
where x.cardno is null

[chenghenly]

真的很不错！
agree to upstairs

[SE1]

首先找出4个用户（'name1'、'name2'、'name3'、'name4'）中任何一个用户打过的牌：
select CardNo
from table1
where UserId in ('name1','name2','name3','name4')

然后，求出table2和上述结果集在CardNo上的差集，可以用not exit、left join ... is null、not in实现，在数据集较大时，not exit效率最高、left join ... is null次之、not in最低。
实现：
select top 1 *
from table2 t1
where not exit(select *
from (select CardNo
from table1
where UserId in ('name1','name2','name3','name4')
) t2
where t1.CardNo=t2.CardNo
)

[SE1]

to baobao(天下草木皆能当剑):
上述对效率的评价是基于“预计执行计划”评估的，基于一个200万记录的表，但结构不同。
你可以结合你自己的数据进行测试。

[playmud]

改结构吧，
一张牌一条纪录，打没打过可以做一个标志位。
牌的表结构如下
id 花色 数字 谁持有 标志位（是否打出，是否发出）....

[ajoo]

who says "not exists" is more efficient than left join?
with "not exists", you will always end up with "nested loop", no difference with "not in" at all!
While left join can take advantage of indexes and "merge join"

And, merge join is the most efficient way for large table.

[ajoo]

不要总相信书上说的话. 如果你真写过对上百万条纪录的表的查询,你就会知道"not exists" 会比"left join"慢多少.那可能是几十个小时和几分钟的差别!
到books online上去看看关于"NESTED LOOP", "MERGE JOIN"的文章.
再用QUERY ANALYZER去分析一下query plan, 你酒会知道为什么"NOT EXISTS"比"left join"慢了.
什么都得自己试验了才算明白呀.

[SE1]

呵呵，我的结论不是从书上来的，而是基于一个200万条记录（不知是否够大），然后使用QUERY ANALYZER分析query plan得出的。
也许，QUERY ANALYZER分析query plan也有乱来的？
这样，就只有用具体的数据来针对具体的情况作测试了。

[ajoo]

Really? Interesting.
what was the join plan when you used "not exists"?
"merge join" or "nested loop"?
What was the difference you got between "not exists" and "left join"?