select top 12 a.* From news a Where a.id in(select top 1 id from news where classid=a.classid and tuijian=1 order by adddate desc)if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[news]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[news]
GOCREATE TABLE [dbo].[news] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[TitleStyle] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassID] [int] NULL ,
[Author] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[InfoStyle] [int] NULL ,
[TitlePic] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[AddDate] [datetime] NULL ,
[Content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[UploadFile] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[Tuijian] [bit] NULL ,
[Show] [bit] NULL ,
[Hits] [int] NULL ,
[newsaddr] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
drop table [dbo].[news]
GOCREATE TABLE [dbo].[news] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[TitleStyle] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[ClassID] [int] NULL ,
[Author] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[InfoStyle] [int] NULL ,
[TitlePic] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[AddDate] [datetime] NULL ,
[Content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[UploadFile] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[Tuijian] [bit] NULL ,
[Show] [bit] NULL ,
[Hits] [int] NULL ,
[newsaddr] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
select top 12 *
From news
Where tuijian=1
and not exists(select * from news a where a.classid=news.classid and a.tuijian=1 and a.adddate>news.adddate)
Select TOP 12 A.*
From news A
Inner Join (Select classid,Max(adddate) As adddate From news Where tuijian=1 Group By classid) B
On A.classid=B.classid And A.adddate=B.adddate
如果都是顺序加的话,a.adddate>news.adddate 和 a.id>news.id 是一样的
再配合classid,tuijian上的索引效率会更高(不经常用没必要乱建)
select top 12 *
From news
Where tuijian=1
and not exists(select * from news a where a.classid=news.classid and a.tuijian=1 and a.id>news.id)
select top 12 a.* From news a Where a.id=(select top 1 id from news where classid=a.classid and tuijian=1 order by adddate desc)关键在于建立索引
应该建立(classid,tuijian)的组合索引
(classid,tuijian,adddate)
inner join (
select max(id) as id,classid from news where tuijian=1 group by classid )b
on a.id=b.Id
楼主的ID为自动编号,所以日期最大的编号也越大,可以这样啊-----------------------------
這個是不一定的,日期是可以更新的。
select top 12 a.* From news a Where a.id=(select top 1 id from news where classid=a.classid and tuijian=1 order by adddate desc)
优化点:
1--确定要查出所有的列么,否则指定列名
2--如果你的外表数据大,而内表小,就使用IN,反之用EXISTS方法
3--不管什么连接,在连接的字段上加索引
4--写出多种等效语句,查看执行计划
......
索引加了*的效率不是瓶颈谢谢大家
1. 去掉 TOP , 看看效率2. 试试语句改变
将id设置为主键
建立复合索引 classid, adddateSELECT A.*
FROM news A,(
SELECT id = MAX(A.ID)
FROM news A,(
SELECT TOP 12
classid, adddate = MAX(adddate)
FROM news
GROUP BY classid
)B
WHERE A.classid = B.classid
AND A.adddate = B.adddate
GROUP BY A.classid
)B
WHERE A.id = B.id
--有一点区别是我的表中有null值,时间有重复(这样各个查询结果会有出入,但数量少,影响不大)
--前一个是服务器响应时间,后面是客户端处理时间 我做了3组
--1原 2052 549/2157 629/2113 555
select a.* From customer a Where a.is_id=(select top 1 is_id from customer where c_code=a.c_code and is_use=0 order by k_time desc)
--2用exists改写 321 104/323 104/314 104
select *
From customer
Where is_use=0
and not exists(select * from customer a where a.c_code=customer.c_code and a.is_use=0 and a.k_time>customer.k_time)
--3联合查询 237 162/258 168/287 165
Select A.*
From customer A
Inner Join (Select c_code,Max(k_time) As k_time From customer Where is_use=0 Group By c_code) B
On A.c_code=B.c_code And A.k_time=B.k_time
--4邹建 157 286/152 253/155 250 (邹老大原文少个条件'tuijian=1' 我给加上了)
SELECT A.*
FROM customer A,(
SELECT is_id = MAX(A.is_ID)
FROM customer A,(
SELECT c_code, k_time = MAX(k_time)
FROM customer where is_use=0
GROUP BY c_code
)B
WHERE A.c_code = B.c_code
AND A.k_time = B.k_time
GROUP BY A.c_code
)B
WHERE A.is_id = B.is_id
--从这个结果来看 4>3>2>1 以4邹建的方法效果最好(服务器响应时间最短),顶!