一句T-SQL语句引发的思考_Sql Server_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

一句T-SQL语句引发的思考

作者：闵涛文章来源：闵涛的学习笔记点击数：3471 更新时间：2007/11/14 12:58:16

PRINT ''''第五种语句：''''
SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c
WHERE a.[key]=''''Ac'''' AND b.[key]=''''Bb'''' AND c.[key]=''''Ca''''
AND a.[id]=b.[id] AND a.[id]=c.[id]

GO
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

--先对1百万条/1亿条记录进行测试（选取6列）的T-SQL:
PRINT ''''第一种语句：''''
SET STATISTICS TIME ON
SET STATISTICS IO ON
select a.[id] from
(select distinct [id] from stress_test where [key] = ''''Az'''') a,
(select distinct [id] from stress_test where [key] = ''''Bw'''') b ,
(select distinct [id] from stress_test where [key] = ''''Cv'''') c,
(select distinct [id] from stress_test where [key] = ''''Du'''') d,
(select distinct [id] from stress_test where [key] = ''''Ex'''') e,
(select distinct [id] from stress_test where [key] = ''''Fy'''') f
where a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]
GO
PRINT ''''第二种语句：''''
select [id]
from stress_test
where [key]=''''Az'''' or [key]=''''Bw'''' or [key]=''''Cv'''' or [Key]=''''Du''''or [Key]=''''Ex''''or [Key]=''''Fy''''
group by id having(count(distinct [key])=6)
GO
PRINT ''''第三种语句：''''
select distinct [id] from stress_test A where
not exists (
select 1 from
(select ''''Az'''' as k union all select ''''Bw'''' union all select ''''Cv''''union all select ''''Du''''union all select ''''Ex''''union all select ''''Fy'''') B
left join stress_test C on C.id=A.id and B.[k]=C.[key]
where C.id is null)
GO
PRINT ''''第四种语句：''''
select distinct a.id from stress_test a
where not exists
( select * from keytb c
   where not exists
   ( select * from stress_test b
     where
       b.id = a.id
       and
       c.kf1 = b.[key]
   )
)
GO
PRINT ''''第五种语句：''''
SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c,stress_test AS d,stress_test AS e,stress_test AS f
WHERE a.[key]=''''Az'''' AND b.[key]=''''Bw'''' AND c.[key]=''''Cv'''' AND d.[key]=''''Du'''' AND e.[key]=''''Ex'''' AND f.[key]=''''Fy''''
     and a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]