groupby用法

[qiujieda]

有个成绩表，共有5个用户，1,2,3,4,5，一个用户可以学习多次，每次学习都有记录。
1.求每个人的最高成绩和AttemptId
2.求每个人的最近一次学习记录
CREATE TABLE ABC_allScore(
OrderId INTEGER IDENTITY (1,1),
AttemptId INTEGER,
RegID INTEGER,
EmpId INTEGER,
StartDt datetime default null,
EndDt datetime default null,
Success INTEGER,
Score INTEGER,
EmployeeNumber nvarchar(200),
EmpFName nvarchar(200),
EmpLName nvarchar(200),
PrimaryDomain nvarchar(200)
)

INSERT INTO ABC_allScore VALUES(1,1,1,N'2010-08-19 04:49:32.320', N'2010-09-16 04:32:49.600', 1,100, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(2,2,1,N'2010-11-11 03:17:57.967', N'2010-11-25 03:20:02.853', 1,90, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(3,3,1,N'2010-11-04 07:53:47.103', N'2010-11-09 02:04:29.400', 1,100, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(4,4,1,N'2010-12-01 09:54:27.310', N'2010-12-01 11:42:23.607', 1,96, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(5,5,1,N'2010-11-22 08:21:54.070', N'2010-11-23 15:07:16.760', 1,90, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(6,6,2,N'2010-06-19 04:49:32.320', N'2010-09-16 04:32:49.600', 1,100, N'2',N'B',N'B',N'AA')
INSERT INTO ABC_allScore VALUES(7,7,2,N'2010-10-11 03:17:57.967', N'2010-11-25 03:20:02.853', 1,90, N'2',N'B',N'B',N'AA')
INSERT INTO ABC_allScore VALUES(8,8,3,N'2010-10-04 07:53:47.103', N'2010-11-09 02:04:29.400', 1,100, N'3',N'C',N'C',N'AA')
INSERT INTO ABC_allScore VALUES(9,9,4,N'2010-10-01 09:54:27.310', N'2010-12-01 11:42:23.607', 1,96, N'4',N'D',N'D',N'AA')
INSERT INTO ABC_allScore VALUES(10,10,5,N'2010-10-22 08:21:54.070', N'2010-11-23 15:07:16.760', 1,90, N'5',N'E',N'E',N'AA')
INSERT INTO ABC_allScore VALUES(11,11,1,N'2011-08-19 04:49:32.320', N'2011-09-16 04:32:49.600', 1,100, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(12,12,1,N'2011-11-11 03:17:57.967', N'2011-11-25 03:20:02.853', 1,90, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(13,13,1,N'2011-11-04 07:53:47.103', N'2011-11-09 02:04:29.400', 1,100, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(14,14,1,N'2011-12-01 09:54:27.310', N'2011-12-01 11:42:23.607', 1,96, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(15,15,1,N'2011-11-22 08:21:54.070', N'2011-11-23 15:07:16.760', 1,90, N'1',N'A',N'A',N'AA')
INSERT INTO ABC_allScore VALUES(16,16,2,N'2011-06-19 04:49:32.320', N'2011-09-16 04:32:49.600', 1,100, N'2',N'B',N'B',N'AA')
INSERT INTO ABC_allScore VALUES(17,17,2,N'2011-10-11 03:17:57.967', N'2011-11-25 03:20:02.853', 1,90, N'2',N'B',N'B',N'AA')
INSERT INTO ABC_allScore VALUES(18,18,3,N'2011-10-04 07:53:47.103', N'2011-11-09 02:04:29.400', 1,100, N'3',N'C',N'C',N'AA')
INSERT INTO ABC_allScore VALUES(19,19,4,N'2011-10-01 09:54:27.310', N'2011-12-01 11:42:23.607', 1,96, N'4',N'D',N'D',N'AA')
INSERT INTO ABC_allScore VALUES(20,20,5,N'2011-10-22 08:21:54.070', N'2011-11-23 15:07:16.760', 1,90, N'5',N'E',N'E',N'AA')

[發糞塗牆]

你的字段说明一下可以把？

[qiujieda]

OrderId
AttemptId 尝试ID
RegID 注册ID
EmpId 用户ID,
StartDt 开始时间
EndDt 完成时间
Success 完成状态
Score 分数
EmployeeNumber 员工编号
EmpFName nvarchar 姓
EmpLName nvarchar 名
PrimaryDomain 域

[qiujieda]

[Quote=引用 1 楼 的回复:]

你的字段说明一下可以把？
[/Quote]

AttemptId INTEGER,--尝试ID
RegID INTEGER,--注册ID
EmpId INTEGER,--用户ID
StartDt datetime default null,--开始时间
EndDt datetime default null,--完成时间
Success INTEGER,--完成状态
Score INTEGER,--成绩
EmployeeNumber nvarchar(200),--员工编号
EmpFName nvarchar(200),--姓
EmpLName nvarchar(200),--名
PrimaryDomain nvarchar(200)--域	

[發糞塗牆]

第一个：不过觉得怪怪的，求每个人的最高成绩和AttemptId 这里的AttemptId是指这个人总共的？还是说和这次最高成绩相对的AttemptId？另外同分的时候怎么取？

SELECT empid,EmployeeNumber,EmpFName,score,EmpLName,MAX(AttemptId)
FROM ABC_allScore a
WHERE EXISTS (
SELECT 1 FROM (SELECT empid,MAX(score)score FROM ABC_allScore GROUP BY empid) b where a.empid=b.empid AND a.score=b.score)
GROUP BY empid,EmployeeNumber,EmpFName,score,EmpLName

第二个：求每个人的最近一次学习记录

SELECT *
FROM ABC_allScore a
WHERE EXISTS (SELECT 1 FROM 
(SELECT empid,MAX(enddt) enddt
FROM ABC_allScore
GROUP BY empid) b WHERE A.empid=b.empid AND A.enddt=b.enddt )

[qiujieda]

[Quote=引用 4 楼 的回复:]

第一个：不过觉得怪怪的，求每个人的最高成绩和AttemptId 这里的AttemptId是指这个人总共的？还是说和这次最高成绩相对的AttemptId？另外同分的时候怎么取？
SQL code
SELECT empid,EmployeeNumber,EmpFName,score,EmpLName,MAX(AttemptId)
FROM ABC_allScore a
WHERE EXISTS……
[/Quote]
是和最高成绩对应的，同分的时候取最近的

[汤姆克鲁斯]

--1
SELECT * FROM  ABC_allScore a WHERE 1>(SELECT COUNT(*) FROM ABC_allScore WHERE EmpId=a.EmpId AND Score>a.Score)
SELECT * FROM  ABC_allScore a WHERE NOT exists (SELECT 1 FROM ABC_allScore WHERE EmpId=a.EmpId AND Score>a.Score)

--2
SELECT * FROM  ABC_allScore a WHERE NOT exists (SELECT 1 FROM ABC_allScore WHERE EmpId=a.EmpId AND StartDt>a.StartDt)

1里面有好多最好成绩一样的怎么处理？

[汤姆克鲁斯]

[Quote=引用 5 楼 的回复:]

引用 4 楼 的回复:

第一个：不过觉得怪怪的，求每个人的最高成绩和AttemptId 这里的AttemptId是指这个人总共的？还是说和这次最高成绩相对的AttemptId？另外同分的时候怎么取？
SQL code
SELECT empid,EmployeeNumber,EmpFName,score,EmpLName,MAX(AttemptId)
FROM ABC_allSco……
[/Quote]

--1

SELECT * FROM  ABC_allScore a WHERE 
NOT exists (SELECT 1 FROM ABC_allScore WHERE EmpId=a.EmpId AND Score>a.Score)
AND NOT exists (SELECT 1 FROM ABC_allScore WHERE EmpId=a.EmpId AND Score=a.Score AND StartDt>a.StartDt)

--2
SELECT * FROM  ABC_allScore a WHERE NOT exists (SELECT 1 FROM ABC_allScore WHERE EmpId=a.EmpId AND StartDt>a.StartDt)

[qiujieda]

[Quote=引用 4 楼 的回复:]
第一个：不过觉得怪怪的，求每个人的最高成绩和AttemptId 这里的AttemptId是指这个人总共的？还是说和这次最高成绩相对的AttemptId？另外同分的时候怎么取？
SQL code
SELECT empid,EmployeeNumber,EmpFName,score,EmpLName,MAX(AttemptId)
FROM ABC_allScore a
WHERE EXISTS ……
[/Quote]
这样如果成绩为空就不显示，如果成绩和时间为空时也要有这条记录该怎么改