现有N张表按照每天分别创建的表,数据结构相同。表1 ADCount100603
id webid ip uv addtime
3 20 152.164.169.181 1 2010-06-03 11:25:54
3 20 152.164.169.181 0 2010-06-03 12:05:54
3 20 152.164.169.181 0 2010-06-03 15:45:54表2 ADCount100604
id webid ip uv addtime
3 20 192.199.202.210 0 2010-06-04 13:05:54
3 20 227.230.231.235 1 2010-06-04 13:05:54
3 20 45.71.79.105 0 2010-06-04 13:05:54表3 ADCount100605
id webid ip uv addtime
3 20 152.164.169.181 1 2010-06-05 14:05:54
3 20 111.128.134.152 0 2010-06-05 14:05:54
3 20 28.56.65.93 1 2010-06-05 14:05:54
3 20 23.156.65.93 1 2010-06-05 14:05:54
3 20 23.156.65.93 0 2010-06-05 14:05:54
问题1 我要按每天时间数据统计最终效果是
日期 PV(总访问) UV IP
2010-06-05 5 3 4
2010-06-04 3 1 3
2010-06-03 3 1 1我目前只能查出单个。要想同时统计出来不知道如何下手。 谢谢各位高手了
--独立IP出现次数
SELECT count (*) as 出现次数,ip FROM ( select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604 ) X group by ip --每天统计总PV
select count(*) as 访问量,convert(varchar(11),addtime,120) AS 日期 FROM (select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604) x group by convert(varchar(11),addtime,120)
id webid ip uv addtime
3 20 152.164.169.181 1 2010-06-03 11:25:54
3 20 152.164.169.181 0 2010-06-03 12:05:54
3 20 152.164.169.181 0 2010-06-03 15:45:54表2 ADCount100604
id webid ip uv addtime
3 20 192.199.202.210 0 2010-06-04 13:05:54
3 20 227.230.231.235 1 2010-06-04 13:05:54
3 20 45.71.79.105 0 2010-06-04 13:05:54表3 ADCount100605
id webid ip uv addtime
3 20 152.164.169.181 1 2010-06-05 14:05:54
3 20 111.128.134.152 0 2010-06-05 14:05:54
3 20 28.56.65.93 1 2010-06-05 14:05:54
3 20 23.156.65.93 1 2010-06-05 14:05:54
3 20 23.156.65.93 0 2010-06-05 14:05:54
问题1 我要按每天时间数据统计最终效果是
日期 PV(总访问) UV IP
2010-06-05 5 3 4
2010-06-04 3 1 3
2010-06-03 3 1 1我目前只能查出单个。要想同时统计出来不知道如何下手。 谢谢各位高手了
--独立IP出现次数
SELECT count (*) as 出现次数,ip FROM ( select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604 ) X group by ip --每天统计总PV
select count(*) as 访问量,convert(varchar(11),addtime,120) AS 日期 FROM (select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604) x group by convert(varchar(11),addtime,120)
union
select count(*) as 访问量,convert(varchar(11),addtime,120) AS 日期 FROM (select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604) x group by convert(varchar(11),addtime,120)
sum(uv) as uv,
count(distinct ip) as ip
FROM (select * from ADCount100603 union select * from ADCount100605 union select * from ADCount100604) x group by convert(varchar(11),addtime,120)
IF OBJECT_ID('[ADCount100603]') IS NOT NULL
DROP TABLE [ADCount100603]
GO
CREATE TABLE [ADCount100603] ([id] [int],[webid] [int],[ip] [nvarchar](20),[uv] [int],[addtime] [datetime])
INSERT INTO [ADCount100603]
SELECT '3','20','152.164.169.181','1','2010-06-03 11:25:54' UNION ALL
SELECT '3','20','152.164.169.181','0','2010-06-03 12:05:54' UNION ALL
SELECT '3','20','152.164.169.181','0','2010-06-03 15:45:54'--> 生成测试数据表: [ADCount100604]
IF OBJECT_ID('[ADCount100604]') IS NOT NULL
DROP TABLE [ADCount100604]
GO
CREATE TABLE [ADCount100604] ([id] [int],[webid] [int],[ip] [nvarchar](20),[uv] [int],[addtime] [datetime])
INSERT INTO [ADCount100604]
SELECT '3','20','192.199.202.210','0','2010-06-04 13:05:54' UNION ALL
SELECT '3','20','227.230.231.235','1','2010-06-04 13:05:54' UNION ALL
SELECT '3','20','45.71.79.105','0','2010-06-04 13:05:54'--> 生成测试数据表: [ADCount100605]
IF OBJECT_ID('[ADCount100605]') IS NOT NULL
DROP TABLE [ADCount100605]
GO
CREATE TABLE [ADCount100605] ([id] [int],[webid] [int],[ip] [nvarchar](20),[uv] [int],[addtime] [datetime])
INSERT INTO [ADCount100605]
SELECT '3','20','152.164.169.181','1','2010-06-05 14:05:54' UNION ALL
SELECT '3','20','111.128.134.152','0','2010-06-05 14:05:54' UNION ALL
SELECT '3','20','28.56.65.93','1','2010-06-05 14:05:54' UNION ALL
SELECT '3','20','23.156.65.93','1','2010-06-05 14:05:54' UNION ALL
SELECT '3','20','23.156.65.93','0','2010-06-05 14:05:54'
-->SQL查询如下:
SELECT CONVERT(CHAR, addtime, 23) 日期, COUNT(1) 总访问量,
UV = SUM(UV), IP = COUNT(DISTINCT ip)
FROM (
SELECT *
FROM [ADCount100603]
UNION ALL
SELECT *
FROM [ADCount100604]
UNION ALL
SELECT *
FROM [ADCount100605]
) t
GROUP BY CONVERT(CHAR, addtime, 23)
ORDER BY 1 DESC, 2 DESC, 3 DESC
/*
日期 总访问量 UV IP
------------------------------ ----------- ----------- -----------
2010-06-05 5 3 4
2010-06-04 3 1 3
2010-06-03 3 1 1(3 行受影响)*/
太崇拜你了。