解决方案 »
- 大家帮忙看一下这条语句哪里有错误!
- SQL 中 OR ??
- 求解:创建视图,如何用一个虚拟字段作为连接
- 一个小小的SQL2005数据库问题
- 没有遇见过的问题!!请高手解决 !
- 怎样获取MySQL数据库中表设计中的值
- sql库销比查询
- 在ASP中如何与SQL server 2000数据库连接!!!!---------->>>>>>>>
- VFP的group by与Sql的group by的区别?
- MSSQL的触发器问题
- 求教!如何查询存储过程处理过程中出错的列??
- 连接接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 无法 INSERT INTO 表 "[Micro
(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 行受影响)
*/
in这个大数据量上性能比较差
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)
(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 個資料列受到影響)
*/
关于索引,要根据机器的具体配置来定
索引最优当然是把查询所需的差异性最大的字段放在最前面,这样效率最高
但是,这样就增加了索引维护的成本,如果数据更新频繁,索引要频繁维护,可能有时得不偿失简单的方案,也是懒人方案(拷贝联机丛书的,嘿嘿)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)