sql语句的简单问题

[wangjinchang]

IP_Liulang表的数据如：
id ip IP_datetime IP_url
749 127.0.0.1 2009-11-17 11:48:01.967 Index.aspx
750 127.0.0.1 2009-11-17 11:48:03.403 ChatRoom
751 127.0.0.1 2009-11-17 11:48:04.543 Task
752 127.0.0.1 2009-11-17 11:48:11.780 VFClassroom
753 127.0.0.1 2009-11-17 11:48:12.717 Game
754 127.0.0.1 2009-11-17 11:48:13.717 Zsy
755 127.0.0.1 2009-11-17 11:48:14.733 CoursewareManagement
756 127.0.0.1 2009-11-17 11:48:16.000 friend
757 127.0.0.1 2009-11-17 11:48:18.687 http://localhost:1147/ttxs/user/RefLog.aspx
758 127.0.0.1 2009-11-17 11:48:19.047 http://localhost:1147/ttxs/user/ValidateCode.aspx
759 127.0.0.1 2009-11-17 11:48:20.750 http://localhost:1147/ttxs/index.aspx
760 127.0.0.1 2009-11-17 11:48:22.530 friend
761 127.0.0.1 2009-11-17 11:48:23.937 VFClassroom
762 127.0.0.1 2009-11-17 11:48:25.043 Task
763 127.0.0.1 2009-11-17 11:48:27.750 ChatRoom
764 127.0.0.1 2009-11-17 11:48:29.357 http://localhost:1147/ttxs/about/help.aspx
765 127.0.0.1 2009-11-17 11:48:33.187 CoursewareManagement
766 127.0.0.1 2009-11-17 11:48:34.403 http://localhost:1147/ttxs/index.aspx


我要实现的是，同一天内有多少个不同的ip访问过 一下指定的页面。（是不同IP在同一天的总数）
插入到如下表：
IP_page表结构如下：
id IP_datetime T_index T_chat T_task T_KL_room T_class_room T_game T_zsy T_Course T_friend
49 2009-11-17 1 2 2 0 2 1 1 2 2


其中：T_index 对应：Index.aspx
其中：T_chat 对应：ChatRoom
其中：T_task 对应：Task
其中：T_KL_room 对应：KL_room
其中：T_class_room 对应：VFClassroom
其中：T_game 对应：Game
其中：T_zsy 对应：Zsy
其中：T_Course 对应：CoursewareManagement
其中：T_friend对应：friend
请高手指点

[icelovey]

insert into IP_page(T_index,T_chat,T_task,T_KL_room,T_class_room,T_game,T_zsy,T_Course,T_friend,IP_datetime) 
select 
T_index=sum(case when IP_url='Index.aspx' then 1 else 0 end),
T_Chat=sum(case when IP_url='ChatRoom' then 1 else 0 end),
T_task=sum(case when IP_url='Task' then 1 else 0 end),
T_KL_room=sum(case when IP_url='KL_room' then 1 else 0 end),
T_class_room=sum(case when IP_url='Index.aspx' then 1 else 0 end),
T_game=sum(case when IP_url='Game' then 1 else 0 end),
T_Course=sum(case when IP_url='CoursewareManagement' then 1 else 0 end),
T_friend=sum(case when IP_url='friend' then 1 else 0 end),
convert(varchar(10),IP_datetime,120)
from  IP_Liulang group by convert(varchar(10),IP_datetime,120) 

[wangjinchang]

不对。

[Yang_]

select 
min(id) as id
,IP_datetime
,sum(case when IP_url = 'Index.aspx' then 1 else 0 end) as T_index
,sum(case when IP_url = 'ChatRoom' then 1 else 0 end) as T_chat
-- ...  其他类似
from IP_Liulang
group by IP_datetime

[Yang_]

[Quote=引用 2 楼 wangjinchang 的回复:]
不对。

[/Quote]
那你自己写对的出来吧

[icelovey]

规律都是一样的...照着这个方法一定可以写出来

[wangjinchang]

[Quote=引用 3 楼 yang_ 的回复:]
SQL codeselectmin(id)as id
,IP_datetime
,sum(casewhen IP_url='Index.aspx'then1else0end)as T_index
,sum(casewhen IP_url='ChatRoom'then1else0end)as T_chat-- ... 其他类似from IP_Liulanggroupby IP_datetime
[/Quote]

