select * from ad_hits where ad_id = '131' and year(hits_date) = '2006' and
month(hits_date) = '6' order by hits_date
======================================================
用上面的搜索语句,得到下面的结果..
131 2006-06-01 00:00:00 1
131 2006-06-02 00:00:00 2
131 2006-06-03 00:00:00 1
131 2006-06-05 00:00:00 2
131 2006-06-06 00:00:00 5
131 2006-06-07 00:00:00 1
131 2006-06-10 00:00:00 1
131 2006-06-14 00:00:00 2
==================================
我想得到这样的结果..
131 2006-06-01 00:00:00 1
131 2006-06-02 00:00:00 2
131 2006-06-03 00:00:00 1
131 2006-06-04 00:00:00 0
131 2006-06-05 00:00:00 2
131 2006-06-06 00:00:00 5
131 2006-06-07 00:00:00 1
131 2006-06-08 00:00:00 0
131 2006-06-09 00:00:00 0
131 2006-06-10 00:00:00 1
131 2006-06-11 00:00:00 0
131 2006-06-12 00:00:00 0
131 2006-06-13 00:00:00 0
131 2006-06-14 00:00:00 2就是,如果当天的点击数为O则自动列出为0记录...
month(hits_date) = '6' order by hits_date
======================================================
用上面的搜索语句,得到下面的结果..
131 2006-06-01 00:00:00 1
131 2006-06-02 00:00:00 2
131 2006-06-03 00:00:00 1
131 2006-06-05 00:00:00 2
131 2006-06-06 00:00:00 5
131 2006-06-07 00:00:00 1
131 2006-06-10 00:00:00 1
131 2006-06-14 00:00:00 2
==================================
我想得到这样的结果..
131 2006-06-01 00:00:00 1
131 2006-06-02 00:00:00 2
131 2006-06-03 00:00:00 1
131 2006-06-04 00:00:00 0
131 2006-06-05 00:00:00 2
131 2006-06-06 00:00:00 5
131 2006-06-07 00:00:00 1
131 2006-06-08 00:00:00 0
131 2006-06-09 00:00:00 0
131 2006-06-10 00:00:00 1
131 2006-06-11 00:00:00 0
131 2006-06-12 00:00:00 0
131 2006-06-13 00:00:00 0
131 2006-06-14 00:00:00 2就是,如果当天的点击数为O则自动列出为0记录...
解决方案 »
- SQL2008写一个导出表中数据的脚本
- 多表联合删除,急~~!!
- sql server 2000安装出错
- 用.net 开发软件,GIS监控系统,一分钟有6万的数据量,怎么处理查询数据才行啊!?
- 求大神解决sqlcmd执行sql文件忽略错误的问题
- PB的专用接口访问两个sql server 服务器,访问协议一致性问题。
- 数据库建模工具中powerdesign 是不是最好的?还有哪些常用数据库建模工具?和powerdesign 比较怎么样?
- 一个SQL查询,数据由列转行的问题
- SOS,急救啊。
- 我忘了更新记录的语法了!谁告诉我一下??
- 请教:如何使用变量(1)变量:@a1=100,@a2=200,...(2)变量:@b等于'@a1'或'@a2'或、、、(3)求变量:@c等于@b所表示的@a1或@a2或、、、之值
- 非常郁闷中,一个关于触发器的问题
DECLARE @tb TABLE(ID INT,YourDate DATETIME,NUM INT)
INSERT @tb
SELECT
131, '2006-06-01 00:00:00' ,1
UNION SELECT
131, '2006-06-02 00:00:00' ,2
UNION SELECT
131, '2006-06-03 00:00:00' ,1
UNION SELECT
131, '2006-06-05 00:00:00' ,2
UNION SELECT
131, '2006-06-06 00:00:00' ,5
UNION SELECT
131, '2006-06-07 00:00:00' ,1
UNION SELECT
131, '2006-06-10 00:00:00' ,1
UNION SELECT
131, '2006-06-14 00:00:00' ,2
--生成临时数据
DECLARE @MinDate Datetime,@MaxDate Datetime
SELECT @MinDate=MIN(YourDate),@MaxDate=MAX(YourDate) FROM @tb
DECLARE @Tmp Table(Num1 INT,TmpDate DateTime,Num2 INT)
WHILE @MinDate<@MaxDate
BEGIN
INSERT @Tmp VALUES(131,DATEADD(dd,1,@MinDate),0)
SET @MinDate=DATEADD(dd,1,@MinDate)
END--显示所需结果
SELECT * FROM @tb
UNION
SELECT * FROM @Tmp WHERE TmpDate NOT IN(SELECT YourDate FROM @tb)
(ad_id Varchar(3),
hits_date DateTime,
hits Int)
Insert ad_hits Select '131', '2006-06-01 00:00:00', 1
Union All Select'131', '2006-06-02 00:00:00', 2
Union All Select'131', '2006-06-03 00:00:00', 1
Union All Select'131', '2006-06-05 00:00:00', 2
Union All Select'131', '2006-06-06 00:00:00', 5
Union All Select'131', '2006-06-07 00:00:00', 1
Union All Select'131', '2006-06-10 00:00:00', 1
Union All Select'131', '2006-06-14 00:00:00', 2
GO
Select TOP 31 ID=Identity(Int,1,1) Into # From syscolumns,SysObjectsSelect Distinct
IsNull(A.ad_id,'131') As ad_id,
B.hits_date,
IsNull(hits,0) As hits
From
(Select DateAdd(dd,ID-1,(Select Min(hits_date) From ad_hits)) As hits_date From #) B
Left Join
ad_hits A
On A.hits_date=B.hits_date
And ad_id = '131' and year(A.hits_date) = '2006' and month(A.hits_date) = '6'
Where B.hits_date<=(Select Max(hits_date) From ad_hits)
order by B.hits_date
GO
Drop Table ad_hits,#
--Result
/*
ad_id hits_date hits
131 2006-06-01 00:00:00.000 1
131 2006-06-02 00:00:00.000 2
131 2006-06-03 00:00:00.000 1
131 2006-06-04 00:00:00.000 0
131 2006-06-05 00:00:00.000 2
131 2006-06-06 00:00:00.000 5
131 2006-06-07 00:00:00.000 1
131 2006-06-08 00:00:00.000 0
131 2006-06-09 00:00:00.000 0
131 2006-06-10 00:00:00.000 1
131 2006-06-11 00:00:00.000 0
131 2006-06-12 00:00:00.000 0
131 2006-06-13 00:00:00.000 0
131 2006-06-14 00:00:00.000 2
*/