首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

请问一条最优的SQL语句

2012-03-19 
请教一条最优的SQL语句SQL codeCREATE TABLE [dbo].[PRE_Follow]([ID] [int] IDENTITY(1,1) NOT NULL,[Use

请教一条最优的SQL语句

SQL code
CREATE TABLE [dbo].[PRE_Follow](    [ID] [int] IDENTITY(1,1) NOT NULL,    [UserNumber] [varchar](50) NOT NULL,    [FollowState] [int] NOT NULL CONSTRAINT [DF_EZX_Follow_FollowState]  DEFAULT ((0)),    [FollowUserName] [nvarchar](500) NOT NULL,    [FollowContent] [ntext] NULL,    [FollowTime] [smalldatetime] NULL,    [AddTime] [smalldatetime] NOT NULL CONSTRAINT [DF_EZX_Follow_AddTime]  DEFAULT (getdate()), CONSTRAINT [PK_PRE_Follow] PRIMARY KEY CLUSTERED (    [ID] ASC,    [UserNumber] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO


这个是我写的
SQL code
Select tmp.UserNumber From PRE_Follow Inner Join(Select UserNumber,Max(FollowTime) as TopFollowTime From PRE_Follow Where FollowState=1 Group By UserNumber)as tmp on PRE_Follow.UserNumber=tmp.UserNumber And PRE_Follow.FollowTime=tmp.TopFollowTime And FollowTime<=Convert(DateTime,GetDate())



需求就是查询出 这张表PRE_Follow中的FollowTime字段的最大时间小于今天 的所有用户号

[解决办法]
SQL code
select *from tb twhere not exists (select 1 from tb where 用户号=t.用户号 and followtime>t.followtime)    and datediff(dd,getdate(),followtime)<0
[解决办法]
SQL code
select * from tb twhere not exists (select 1 from tb where 用户号=t.用户号 and followtime>t.followtime and datediff(dd,getdate(),followtime)<0)and datediff(dd,getdate(),t.followtime)<0
[解决办法]
SQL code
select t.ID from (select ID,max(FollowTime) maxFollowTime from PRE_Follow group by ID) twhere datediff(d,t.maxFollowTime getdate())>=1
[解决办法]
个人觉得你自己的语句
应该还行
主要看有没有走索引

[解决办法]
探讨

个人觉得你自己的语句
应该还行
主要看有没有走索引

[解决办法]
没什么好优化的。
[解决办法]
这要根据你表数据的大小和分布情况

那个表数据特别多 
 PRE_Follow的UserNumber 可以索引一下
[解决办法]
SQL code
--trycreate index idx_usnum_fton [dbo].[PRE_Follow](UserNumber,FollowTime) 

热点排行