SELECT COUNT(DISTINCT name) c FROM t a WHERE EXISTS(SELECT * FROM ety b WHERE b.name=a.name)a的数据量有50万多 b的10多万 现在要统计a中name的个数,a.name满足如下条件 1. 在b中有相应匹配项 2. 不计重复 PS:索引已建
应该是最简单的了SELECT COUNT(DISTINCT name) c FROM t a WHERE EXISTS(SELECT 1 FROM ety b WHERE b.name=a.name)
现在是对多个表进行这样的操作,然后全部UNION ALL 作为一条语句执行的这样有加速度的办法吗?sql server 2000 50万条都搞不定不会吧?
a表大,b表小,a与b 是内连接的关系, a,b位置交换,速度可能会更快 SELECT COUNT(DISTINCT name) c FROM ety b WHERE EXISTS(SELECT * FROM t a WHERE b.name=a.name)结果是一样的,如果b的name不重复,distinct可去掉.
to cxmcxm(小陈): 绝对快啊,哈哈, 不知sql server 2000这么优化了没有,试一下先
SELECT COUNT(DISTINCT name) c FROM t a WHERE EXISTS(SELECT * FROM ety b WHERE b.name=a.name)没有试过,我觉得我这样写会更快一点 select count(distinct a.[name]) as c1 from T a left join ety b on a.[name]=b.[name]
tryselect count(1) c from ety b,(select DISTINCT name from A )a where b.name=a.name
SELECT COUNT(DISTINCT a.name) c FROM t a, ety b on b.name=a.name试试
用in和exists的速度差不多
作为一条语句执行的这样有加速度的办法吗?sql server 2000 50万条都搞不定不会吧?
a,b位置交换,速度可能会更快
SELECT COUNT(DISTINCT name) c FROM ety b WHERE EXISTS(SELECT * FROM t a WHERE b.name=a.name)结果是一样的,如果b的name不重复,distinct可去掉.
绝对快啊,哈哈,
不知sql server 2000这么优化了没有,试一下先
select count(distinct a.[name]) as c1
from T a
left join ety b on a.[name]=b.[name]
where b.name=a.name
FROM t a, ety b
on b.name=a.name试试