老大，是不同的IP有多少个？

[syw_java]

对，lz要的是除去重复访问的

[icelovey]

SQL2000测试通过了。你对着我上面的测试数据核对下看看是不是这样

declare @tb table([id] int,[ip] varchar(9),[IP_datetime] datetime,[IP_url] varchar(49))
insert @tb
select 749,'127.0.0.1','2009-11-17 11:48:01.967','Index.aspx' union all
select 749,'127.0.0.1','2009-11-17 11:48:01.967','Index.aspx' union all
select 749,'127.0.0.1','2009-11-18 11:48:01.967','Index.aspx' union all
select 749,'127.0.0.2','2009-11-18 11:48:01.967','Index.aspx' union all
select 749,'127.0.0.2','2009-11-18 11:48:01.967','Index.aspx' union all
select 750,'127.0.0.2','2009-11-17 11:48:03.403','ChatRoom' union all
select 751,'127.0.0.4','2009-11-17 11:48:04.543','Task' union all
select 752,'127.0.0.2','2009-11-17 11:48:11.780','VFClassroom' union all
select 753,'127.0.0.4','2009-11-17 11:48:12.717','Game' union all
select 754,'127.0.0.3','2009-11-17 11:48:13.717','Zsy' union all
select 755,'127.0.0.1','2009-11-17 11:48:14.733','CoursewareManagement' union all
select 756,'127.0.0.2','2009-11-17 11:48:16.000','friend' union all
select 757,'127.0.0.5','2009-11-17 11:48:18.687','http://localhost:1147/ttxs/user/RefLog.aspx' union all
select 758,'127.0.0.6','2009-11-17 11:48:19.047','http://localhost:1147/ttxs/user/ValidateCode.aspx' union all
select 759,'127.0.0.1','2009-11-17 11:48:20.750','http://localhost:1147/ttxs/index.aspx' union all
select 760,'127.0.0.2','2009-11-17 11:48:22.530','friend' union all
select 761,'127.0.0.3','2009-11-17 11:48:23.937','VFClassroom' union all
select 762,'127.0.0.2','2009-11-17 11:48:25.043','Task' union all
select 763,'127.0.0.2','2009-11-17 11:48:27.750','ChatRoom' union all
select 764,'127.0.0.2','2009-11-17 11:48:29.357','http://localhost:1147/ttxs/about/help.aspx' union all
select 765,'127.0.0.3','2009-11-17 11:48:33.187','CoursewareManagement' union all
select 766,'127.0.0.3','2009-11-17 11:48:34.403','http://localhost:1147/ttxs/index.aspx'




select convert(varchar(10),IP_datetime,120) as fldDate,
T_index=sum(case when IP_url='Index.aspx' then ipcount else 0 end),
T_Chat=sum(case when IP_url='ChatRoom' then ipcount else 0 end),
T_task=sum(case when IP_url='Task' then ipcount else 0 end),
T_KL_room=sum(case when IP_url='KL_room' then ipcount else 0 end),
T_class_room=sum(case when IP_url='VFClassroom' then ipcount else 0 end),
T_game=sum(case when IP_url='Game' then ipcount else 0 end),
T_Course=sum(case when IP_url='CoursewareManagement' then ipcount else 0 end),
T_friend=sum(case when IP_url='friend' then ipcount else 0 end)
from (
		select count(ip) as ipcount,IP_datetime, IP_url
		from (
		select distinct ip, convert(nvarchar, IP_datetime,111) as IP_datetime, IP_url
		from @tb
		) as a
		group by IP_datetime, IP_url
	) as b
 group by convert(varchar(10),IP_datetime,120) 

/*
fldDate    T_index     T_Chat      T_task      T_KL_room   T_class_room T_game      T_Course    T_friend    
---------- ----------- ----------- ----------- ----------- ------------ ----------- ----------- ----------- 
2009/11/17 1           1           2           0           2            1           2           1
2009/11/18 2           0           0           0           0            0           0           0

（所影响的行数为 2 行）

[jianshao810]

高手啊。。