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]

GO
SET STATISTICS TIME  OFF
SET STATISTICS IO  OFF

GO

上一页  [1] [2] [3]