sql语句问题,请高手帮忙看看

wangjinchang 2009-11-18 02:52:20
表IP_Liulang如下:
id IP_address IP_num IP_datetime
246 127.0.0.2 1 2009-11-15
247 127.0.0.1 1 2009-11-15
248 127.0.0.0 10 2009-11-15
249 127.0.0.1 1 2009-11-16
250 127.0.0.9 2 2009-11-16
251 127.0.0.7 8 2009-11-17
252 127.0.0.1 1 2009-11-17

请问,如何得到同一天字段有几条数据(就是相同的时间出现过几次)和同一天字段IP_num的和,并且时间有大到小排列。最后查询应该得到的表结构如下:
IP_datetime x y
2009-11-17 2 9
2009-11-16 2 3
2009-11-15 3 12
说明:x是同一天出现的次数。y是同一天IP_num的和。时间列是按倒序排列的。
请高手指点。


...全文
79 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
qiqi860819 2009-11-18
  • 打赏
  • 举报
回复

create table liu_lang
(
id varchar(8),
Ip_Address varchar(16),
Ip_Num int,
Ip_DateTime varchar(16)
)
go

insert into liu_lang
select '246', '127.0.0.2', 1, '2009-11-15' union all
select '247', '127.0.0.1', 1, '2009-11-15' union all
select '248', '127.0.0.0', 10, '2009-11-15' union all
select '249', '127.0.0.1', 1, '2009-11-16' union all
select '250', '127.0.0.9', 2, '2009-11-16' union all
select '251', '127.0.0.7', 8, '2009-11-17' union all
select '252', '127.0.0.1', 1, '2009-11-17'
go

select Ip_DateTime,Count(Ip_Num),sum(Ip_Num) from liu_lang group by Ip_DateTime order by Ip_DateTime desc
Flyingdragon168 2009-11-18
  • 打赏
  • 举报
回复
SELECT A.IP_datetime,A.RecCount As x,B.IP_Count As Y
FROM (SELECT COUNT(IP_Address),IP_datetime As RecCount FROM IP_Liulang GROUP BY IP_datetime) A INNER JOIN
(SELECT Ip_Datetime,SUM(IP_num) As Ip_Count FROM IP_Liulang GROUP BY Ip_Datetime ) B ON A.Ip_Datetime=B.IP_datetime
ORDER BY A.IP_datetime DESC
jenny0810 2009-11-18
  • 打赏
  • 举报
回复

--测试数据
if object_id('uu') is not null drop table uu
go
create table uu(id int,IP_address varchar(20),IP_num int,IP_datetime smalldatetime)
insert uu
select 246,'127.0.0.2', 1, '2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1, '2009-11-16' union all
select 250,'127.0.0.9', 2, '2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'


--查询
select IP_datetime ,count(*) as x,sum(IP_num) from uu group by IP_datetime order by IP_datetime desc

icelovey 2009-11-18
  • 打赏
  • 举报
回复

declare @TB1 table([id] int,[IP_address] varchar(9),[IP_num] int,[IP_datetime] datetime)
insert @TB1
select 246,'127.0.0.2',1,'2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1,'2009-11-16' union all
select 250,'127.0.0.9',2,'2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'

select distinct [IP_datetime], COUNT([IP_address]) OVER (PARTITION BY [IP_datetime]) as X
, sum([IP_num]) OVER (PARTITION BY [IP_datetime]) as Y
from @TB1

/*
IP_datetime X Y
----------------------- ----------- -----------
2009-11-15 00:00:00.000 3 12
2009-11-16 00:00:00.000 2 3
2009-11-17 00:00:00.000 2 9

(3 row(s) affected)
快乐_石头 2009-11-18
  • 打赏
  • 举报
回复
--> Title  : Generating test data [tb]
--> Author :
--> Date : 2009-11-18 07:55:32
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,IP_address nvarchar(18),IP_num int,IP_datetime datetime)
insert into [tb]
select 246,'127.0.0.2',1,'2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1,'2009-11-16' union all
select 250,'127.0.0.9',2,'2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'
select IP_datetime,
count(*)X,
sum(IP_num)Y
from tb
group by IP_datetime
/*
IP_datetime X Y
----------------------- ----------- -----------
2009-11-15 00:00:00.000 3 12
2009-11-16 00:00:00.000 2 3
2009-11-17 00:00:00.000 2 9

(3 個資料列受到影響)
*/
萨乌森桑尼 2009-11-18
  • 打赏
  • 举报
回复

SELECT IP_DATETIME,COUNT(1) AS X,SUM(IP_NUM) AS Y
FROM IP_LIULANG
GROUP BY IP_DATETIME
ORDER BY IP_DATETIME DESC
wangwei4348 2009-11-18
  • 打赏
  • 举报
回复
用dadatiff()

那个排序用order by ... desc 聚合函数就行!

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