34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT COUNT(DISTINCT 学号) AS 人数,
SUM(CASE WHEN rid = 1 THEN 年龄 ELSE 0 END) AS 总年龄,
SUM(成绩) AS 总成绩
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY 学号 ORDER BY 年龄) AS rid,
学号,
年龄,
成绩
FROM t
) AS tt
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
姓名 NVARCHAR(10),
性别 NCHAR(1),
年龄 INT,
学号 INT,
科目 NVARCHAR(10),
成绩 INT
)
INSERT INTO t
SELECT '张三','男','20',' 1234','语文',90
UNION ALL SELECT '张三','男','20','1234','数学',95
UNION ALL SELECT '李四','女','20',' 1111','语文',92
GO
select count(distinct 学号) 人数
,(
SELECT SUM(年龄) FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY 学号 ORDER BY 年龄) AS rid,年龄 FROM t
) AS t WHERE rid=1
) AS 总年龄
, sum(成绩) 总成绩
FROM t
/*
人数 总年龄 总成绩
----------- ----------- -----------
2 40 277
*/
SELECT count(工号) as 人数,sum(年龄) as 总年龄,sum(成绩) as 总成绩
FROM
(SELECT 工号,年龄,sum(成绩) as 成绩 from table group by 工号,年龄) as A