表A：学生表 （学号关联）
表B：学生科目成绩表 （ 数字，语文，英语等）（学号关联）
统计：学生的 各单科成绩，总分，平均分，排名（按这排序）
统计的科目成绩,但不一定是全部科目，按条件查询其中一个或多个科目成绩。
如果查询两科成绩出现结果
学生/学号 语文 数字 总成绩 平均分 排名
1001 80 90 170 85 1
1008 82 80 162 81 2
.....................................
如果查询三科成绩出现结果
学生/学号 语文 数字 英语 总成绩 平均分 排名
1001 80 90 100 270 90 1
1008 82 80 81 243 81 2
.....................................
求统计语句，，谢谢。。。。。。。。。
------解决思路----------------------
给点数据及结果。
------解决思路----------------------
CREATE TABLE Student(
strCode varchar(10) NOT NULL PRIMARY KEY,
strName varchar(50) NULL
)
CREATE TABLE Grade(
strCode varchar(10) NOT NULL,
strCourse varchar(50)NOT NULL,
fGrade FLOAT NOT NULL
)
ALTER TABLE Grade
ADD CONSTRAINT pk_Grade PRIMARY KEY(strCode, strCourse)
go
INSERT INTO Student(strCode,strName) VALUES ('1001 ','张三')
INSERT INTO Student(strCode,strName) VALUES ('1002 ','王五')
INSERT INTO Student(strCode,strName) VALUES ('1008 ','李四')
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1001 ','语文',80)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1001 ','数学',90)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1001 ','英语',100)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1002 ','语文',65)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1002 ','数学',68)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1002 ','英语',77)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1008 ','语文',82)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1008 ','数学',80)
INSERT INTO Grade(strCode,strCourse,fGrade) VALUES ('1008 ','英语',81)
go
CREATE PROCEDURE GetGrade
@str varchar(500)
AS
declare @sqlcom nvarchar(2000)
declare @str1 varchar(500)
set @str1=''''+@str+''''
set @sqlcom='
SELECT m.* ,
n.总分 ,
n.平均分, ROW_NUMBER() over(order by n.平均分 DESC ) as 排名
FROM ( SELECT *
FROM Grade PIVOT( MAX(fGrade) FOR strCourse IN ( '+@str+' ) ) a
) m ,
( SELECT strCode ,
SUM(fGrade) 总分 ,
CAST(AVG(fGrade * 1.0) AS DECIMAL(18, 2)) 平均分
FROM Grade where '+@str1+' like ''%''+strCourse+''%''
GROUP BY strCode
) n
WHERE m.strCode = n.strCode'
print @sqlcom
exec(@sqlcom)
go
exec GetGrade '语文'
exec GetGrade '语文,数学'
exec GetGrade '语文,数学,英语'
执行存储过程所得结果如下图：
------解决思路----------------------
-- 快下班了，写一个
-- 要动态 SQL 。
create table test(xh int , km varchar(10) , cj int)
go
insert into test(xh , km,cj) values
(1001 ,'语文', 80 ),
(1001 ,'数学', 90 ),
(1001 ,'英语', 100),
(1008 ,'语文' ,82 ),
(1008 ,'数学', 80 ),
(1008 ,'英语', 81 )
go
declare @sql varchar(2000) = 'select xh ,'
declare @km varchar(100)
declare @s varchar(100)
declare @condition varchar(100)
set @condition = '语文,数学,'
set @s = @condition
while charindex(',',@s,1) > 0
begin
set @km = substring(@s,1,charindex(',',@s,1)-1)
set @s = substring(@s,charindex(',',@s,1) + 1 ,len(@s))
set @sql = @sql + 'sum(case when km = ''' + @km + ''' then cj end) as ' + @km + ','
end
set @sql = @sql + 'sum(cj) as sum_cj ,'
set @sql = @sql + 'avg(cj) as avg_cj ,'
set @sql = @sql + 'row_number() over(order by sum(cj) desc ) as mc'
set @sql = @sql + ' from test where charindex(km,''' + @condition + ''') > 0 group by xh';
exec (@sql)
go
drop table test
go
(6 行受影响)
xh 语文 数学 sum_cj avg_cj mc
----------- ----------- ----------- ----------- ----------- --------------------
1001 80 90 170 85 1
1008 82 80 162 81 2
(2 行受影响)
------解决思路----------------------
建议楼主再提问时，把数据组织好，这样可以节省那些大神们的时间。
------解决思路----------------------
用case when 效率会低很多的，建议用行转列来处理
------解决思路----------------------
因为你查询的科目不确定，是动态的，建议最好用存储过程或函数来调用。
直接用SQL语句的话，你每次都要去修改条件，不建议使用
直接用SQL语句查询语文和数学的话可以这样写
SELECT m.* ,
n.总分 ,
n.平均分, ROW_NUMBER() over(order by n.平均分 DESC ) as 排名
FROM ( SELECT *
FROM Grade PIVOT( MAX(fGrade) FOR strCourse IN ( 语文,数学 ) ) a
) m ,
( SELECT strCode ,
SUM(fGrade) 总分 ,
CAST(AVG(fGrade * 1.0) AS DECIMAL(18, 2)) 平均分
FROM Grade where '语文,数学' like '%'+strCourse+'%'
GROUP BY strCode
) n
WHERE m.strCode = n.strCode
------解决思路----------------------
把一堆语句放在一个SQL 块就可以了，完全可以不用存储过程。
但是，还是建议使用存储过程，以后万一逻辑有变动，处理起来会方便很多。