关于自连接的嵌套子查询

[zpzpyesterday]

SELECT DISTINCT  A,B
FROM dbo.TB_test a  
WHERE B< '2010-02-01'
AND NOT EXISTS  
(SELECT 1 FROM dbo.TB_test
WHERE A = a.A AND C <> 1)

TB_test 这个表是有几百万条数据的表，表中有A,B,C 3列
上面这个查询，效率感觉很低，
大家给出个意见，看看怎么改善能够好些，
多谢了

[水族杰纶]

--try
select a.a,
       a.b
from dbo.tb_test a,dbo.tb_test b
where a.a=b.a and a.b<'2010-02-01'
and b.c!=1

[--小F--]

a字段加索引

[水族杰纶]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL code--tryselect a.a,
a.bfrom dbo.tb_test a,dbo.tb_test bwhere a.a=b.aand a.b<'2010-02-01'and b.c!=1
[/Quote]
sorry
貌似不對

[--小F--]

改嵌套查询为连接查询
select
 a.a,a.b
from
 dbo.tb_test a,dbo.tb_test b
where
 a.a=b.a and a.b<'2010-02-01'
and
 b.c<>1

[ws_hgo]

try

select * from
(
 SELECT 1 FROM dbo.TB_test WHERE A = a.A AND C <> 1
) a
where B< '2010-02-01'

[--小F--]

