-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-24 15:47:41
-------------------------------------
--> 生成测试数据: @message
DECLARE @message TABLE (id INT,fromname VARCHAR(6),toname VARCHAR(6),operation VARCHAR(3),date DATETIME)
INSERT INTO @message
SELECT 1,'admin1','admin2','get','2009-06-24 15:15:16' UNION ALL
SELECT 2,'admin1','admin2','get','2009-06-23 15:15:16' UNION ALL
SELECT 3,'admin1','admin3','get','2009-06-23 23:15:16' UNION ALL
SELECT 4,'admin2','admin1','get','2009-06-23 17:15:16' UNION ALL
SELECT 5,'admin1','admin1','get','2009-06-22 15:15:16'--SQL查询如下:SELECT fromname,CONVERT(VARCHAR(10),date,120) AS date,
COUNT(DISTINCT toname) AS 次数
FROM @message
WHERE operation = 'get'
GROUP BY fromname,CONVERT(VARCHAR(10),date,120)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-24 15:47:41
-------------------------------------
--> 生成测试数据: @message
DECLARE @message TABLE (id INT,fromname VARCHAR(6),toname VARCHAR(6),operation VARCHAR(3),date DATETIME)
INSERT INTO @message
SELECT 1,'admin1','admin2','get','2009-06-24 15:15:16' UNION ALL
SELECT 2,'admin1','admin2','get','2009-06-23 15:15:16' UNION ALL
SELECT 3,'admin1','admin3','get','2009-06-23 23:15:16' UNION ALL
SELECT 4,'admin2','admin1','get','2009-06-23 17:15:16' UNION ALL
SELECT 5,'admin1','admin1','get','2009-06-22 15:15:16'--SQL查询如下:SELECT fromname,CONVERT(VARCHAR(10),date,120) AS date,
COUNT(DISTINCT toname) AS 次数
FROM @message
WHERE operation = 'get'
GROUP BY fromname,CONVERT(VARCHAR(10),date,120)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-24 15:47:41
-------------------------------------
--> 生成测试数据: @message
DECLARE @message TABLE (id INT,fromname VARCHAR(6),toname VARCHAR(6),operation VARCHAR(3),date DATETIME)
INSERT INTO @message
SELECT 1,'admin1','admin2','get','2009-06-24 15:15:16' UNION ALL
SELECT 2,'admin1','admin2','get','2009-06-23 15:15:16' UNION ALL
SELECT 3,'admin1','admin3','get','2009-06-23 23:15:16' UNION ALL
SELECT 4,'admin2','admin1','get','2009-06-23 17:15:16' UNION ALL
SELECT 5,'admin1','admin1','get','2009-06-22 15:15:16'--SQL查询如下:--发
SELECT fromname,CONVERT(VARCHAR(10),date,120) AS date,
COUNT(DISTINCT toname) AS 次数
FROM @message
WHERE operation = 'get'
GROUP BY fromname,CONVERT(VARCHAR(10),date,120)--收
SELECT toname,CONVERT(VARCHAR(10),date,120) AS date,
COUNT(DISTINCT toname) AS 次数
FROM @message
WHERE operation = 'get'
GROUP BY toname,CONVERT(VARCHAR(10),date,120)
union all
select toname,date,'发件'+convert(char,count(operation))+'次' as 次数 from message where operation='get' group by toname,date
SELECT FROMNAME , COUNT(TONAME) , SENDAT FROM LYBBS_WEBQQ
WHERE OPERATION = 'GET'
GROUP BY FROMNAME , STRCMP(SENDAT,'YYYY-MM-DD')--发
SELECT FROMNAME , COUNT(TONAME) , SENDAT FROM LYBBS_WEBQQ
WHERE OPERATION = 'POST'
GROUP BY FROMNAME , STRCMP(SENDAT,'YYYY-MM-DD')
--发
SELECT fromname,DATE(`date`) AS date,
COUNT(DISTINCT toname) AS 次数
FROM message
WHERE operation = 'get'
GROUP BY fromname,DATE(`date`);--收
SELECT toname,DATE(`date`) AS date,
COUNT(DISTINCT toname) AS 次数
FROM message
WHERE operation = 'get'
GROUP BY toname,DATE(`date`)