SELECT 中介编码, ID = min(ID) FROM Temp1 GROUP BY 中介编码
UNION ALL
SELECT a.中介编码, ID = min(a.ID) FROM Temp1 a, (SELECT 中介编码, ID = min(ID) FROM Temp1 GROUP BY 中介编码) b WHERE a.中介编码 = b.中介编码 and a.ID <> b.ID GROUP BY a.中介编码
ORDER BY 中介编码
UNION ALL
SELECT a.中介编码, ID = min(a.ID) FROM Temp1 a, (SELECT 中介编码, ID = min(ID) FROM Temp1 GROUP BY 中介编码) b WHERE a.中介编码 = b.中介编码 and a.ID <> b.ID GROUP BY a.中介编码
ORDER BY 中介编码
from 表 a
where ID in (select top 2 ID from 表 where 中介编码=a.中介编码)
from table a
where ID in
(select max(ID) from table group by 中介编码
union
select min(ID) from table Group by 中介编码)
where id=(select min(id) from 表 where 中介编码=a.中介编码)
or id=(select min(id) from 表 b where b.中介编码=a.中介编码
and b.id<>(select min(id) from 表 where 中介编码=a.中介编码))
order by 中介编码
(根据haipingma(ToJava)的改写)select 中介编码,ID
from 表 a where ID in
(select min(ID) from 表 group by 中介编码
union all
select min(ID) from 表 where id not in(select min(ID) from 表 group by 中介编码 )
Group by 中介编码 )
order by 中介编码
三种语句的执行计划比较:总共花费时间13秒(1)查询成本:99.95%
select 中介编码,ID from 表 a
where ID in (select top 2 ID from 表 where 中介编码=a.中介编码 order by id)
order by 中介编码(2)查询成本:0.03%
select 中介编码,ID from 表 a
where id=(select min(id) from 表 where 中介编码=a.中介编码)
or id=(select min(id) from 表 b where b.中介编码=a.中介编码
and b.id<>(select min(id) from 表 where 中介编码=a.中介编码))
order by 中介编码(3)查询成本:0.02%
select 中介编码,ID
from 表 a where ID in
(select min(ID) from 表 group by 中介编码
union all
select min(ID) from 表 where id not in(select min(ID) from 表 group by 中介编码 )
Group by 中介编码 )
order by 中介编码
数据:10
索引:无
declare @t datetime
/*测试1:2193ms*/
set @t = getdate()
SELECT Test1, Test2 = min(Test2) FROM Test GROUP BY Test1
UNION ALL
SELECT a.Test1, Test2 = min(a.Test2) FROM Test a, (SELECT Test1, Test2 = min(Test2) FROM Test GROUP BY Test1) b WHERE a.Test1 = b.Test1 and a.Test2 <> b.Test2 GROUP BY a.Test1
ORDER BY Test1
select datediff(ms, @t, getdate())
/*测试2:6240ms*/
set @t = getdate()
select Test1,Test2
from Test a where Test2 in
(select min(Test2) from Test group by Test1
union all
select min(Test2) from Test where Test2 not in(select min(Test2) from Test group by Test1 )
Group by Test1 )
order by Test1
select datediff(ms, @t, getdate())
/*测试3:8030ms*/
set @t = getdate()
select Test1,Test2 from Test a
where Test2=(select min(Test2) from Test where Test1=a.Test1)
or Test2=(select min(Test2) from Test b where b.Test1=a.Test1
and b.Test2<>(select min(Test2) from Test where Test1=a.Test1))
order by Test1
select datediff(ms, @t, getdate())
/*测试4:19266ms*/
set @t = getdate()
select Test1,Test2 from Test a
where Test2 in (select top 2 Test2 from Test where Test1=a.Test1 order by Test2)
order by Test1
select datediff(ms, @t, getdate())
再测试:Test(Test1 int, Test2 varchar(8))
数据:100000
索引:无
*//*测试1:4196ms*/
declare @t datetime
set @t = getdate()
SELECT Test1, Test2 = min(Test2) FROM Test GROUP BY Test1
UNION ALL
SELECT a.Test1, Test2 = min(a.Test2) FROM Test a, (SELECT Test1, Test2 = min(Test2) FROM Test GROUP BY Test1) b WHERE a.Test1 = b.Test1 and a.Test2 <> b.Test2 GROUP BY a.Test1
ORDER BY Test1
select datediff(ms, @t, getdate())/*测试2:6660ms*/
set @t = getdate()
select Test1,Test2
from Test a where Test2 in
(select min(Test2) from Test group by Test1
union all
select min(Test2) from Test where Test2 not in(select min(Test2) from Test group by Test1 )
Group by Test1 )
order by Test1
select datediff(ms, @t, getdate())/*测试3:10503ms*/
set @t = getdate()
select Test1,Test2 from Test a
where Test2=(select min(Test2) from Test where Test1=a.Test1)
or Test2=(select min(Test2) from Test b where b.Test1=a.Test1
and b.Test2<>(select min(Test2) from Test where Test1=a.Test1))
order by Test1
select datediff(ms, @t, getdate())/*测试4:26790ms*/
set @t = getdate()
select Test1,Test2 from Test a
where Test2 in (select top 2 Test2 from Test where Test1=a.Test1 order by Test2)
order by Test1
select datediff(ms, @t, getdate())
1:4196ms
2:6660ms
3:10503ms
4:26790ms普通索引 IX_Test_Test1
1:5346ms
2:7960ms
3:13240ms
4:70733ms再加普通索引 IX_Test_Test2
1:5900ms
2:10263ms
3:13210ms
4:71063ms删除 IX_Test_Test2, 添加主键 PK_Test_Test2
1:5960ms
2:5980ms
3:5046ms
4:4146ms删除 IX_Test_Test1
.
.
.
.
.
.
晕晕, 测试半个小时都没结果出来, 我中止了从以上测试可以知道, 楼主为什么那么慢: ID为主键, 中介编码普通索引的情况下, 各种方法速度差不多, 但还是3楼的最快. 但如果ID为主键, 中介编码没有索引的情况下, 超慢!!.
我想hudan(中文昵称)也不用测试多条记录的情况了, 关键是索引的优化问题.
在主键为ID的情况下, 三楼huwgao(小楼听雨) 的方法,要比其他几种方法稍慢一点,
但其他几种方法想取前10条,我都不知道怎么写(太复杂)测试了一下3楼的方法(ID是主键,数据8万行):
取前2 条,花费 1.5m
取前20条,花费 5.6m
取前50条,花费 20.8m三楼的方法代码简洁,思路清晰,应为首选(必须要有索引)
CREATE INDEX [索引名] ON [dbo].[表名]([字段] DESC ) ON [PRIMARY]
--
在企业管理器中选择要创建索引的表,右击该表,然后从快捷菜单中选择"索引/键"命令。
-或-为要创建索引的表打开表设计器,在表设计器中右击,然后从快捷菜单中选择"索引/键"命令。选择"新建"命令。"选定的索引"框显示系统分配给新索引的名称。
在"列名"下选择要创建索引的列。可以选择多达 16 列。为获得最佳性能,最好只选择一列或两列。对所选的每一列,可指出索引是按升序还是降序组织列值。
为索引指定任何其它需要的设置,然后单击"确定"按钮。