问一个,按日期统计点击数的问题...

2006-06-14 05:17:07 312 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

esp32s3 spi camera gc6133调试 gc6133 spi只有master模式，同时需要esp32提供主时钟，需要有主时钟然后才能通过i2c进行初始化.测试了几种方式，记录如下。阅读详情

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记录...

...全文

NET 实战：30 天搞定企业级 Web 订餐系统（附完整源码 + 数据库设计）订餐流程：浏览菜品 → 选择菜品及数量 → 填写送餐地址 → 提交订单 → 数据库记录核心代码（绑定菜品列表）：vb' 绑定菜单表到DataGrid控件，展示菜品信息user id=sa;' 查询菜品名称、价格、口味Dim adpt1 As New SqlDataAdapter("select cdishid,vdishname 菜品名称,cdishrate 价格,vdishdescription 口味 from dish", conn)End Sub用户体验优化。阅读详情

fashman

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

fashman 2006-06-15

打赏
举报

谢谢弟兄们的帮忙..结贴...

losedxyz 2006-06-14

打赏
举报

mark

paoluo 2006-06-14

打赏
举报

Create Table ad_hits
(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,SysObjects

Select 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
*/

fcuandy 2006-06-14

打赏
举报

--生成测试数据
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)

fashman 2006-06-14

打赏
举报

那个记录数据库里面没有,能不能用sql语句给补上??

问一个,按日期统计点击数的问题...

相关推荐