数据如下:
id userId Type dui cuo date
56 00001 一般 1 0 2008-12-15 14:17:49
57 00001 一般 0 1 2008-12-15 14:17:49
58 00001 一般 1 0 2008-12-15 14:17:49
60 00002 不错 0 1 2008-11-15 14:17:49
61 00002 不错 0 1 2008-11-15 14:17:49
62 00002 不错 1 0 2008-11-15 14:17:49
63 00001 良好 0 1 2008-10-15 14:17:49
64 00001 良好 0 1 2008-10-15 14:17:49
65 00001 良好 1 0 2008-10-15 14:17:49
我想查询出来的结果如下
用户ID 类型 对 错 日期
00001 一般 2 1 2008-12-15 14:17:49
00002 不错 1 2 2008-11-15 14:17:49
00002 良好 1 2 2008-10-15 14:17:49
它们是根据date来判断的,date是同一个为一组数据,dui和cuo字段就是同一组数据相加的结果!
这样一个效果要怎么写阿?
id userId Type dui cuo date
56 00001 一般 1 0 2008-12-15 14:17:49
57 00001 一般 0 1 2008-12-15 14:17:49
58 00001 一般 1 0 2008-12-15 14:17:49
60 00002 不错 0 1 2008-11-15 14:17:49
61 00002 不错 0 1 2008-11-15 14:17:49
62 00002 不错 1 0 2008-11-15 14:17:49
63 00001 良好 0 1 2008-10-15 14:17:49
64 00001 良好 0 1 2008-10-15 14:17:49
65 00001 良好 1 0 2008-10-15 14:17:49
我想查询出来的结果如下
用户ID 类型 对 错 日期
00001 一般 2 1 2008-12-15 14:17:49
00002 不错 1 2 2008-11-15 14:17:49
00002 良好 1 2 2008-10-15 14:17:49
它们是根据date来判断的,date是同一个为一组数据,dui和cuo字段就是同一组数据相加的结果!
这样一个效果要怎么写阿?
Type,
对 = (select sum(dui) from tb where userid = t.userid and type = t.type and date = t.date),
对 = (select sum(cuo) from tb where userid = t.userid and type = t.type and date = t.date),
date
from tb t
-- Author: liangCK 小梁
-- Date : 2008-11-17 14:53:53
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,userId VARCHAR(5),Type VARCHAR(4),dui INT,cuo INT,date DATETIME)
INSERT INTO @T
SELECT 56,'00001','一般',1,0,'2008-12-15 14:17:49' UNION ALL
SELECT 57,'00001','一般',0,1,'2008-12-15 14:17:49' UNION ALL
SELECT 58,'00001','一般',1,0,'2008-12-15 14:17:49' UNION ALL
SELECT 60,'00002','不错',0,1,'2008-11-15 14:17:49' UNION ALL
SELECT 61,'00002','不错',0,1,'2008-11-15 14:17:49' UNION ALL
SELECT 62,'00002','不错',1,0,'2008-11-15 14:17:49' UNION ALL
SELECT 63,'00001','良好',0,1,'2008-10-15 14:17:49' UNION ALL
SELECT 64,'00001','良好',0,1,'2008-10-15 14:17:49' UNION ALL
SELECT 65,'00001','良好',1,0,'2008-10-15 14:17:49'--SQL查询如下:SELECT
userid,
Type,
SUM(dui) AS 对,
SUM(cuo) AS 错,
date
FROM @T
GROUP BY
userid,
Type,
date
/*
userid Type 对 错 date
------ ---- ----------- ----------- -----------------------
00001 良好 1 2 2008-10-15 14:17:49.000
00001 一般 2 1 2008-12-15 14:17:49.000
00002 不错 1 2 2008-11-15 14:17:49.000(3 行受影响)
*/
insert into tb values(56 , '00001' , '一般' , 1 , 0 , '2008-12-15 14:17:49')
insert into tb values(57 , '00001' , '一般' , 0 , 1 , '2008-12-15 14:17:49')
insert into tb values(58 , '00001' , '一般' , 1 , 0 , '2008-12-15 14:17:49')
insert into tb values(60 , '00002' , '不错' , 0 , 1 , '2008-11-15 14:17:49')
insert into tb values(61 , '00002' , '不错' , 0 , 1 , '2008-11-15 14:17:49')
insert into tb values(62 , '00002' , '不错' , 1 , 0 , '2008-11-15 14:17:49')
insert into tb values(63 , '00001' , '良好' , 0 , 1 , '2008-10-15 14:17:49')
insert into tb values(64 , '00001' , '良好' , 0 , 1 , '2008-10-15 14:17:49')
insert into tb values(65 , '00001' , '良好' , 1 , 0 , '2008-10-15 14:17:49')
goselect userId ,
Type,
对 = (select sum(dui) from tb where userid = t.userid and type = t.type and date = t.date),
错 = (select sum(cuo) from tb where userid = t.userid and type = t.type and date = t.date),
date
from tb t
group by userId , Type,datedrop table tb/*
userId Type 对 错 date
---------- ---------- ----------- ----------- ------------------------------------------------------
00001 良好 1 2 2008-10-15 14:17:49.000
00001 一般 2 1 2008-12-15 14:17:49.000
00002 不错 1 2 2008-11-15 14:17:49.000(所影响的行数为 3 行)
*/
Type,
sum(dui),
sum(cuo),
date
from tb group by date最后一条00002肯定是写错了
create table tb(id int, userId varchar(10), Type varchar(10), dui int, cuo int, date datetime)
insert into tb values(56 , '00001' , '一般' , 1 , 0 , '2008-12-15 14:17:49')
insert into tb values(57 , '00001' , '一般' , 0 , 1 , '2008-12-15 14:17:49')
insert into tb values(58 , '00001' , '一般' , 1 , 0 , '2008-12-15 14:17:49')
insert into tb values(60 , '00002' , '不错' , 0 , 1 , '2008-11-15 14:17:49')
insert into tb values(61 , '00002' , '不错' , 0 , 1 , '2008-11-15 14:17:49')
insert into tb values(62 , '00002' , '不错' , 1 , 0 , '2008-11-15 14:17:49')
insert into tb values(63 , '00001' , '良好' , 0 , 1 , '2008-10-15 14:17:49')
insert into tb values(64 , '00001' , '良好' , 0 , 1 , '2008-10-15 14:17:49')
insert into tb values(65 , '00001' , '良好' , 1 , 0 , '2008-10-15 14:17:49')
goselect userId ,
Type,
对 = sum(dui),
错 = sum(cuo),
date
from tb t
group by userId , Type,datedrop table tb/*
userId Type 对 错 date
---------- ---------- ----------- ----------- ------------------------------------------------------
00001 良好 1 2 2008-10-15 14:17:49.000
00001 一般 2 1 2008-12-15 14:17:49.000
00002 不错 1 2 2008-11-15 14:17:49.000(所影响的行数为 3 行)
*/
from tb
group by date,userId,Type
Type,
sum(dui),
sum(cuo),
date
from tb group by date,Type,userId
Type,
对 = (select sum(dui) from tb where userid = t.userid and type = t.type and date = t.date),
错 = (select sum(cuo) from tb where userid = t.userid and type = t.type and date = t.date),
date
from tb t
INSERT INTO #TT
SELECT 56,'00001','一般',1,0,'2008-12-15 14:17:49' UNION ALL
SELECT 57,'00001','一般',0,1,'2008-12-15 14:17:49' UNION ALL
SELECT 58,'00001','一般',1,0,'2008-12-15 14:17:49' UNION ALL
SELECT 60,'00002','不错',0,1,'2008-11-15 14:17:49' UNION ALL
SELECT 61,'00002','不错',0,1,'2008-11-15 14:17:49' UNION ALL
SELECT 62,'00002','不错',1,0,'2008-11-15 14:17:49' UNION ALL
SELECT 63,'00001','良好',0,1,'2008-10-15 14:17:49' UNION ALL
SELECT 64,'00001','良好',0,1,'2008-10-15 14:17:49' UNION ALL
SELECT 65,'00001','良好',1,0,'2008-10-15 14:17:49'select userId,sum(dui) '对',sum(cuo) '错',date from #TT group by userId,date