select 姓名 ,sum(分数) as total from T group by 姓名 order by total
select name,subject,sum(score) from test group by name,subject
select 姓名 ,sum(分数) as total from T group by 姓名 order by total
select 姓名 ,sum(分数) as total from T group by 姓名 order by total
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(姓名 varchar(10),科目 int, 分数 int) go insert tb SELECT 'z' , 1 , 67 UNION ALL SELECT 'x' , 2 , 78 UNION ALL SELECT 'y' , 1 , 89 UNION ALL SELECT 'x' , 1 , 99 UNION ALL SELECT 'z' , 2 , 87 UNION ALL SELECT 'y' , 2 , 78 go select 姓名,SUM(分数) as 总分数 from tb group by 姓名 order by 总分数 go /*------------ (6 行受影响) 姓名 总分数 ---------- ----------- z 154 y 167 x 177-------*/
if object_id('test') is not null drop table test create table test ( name varchar(10), subject varchar(10), score int )insert test select 'z',1,67 union all select 'x',2,78 union all select 'y',1,89 union all select 'x',1,99 union all select 'x',2,87 union all select 'y',2,78select * from test--按姓名 select name,amount=sum(score) from test group by name order by amount --按姓名和科目 select name,subject,amount=sum(score) from test group by name,subject order by amount/** name amount ---------- ----------- z 67 y 167 x 264(所影响的行数为 3 行)name subject amount ---------- ---------- ----------- z 1 67 y 2 78 y 1 89 x 1 99 x 2 165(所影响的行数为 5 行) **/
//看来英雄所见略同 select 姓名 ,sum(分数) as total from T group by 姓名 order by total
from T
group by 姓名
order by total
select name,subject,sum(score)
from test group by name,subject
from T
group by 姓名
order by total
select 姓名 ,sum(分数) as total
from T
group by 姓名
order by total
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(姓名 varchar(10),科目 int, 分数 int)
go
insert tb SELECT
'z' , 1 , 67 UNION ALL SELECT
'x' , 2 , 78 UNION ALL SELECT
'y' , 1 , 89 UNION ALL SELECT
'x' , 1 , 99 UNION ALL SELECT
'z' , 2 , 87 UNION ALL SELECT
'y' , 2 , 78
go
select 姓名,SUM(分数) as 总分数
from tb
group by 姓名
order by 总分数
go
/*------------
(6 行受影响)
姓名 总分数
---------- -----------
z 154
y 167
x 177-------*/
if object_id('test') is not null
drop table test
create table test
(
name varchar(10),
subject varchar(10),
score int
)insert test
select 'z',1,67 union all
select 'x',2,78 union all
select 'y',1,89 union all
select 'x',1,99 union all
select 'x',2,87 union all
select 'y',2,78select * from test--按姓名
select name,amount=sum(score) from test
group by name order by amount
--按姓名和科目
select name,subject,amount=sum(score) from test
group by name,subject order by amount/**
name amount
---------- -----------
z 67
y 167
x 264(所影响的行数为 3 行)name subject amount
---------- ---------- -----------
z 1 67
y 2 78
y 1 89
x 1 99
x 2 165(所影响的行数为 5 行)
**/
//看来英雄所见略同
select 姓名 ,sum(分数) as total
from T
group by 姓名
order by total