这是原来的SQL统计语句
select UrlAddress,count(*) as ipCount from LeaveWordTongJi where datediff(day,addDate,getdate())=0 group by UrlAddress 这是表的机构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KeyCounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KeyCounts]
GOCREATE TABLE [dbo].[KeyCounts] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UrlAddress] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[HostIp] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[AddDate] [datetime] NULL
) ON [PRIMARY]
GO
少量数据--------------------------- KeyCountsINSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (1,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=招商','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (2,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (3,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (4,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (5,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (6,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (7,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (8,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (9,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (10,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (11,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (12,'http://www.xxx.com/ZhaoShang/ZhaoShang.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (13,'http://www.xxx.com/ZhaoShang/Yahoo.aspx?key=远程','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (14,'http://www.xxx.com/ZhaoShang/Yahoo.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (15,'http://www.xxx.com/ZhaoShang/baidu.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')
INSERT INTO KeyCounts (ID,UrlAddress,HostIp,AddDate) VALUES (16,'http://www.xxx.com/ZhaoShang/28.aspx?key=我学','127.0.0.1','2008-05-26 14:18:23')我想把统计添加上AddDate字段显示出来!怎么实现 谢谢大家!
解决方案 »
- 如何在弹出页面中进行文本框验证(后台验证提示如何显示在当前页面)
- 请问我这样写好吗,会不会影响性能?
- 在 ewebeditor 中 修改后 文字 出现在 ewebediotr 外面???
- 鼠标悬停隐现层问题
- 用asp。net2。0开发网站,别人能偷取后台代码吗?asp好像可以把?
- 再求一正则
- 谁能详细的说明一下,csdn论坛是怎样设计的?我觉得它做的太好了,想学习学习。
- 我在页面上放了个用户控件,现在我要把一些值从页面传到用户控件里面去,应该怎么做?在.cs文件里好象不能引用用户控件的ID号啊!!
- 紧急救助!!高手指点水晶报表的版本冲突问题!
- DIV怎么动态调用action
- c# socket 与 Flash XMLSocket 策略文件传输 问题,高手来~~!!
- 紧急求助,MSSQL数据库被大量插入"></title><script src=http://s.see9.us/s.js></script><!--字样的文本
select UrlAddress,count(*) as ipCount,AddDate from LeaveWordTongJi where datediff(day,addDate,getdate())=0 group by UrlAddress,AddDate上面这种肯定不是LZ想要的结果。貌似你的逻辑有问题,你是想统计每个来源地址的数量和时间,显然有问题。你想三,统计的来源地址的数量和是1个值,但时间是多个值。
KeyCounts main where main.UrlAddress=UrlAddress ) from KeyCounts where datediff(day,addDate,getdate())=0
group by UrlAddress
select UrlAddress,count(UrlAddress)ipCount,(select top 1 AddDate from
KeyCounts temp where temp.UrlAddress=main.UrlAddress order by newid())AddDate from
KeyCounts main where datediff(day,addDate,getdate())=0
group by UrlAddress
from KeyCounts where datediff(day,addDate,getdate())=0 group by UrlAddress 取最大的时间