--测试环境 create table ussn (updatetime smalldatetime, score1 int, score2 int, name nvarchar(50)) go insert ussn select '2005-12-05',12,99,'王' union all select '2005-06-09',31,16,'王' union all select '2005-09-18',99,40,'李' union all select '2005-12-09',99,20,'李' union all select '2005-12-09',80,80,'钱' go --执行语句 select updatetime=(select top 1 updatetime from ussn where name=u.name order by score1 desc,score2 desc ), score1=(select top 1 score1 from ussn where name=u.name order by score1 desc,score2 desc ), score2=(select top 1 score2 from ussn where name=u.name order by score1 desc,score2 desc ), name from ussn u group by name order by score1 desc --输出结果 2005-09-18 00:00:00 99 40 李 2005-12-09 00:00:00 80 80 钱 2005-06-09 00:00:00 31 16 王楼主 你那结果是不是错了? 自己好好看看 第一: 你咋出来个 2005-19-9 80 80 钱 ? 我改成 12-09 号了 第二 :你第一行 2005-12-9 99 40 李 日期也看串行了吧 应该是 2005-9-18 99 40 李 吧? 看看我写的行不
Create table studentScore (updatatime DATETIME NOT NULL DEFAULT GETDATE(), score1 INT NOT NULL, score2 INT NOT NULL, name NVARCHAR(50) NOT NULL) GO
INSERT studentScore SELECT '2005-12-11',100,20,'cen123' union all select '2005-12-05',12,99,'王' union all select '2005-06-09',31,16,'王' union all select '2005-09-18',99,40,'李' union all select '2005-12-09',99,20,'李' union all select '2005-12-09',80,80,'钱'select * from studentScore order by score1 desc,(select max(score1+score2) as score from studentScore) desc
max(score1),max(score2)
得不到结果,他只是去算最大值,我只想把表中记录排序,重复的人名取最好的成绩算
create table ussn
(updatetime smalldatetime,
score1 int,
score2 int,
name nvarchar(50))
go
insert ussn
select '2005-12-05',12,99,'王' union all
select '2005-06-09',31,16,'王' union all
select '2005-09-18',99,40,'李' union all
select '2005-12-09',99,20,'李' union all
select '2005-12-09',80,80,'钱'
go
--执行语句
select updatetime=(select top 1 updatetime from ussn where name=u.name order by score1 desc,score2 desc ),
score1=(select top 1 score1 from ussn where name=u.name order by score1 desc,score2 desc ),
score2=(select top 1 score2 from ussn where name=u.name order by score1 desc,score2 desc ),
name
from ussn u
group by name
order by score1 desc
--输出结果
2005-09-18 00:00:00 99 40 李
2005-12-09 00:00:00 80 80 钱
2005-06-09 00:00:00 31 16 王楼主 你那结果是不是错了? 自己好好看看
第一: 你咋出来个 2005-19-9 80 80 钱 ?
我改成 12-09 号了
第二 :你第一行 2005-12-9 99 40 李
日期也看串行了吧
应该是 2005-9-18 99 40 李 吧?
看看我写的行不
(updatatime DATETIME NOT NULL DEFAULT GETDATE(),
score1 INT NOT NULL,
score2 INT NOT NULL,
name NVARCHAR(50) NOT NULL)
GO
INSERT studentScore SELECT '2005-12-11',100,20,'cen123' union all
select '2005-12-05',12,99,'王' union all
select '2005-06-09',31,16,'王' union all
select '2005-09-18',99,40,'李' union all
select '2005-12-09',99,20,'李' union all
select '2005-12-09',80,80,'钱'select * from studentScore order by score1 desc,(select max(score1+score2) as score from
studentScore) desc