table1ID Name RefNo FileDir
1 name1 0427 EN-K1
2 name2 0452 DC-K1
3 name3 0427 EN-K2
4 name4 0433 DC-K2
5 name5 0672 DC-K3
6 name6 0123 DC-K1
7 name7 0874 DC-K1
.
.
.
Name 列都是唯一的数据库中结构和上面一样。现在知道Name的值 需要找出
表中的所有记录。条件是
RefNo的值等于 已知Name值那条记录中RefNo。
并且他们隶属于同一个 FileDir的前半部分。
我写了2种方案,不知道哪种效率高。在 RefNo上建立非聚集索引会不会提高点效率呢。1
select * from table1
join
(select substring(FileDir,0,2) as dep ,RefNo from table1 where Name = 'name1'
) a
on table1.RefNo = a.RefNo and substring(FileDir,0,2) =a.dep
2
select * from table1 where RefNo = (select RefNo from table1 where Name = 'name1')
and substring(FileDir,0,2) = (select substring(FileDir,0,2) from table1 where Name = 'Name1')
根据上面的例子,Name = 'Name1'应该可以找出
ID= 1和3 这条记录。表数据有20几万条,上面2条语句哪种效率高啊,有更好的办法吗
1 name1 0427 EN-K1
2 name2 0452 DC-K1
3 name3 0427 EN-K2
4 name4 0433 DC-K2
5 name5 0672 DC-K3
6 name6 0123 DC-K1
7 name7 0874 DC-K1
.
.
.
Name 列都是唯一的数据库中结构和上面一样。现在知道Name的值 需要找出
表中的所有记录。条件是
RefNo的值等于 已知Name值那条记录中RefNo。
并且他们隶属于同一个 FileDir的前半部分。
我写了2种方案,不知道哪种效率高。在 RefNo上建立非聚集索引会不会提高点效率呢。1
select * from table1
join
(select substring(FileDir,0,2) as dep ,RefNo from table1 where Name = 'name1'
) a
on table1.RefNo = a.RefNo and substring(FileDir,0,2) =a.dep
2
select * from table1 where RefNo = (select RefNo from table1 where Name = 'name1')
and substring(FileDir,0,2) = (select substring(FileDir,0,2) from table1 where Name = 'Name1')
根据上面的例子,Name = 'Name1'应该可以找出
ID= 1和3 这条记录。表数据有20几万条,上面2条语句哪种效率高啊,有更好的办法吗
join
(select substring(FileDir,0,2) as dep ,RefNo from table1 where Name = 'name1'
) a
on table1.RefNo = a.RefNo and substring(FileDir,0,2) =a.dep第一种的效率高
在RefNo加索引有效
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-26 23:44:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([ID] int,[Name] varchar(5),[RefNo] varchar(4),[FileDir] varchar(5))
insert [table1]
select 1,'name1','0427','EN-K1' union all
select 2,'name2','0452','DC-K1' union all
select 3,'name3','0427','EN-K2' union all
select 4,'name4','0433','DC-K2' union all
select 5,'name5','0672','DC-K3' union all
select 6,'name6','0123','DC-K1' union all
select 7,'name7','0874','DC-K1'
--------------开始查询--------------------------
select
*
from
[table1] t
where
exists(select 1 from table1 where [RefNo]=t.[RefNo] and left([FileDir],2)=left(t.[FileDir],2) and [Name]<>t.name)
----------------结果----------------------------
/* ID Name RefNo FileDir dep RefNo
----------- ----- ----- ------- ---- -----
1 name1 0427 EN-K1 EN 0427
3 name3 0427 EN-K2 EN 0427(2 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-26 23:44:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([ID] int,[Name] varchar(5),[RefNo] varchar(4),[FileDir] varchar(5))
insert [table1]
select 1,'name1','0427','EN-K1' union all
select 2,'name2','0452','DC-K1' union all
select 3,'name3','0427','EN-K2' union all
select 4,'name4','0433','DC-K2' union all
select 5,'name5','0672','DC-K3' union all
select 6,'name6','0123','DC-K1' union all
select 7,'name7','0874','DC-K1'
--------------开始查询--------------------------
select
*
from
[table1] t
where
exists(select 1 from table1 where [RefNo]=t.[RefNo] and left([FileDir],2)=left(t.[FileDir],2) and [Name]<>t.name)
----------------结果----------------------------
/* ID Name RefNo FileDir
----------- ----- ----- -------
1 name1 0427 EN-K1
3 name3 0427 EN-K2(2 行受影响)*/