34,575
社区成员
发帖
与我相关
我的任务
分享
/*
有一个类似顶、赞功能的记录表,
id,contentid,userid,goodorbad,crtime
字段说明:id是自增列。contentid,userid是int类型,外键。goodorbad是int,只存0或1,1代表好,0代表不好。crtime是指添加日期,是datetime类型(2014-08-18 14:17:354)
请分别输出最近8小时、今天、24小时以内、本周、最近30天、本月,支持或反对最多的记录,倒序排列。
求以上SQL语句,主要是这个日期范围该怎么计算?
*/
-- 环境
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='good')
DROP TABLE dbo.good
go
CREATE TABLE dbo.good(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,contentid INT NOT NULL,userid INT NOT NULL ,
goodorbad bit NOT null,crtime datetime)
go
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 8,1,1,'2014-7-31 20:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 8,1,1,'2014-8-1 13:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 9,1,0,'2014-8-1 18:09:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 10,1,1,'2014-8-8 20:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 10,1,1,'2014-8-11 16:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 8,1,1,'2014-8-15 20:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 10,1,1,'2014-8-18 10:00:00'
INSERT INTO dbo.good(contentid,userid,goodorbad,crtime)
SELECT 8,1,0,'2014-8-18 11:00:00'
GO
--@goodorbad = 1代表好,@goodorbad =0代表不好
DECLARE @goodorbad BIT
SET @goodorbad = 1
--8小时
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE crtime BETWEEN DATEADD(HOUR,-8,GETDATE()) AND GETDATE() AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--24小时
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE crtime BETWEEN DATEADD(HOUR,-24,GETDATE()) AND GETDATE() AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--今天
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE DATEPART(DAY,crtime) =DATEPART(DAY,GETDATE())
AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--本周
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE DATEPART(WEEK,crtime) =DATEPART(WEEK,GETDATE())
AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--最近30天
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE crtime BETWEEN DATEADD(DAY,-30,GETDATE()) AND GETDATE() AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--本月
SELECT TOP 1 contentid,COUNT(1)count
FROM dbo.good
WHERE DATEPART(MONTH,crtime) =DATEPART(MONTH,GETDATE())
AND goodorbad = @goodorbad
GROUP BY contentid,goodorbad
ORDER BY count DESC
--最近8小时
WHERE crtime >= DateAdd(hour,-8,GetDate())
--今天
WHERE crtime >= Convert(datetime,
Convert(varchar(10),
GetDate(),
120),
120)
--最近24小时以内
WHERE crtime >= DateAdd(hour,-24,GetDate())
--本周
WHERE DatePart(week,crtime) = DatePart(week,GetDate())
--本月
WHERE crtime >= Convert(datetime,
Convert(varchar(7),
GetDate(),
120)+'-01',
120)