如:
Select convert(varchar(10),AddTime,120) as AddTime, COUNT(*) AS ClickTime From TableName Where DateDiff(dd, AddTime, GetDate()) <= 7
AND (UserID = @UserID)
group by convert(varchar(10),AddTime,120)上面的sql显示7天前的记录,但有些日期没有记录就不显示出来,我希望7天都能显示出来,没有记录的显示0,请问可以实现吗?
Select convert(varchar(10),AddTime,120) as AddTime, COUNT(*) AS ClickTime From TableName Where DateDiff(dd, AddTime, GetDate()) <= 7
AND (UserID = @UserID)
group by convert(varchar(10),AddTime,120)上面的sql显示7天前的记录,但有些日期没有记录就不显示出来,我希望7天都能显示出来,没有记录的显示0,请问可以实现吗?
解决方案 »
- 装SQLServer2005许多回却不见开始菜单有快捷方式
- 如何表中某字段中相同值,怎么把另外字段分别取出啊
- 请问SQL-SERVER是不是自动添加锁的?
- 问个 SQL 语句
- MSSQL QPS,TPS和IOPS是怎么得到的
- 初学者请教:我想要某个表里的所有字段名及其数据类型等信息,用Sql语句怎么写???
- 求同一表内的合并语句
- sql server2000是不是不能在winxp 专业版下安装??
- 怎么样才能不让别人删除表中的某几行记录呢??
- 向大家请教:两位数的编号,最节省资源的数据类型该采用什么?谢谢!!!
- 求c++操作sql server 2000数据库的增删改查 源码 可运行
- Implicit conversion of varchar value to varchar cannot be performed because the
AND (UserID = @UserID)
group by convert(varchar(10),isnull(AddTime,'1900-01-01'),120)
DECLARE @MinDate DATETIME,@MaxDate DATETIME
SELECT @MinDate=CONVERT(VARCHAR(10),MIN(AddTime),120),@MaxDate=CONVERT(VARCHAR(10),MAX(AddTime),120) From TableName Where DateDiff(dd, AddTime, GetDate()) <= 7 AND (UserID = @UserID)
;WITH DateDay
AS
(
SELECT @MinDate AS DateDay ,UserID = @UserID
UNION ALL
SELECT DateDay+1,UserID FROM DateDay WHERE DateDay<@MaxDate
)
Select
b.DateDay as AddTime,
COUNT(*) AS ClickTime
From TableName AS a
RIGHT JOIN DateDay AS b ON convert(varchar(10),a.AddTime,120)=b.DateDay AND a.UserID=b.UserID
group by b.UserID,b.DateDay
OPTION(MAXRECURSION 0)
CREATE TABLE #TableName(
AddTime DATETIME,
BH VARCHAR(20)
)INSERT INTO #TableName VALUES ('2011-01-10','1')
INSERT INTO #TableName VALUES ('2011-01-11','2')
INSERT INTO #TableName VALUES ('2011-01-12','3')SELECT * FROM #TableNameSELECT A.ADDTIME,COUNT(#TableName.BH) AS ClickTime FROM
(SELECT CONVERT(VARCHAR(10), DATEADD(DAY, NUMBER, GETDATE()-7), 120) AS ADDTIME
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P' AND
NUMBER<7) A LEFT JOIN #TableName ON A.ADDTIME = #TableName.AddTime
GROUP BY A.ADDTIME
AddTime ClickTime
2010-1-16 0
2010-1-15 2
2010-1-14 0
2010-1-13 0
2010-1-12 0
2010-1-11 0
2010-1-10 0
最近的7天,没有记录的ClickTime显示0