SQL查询性能优化问题,请大侠看看!

[degehuawu]

DECLARE @table TABLE
(id INT PRIMARY KEY,bundle NVARCHAR(20),section NVARCHAR(20))
INSERT INTO @table VALUES(1,'001','A')
INSERT INTO @table VALUES(2,'001','A')
INSERT INTO @table VALUES(3,'002','A')
INSERT INTO @table VALUES(4,'002','B')
INSERT INTO @table VALUES(5,'002','B')
INSERT INTO @table VALUES(6,'003','B')
INSERT INTO @table VALUES(7,'003','A')
INSERT INTO @table VALUES(8,'003','C')
INSERT INTO @table VALUES(9,'004','A')
INSERT INTO @table VALUES(10,'005','D')
INSERT INTO @table VALUES(11,'005','A')
SELECT * FROM @table
示例表(id为主键)
id bundle section
1 001 A
2 001 A
3 002 A
4 002 B
5 002 B
6 003 B
7 003 A
8 003 C
9 004 A
10 005 D
11 005 A

问:筛选出bundle重复个数为n(没确定的值),但section不同的行,
只有一个bundle的不用查出来,请注意,该表数据量非常大,必须要考虑性能(可以提出索引优化等建议)
比如n=2筛选结果应为:
id bundle section
3 002 A
4 002 B
5 002 B
6 003 B
7 003 A
8 003 C
10 005 D
11 005 A
比如n=3筛选结果应为:
id bundle section
3 002 A
4 002 B
5 002 B
6 003 B
7 003 A
8 003 C

[--小F--]

DECLARE @table TABLE 
(id INT PRIMARY KEY,bundle NVARCHAR(20),section NVARCHAR(20)) 
INSERT INTO @table VALUES(1,'001','A') 
INSERT INTO @table VALUES(2,'001','A') 
INSERT INTO @table VALUES(3,'002','A') 
INSERT INTO @table VALUES(4,'002','B') 
INSERT INTO @table VALUES(5,'002','B') 
INSERT INTO @table VALUES(6,'003','B') 
INSERT INTO @table VALUES(7,'003','A') 
INSERT INTO @table VALUES(8,'003','C') 
INSERT INTO @table VALUES(9,'004','A') 
INSERT INTO @table VALUES(10,'005','D') 
INSERT INTO @table VALUES(11,'005','A') 
--SELECT * FROM @table 
select
 * 
from
 @table t
where
 bundle in(select bundle from @table group by bundle having count(1)>=2)
and
 exists(select 1  from @table where bundle=t.bundle and section<>t.section)
/*id          bundle               section
----------- -------------------- --------------------
3           002                  A
4           002                  B
5           002                  B
6           003                  B
7           003                  A
8           003                  C
10          005                  D
11          005                  A

(8 行受影响)
*/

[--小F--]

如果是3 就改成3

[drysea]

小F这个。。。如果数据量很大的话，在性能上也许会有问题
in这个。。。大数据量上性能比较差

[sqlserver5]

--可以修改成这样的。试试看
SELECT * 
from
 @table t
WHERE
exists(select bundle from @table WHERE bundle=t.bundle group by bundle having count(1)>=2)
and
exists(select 1  from @table where bundle=t.bundle and section<>t.section)

[水族杰纶]

DECLARE @table TABLE 
(id INT PRIMARY KEY,bundle NVARCHAR(20),section NVARCHAR(20)) 
INSERT INTO @table VALUES(1,'001','A') 
INSERT INTO @table VALUES(2,'001','A') 
INSERT INTO @table VALUES(3,'002','A') 
INSERT INTO @table VALUES(4,'002','B') 
INSERT INTO @table VALUES(5,'002','B') 
INSERT INTO @table VALUES(6,'003','B') 
INSERT INTO @table VALUES(7,'003','A') 
INSERT INTO @table VALUES(8,'003','C') 
INSERT INTO @table VALUES(9,'004','A') 
INSERT INTO @table VALUES(10,'005','D') 
INSERT INTO @table VALUES(11,'005','A') 
select *
from @table t ,(select bundle from @table group by bundle having count(*)>=3)s
where t.bundle=s.bundle
and  exists(select 1 from @table where bundle=t.bundle and section!=t.section) 
/*
id          bundle               section              bundle
----------- -------------------- -------------------- --------------------
3           002                  A                    002
4           002                  B                    002
5           002                  B                    002
6           003                  B                    003
7           003                  A                    003
8           003                  C                    003

(6 個資料列受到影響)
*/

[liuyileneal]

学习了

[drysea]

刚刚有些事情，现在贴上来，基本跟上面的一样
关于索引，要根据机器的具体配置来定
索引最优当然是把查询所需的差异性最大的字段放在最前面，这样效率最高
但是，这样就增加了索引维护的成本，如果数据更新频繁，索引要频繁维护，可能有时得不偿失

简单的方案，也是懒人方案（拷贝联机丛书的，嘿嘿）

SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)

这里面显示的是数据库里面所有的缺失的非聚集索引的信息，每次查询的时候数据库会帮助优化，但是这会损耗查询成本，所以可以根据这个信息自己根据系统的环境及查询的需要，建立非聚集索引（详情参见联机丛书哦~）

DECLARE @n INT  ---传入n的数值
SET @n = 3
;WITH test AS(
		SELECT bundle,COUNT(1) AS num
			FROM @table 
		GROUP BY bundle 
		HAVING COUNT(1) = @n
)SELECT * FROM @table a JOIN test b ON a.bundle = b.bundle
WHERE EXISTS(SELECT 1 FROM @table c WHERE c.bundle = a.bundle AND c.section <>a.section)