---使用索引优化数据库查询效率
1.不宜创建索引的情形
(1)经常插入，修改和删除的表
(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间

2.适合创建索引的情形
(1)为where子句中出现的列创建索引
(2)创建组合索引
(3)为group by 子句中出现的列创建索引

3.聚集索引的设计原则
(1)该列的数值是唯一的或者很少有重复的记录
(2)经常使用between ...and..按顺序查询的列
(3)定义identity的唯一列.
(4)经常用于对数据进行排序的列.

---无法使用索引的select语句
1.对索引列使用了函数,如：
select * from tb where max(id)=100

2.对索引列使用了'%xx'，如：
select * from tb where id like '%1'
需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如
select * from tb where id like '1%'就可以使用索引

3.在where子句中对列进行类型转换(其实也是使用到了函数)

4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；
select * from tb where id='1' and col1='aa'
select id,sum(col1) from tb group by id
select * from tb where id='2' and col2='bb'
则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序

5.在where 子句中使用in关键字的某些句子
当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引
如：
select 
 *
from
 ta 
where
 id 
in
 (select id from tb where ....)

--这样可以用到索引
select * from tb where id in('1','2')

[nianran520]

LZ写的语句貌似有问题
create table TB_test(A varchar(20),B datetime,C int)

insert into TB_test
select '001','2009-09-12',1 union all
select '001','2010-02-01',1 union all
select '001','2010-02-01',2 union all
select '001','2010-02-02',1 union all
select '001','2010-02-04',3 union all
select '001','2009-12-12',1 union all
select '001','2009-10-12',4 union all
select '002','2009-09-12',1 union all
select '002','2010-02-01',1 union all
select '002','2010-02-01',2 union all
select '002','2010-02-02',1 union all
select '002','2010-02-04',3 union all
select '002','2009-12-12',1 union all
select '002','2009-10-12',4 union all
select '002','2009-08-10',1 


SELECT DISTINCT  A,B
FROM dbo.TB_test a  
WHERE B< '2010-02-01'
AND  not EXISTS  
(SELECT 1 FROM dbo.TB_test
WHERE A = a.A AND C <> 1)

[zpzpyesterday]

多谢回复

[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code改嵌套查询为连接查询
select DISTINCT
a.a,a.b from
dbo.tb_test a,dbo.tb_test bwhere
a.a=b.aand a.b<'2010-02-01'and
b.c<>1
[/Quote]

(34098 件处理)

SELECT DISTINCT  A,B
FROM dbo.TB_test a  
WHERE B< '2010-02-01'
AND NOT EXISTS  
(SELECT 1 FROM dbo.TB_test
WHERE A = a.A AND C <> 1)

(1700 件处理)

但是两个的执行结果好像是有些出入

[zpzpyesterday]

[Quote=引用 7 楼 nianran520 的回复:]
SQL codeLZ写的语句貌似有问题createtable TB_test(Avarchar(20),Bdatetime,Cint)insertinto TB_testselect'001','2009-09-12',1unionallselect'001','2010-02-01',1unionallselect'001','2010-02-01',2unionallselect'001','?-
[/Quote]

多谢回复，
我是想通过语句查找，A列相同的情况下，C列不全为1的列

[zpzpyesterday]

[Quote=引用 9 楼 zpzpyesterday 的回复:]
引用 7 楼 nianran520 的回复:
SQL codeLZ写的语句貌似有问题createtable TB_test(Avarchar(20),Bdatetime,Cint)insertinto TB_testselect'001','2009-09-12',1unionallselect'001','2010-02-01',1unionallselect'001','2010-02-01',2unionallselect'001','?-


多谢回复，
我是想通过语句查找，A列相同的情况下，C列不全为1的列
[/Quote]

写错了，A列相同的情况下，C列全为1的列

[qlh]

哥们,要解决问题,你的给出点数据,还有结果,大家才好帮你看呀.不要让人猜吗.

[zpzpyesterday]

[Quote=引用 11 楼 qlh2863 的回复:]
哥们,要解决问题,你的给出点数据,还有结果,大家才好帮你看呀.不要让人猜吗.
[/Quote]

呵呵，是是
借用7楼的例子

create table TB_test(A varchar(20),B datetime,C int)

insert into TB_test
select '001','2009-09-12',1 union all
select '001','2010-02-01',1 union all
select '001','2010-02-01',2 union all
select '001','2010-02-02',1 union all
select '001','2010-02-04',3 union all
select '001','2009-12-12',1 union all
select '001','2009-10-12',4 union all
select '002','2009-09-12',1 union all
select '002','2010-02-01',1 union all
select '002','2010-02-01',2 union all
select '002','2010-02-02',1 union all
select '002','2010-02-04',3 union all
select '002','2009-12-12',1 union all
select '002','2009-10-12',4 union all
select '002','2009-08-10',1 union all
select '003','2009-08-10',1
select * from TB_test

SELECT DISTINCT  A,B
FROM dbo.TB_test a  
WHERE B< '2010-02-01'
AND  not EXISTS  
(SELECT 1 FROM dbo.TB_test
WHERE A = a.A AND C <> 1)

A B
003 2009-08-10 00:00:00.000

(1 件)

[hughie]

SELECT A,B
FROM dbo.TB_test T INNER JOIN
(SELECT A FROM dbo.TB_test WHERE B< '2010-02-01' GROUP BY A HAVING MAX(C)=1) M
ON T.A=M.A
WHERE T.B< '2010-02-01'

[Garnett_KG]

[Quote=引用 10 楼 zpzpyesterday 的回复:]
引用 9 楼 zpzpyesterday 的回复:
引用 7 楼 nianran520 的回复:
SQL codeLZ写的语句貌似有问题createtable TB_test(Avarchar(20),Bdatetime,Cint)insertinto TB_testselect'001','2009-09-12',1unionallselect'001','2010-02-01',1unionallselect'001','2010-02-01',2unionallselect'001','?-


多谢回复，
我是想通过语句查找，A列相同的情况下，C列不全为1的列


写错了，A列相同的情况下，C列全为1的列

[/Quote]


select A FROM TB_test
WHERE B< '2010-02-01'
GROUP BY A
HAVING COUNT(DISTINCT C)=1 AND MAX(C)=MIN(C) AND MAX(C)=1

[幸运的意外]

不存在C<>1,应当就是C=1吧,所以

select
a.a,
a.b
from
dbo.tb_test a
join
dbo.tb_test b
on
a.a=b.a
and
a.b<'2010-02-01'
and
b.c=1

[yingzhilian2008]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL code--tryselect a.a,
a.bfrom dbo.tb_test a,dbo.tb_test bwhere a.a=b.aand a.b<'2010-02-01'and b.c!=1
[/Quote]赞同