现有如下成绩表,现欲求张三的平均分、最高分(科目)、最低(科目)分、总成绩。
<table border="0" cellspacing="1" cellpadding="1" width="300" align="center" bgcolor="#CCFFFF">
<tr align="center" bgcolor="#FFFFFF">
<td height="10">name</td>
<td height="10">english</td>
<td height="10">chinese</td>
<td height="10">maths</td>
<td height="10">science</td>
<td height="10">geography</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>张三</td>
<td>99</td>
<td>98</td>
<td>58</td>
<td>60</td>
<td>52</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>李四</td>
<td>100</td>
<td>85</td>
<td>20</td>
<td>69</td>
<td>97</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>王五</td>
<td>89</td>
<td>32</td>
<td>50</td>
<td>66</td>
<td>78</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>田七</td>
<td>100</td>
<td>82</td>
<td>59</td>
<td>79</td>
<td>85</td>
</tr>
</table>
<table border="0" cellspacing="1" cellpadding="1" width="300" align="center" bgcolor="#CCFFFF">
<tr align="center" bgcolor="#FFFFFF">
<td height="10">name</td>
<td height="10">english</td>
<td height="10">chinese</td>
<td height="10">maths</td>
<td height="10">science</td>
<td height="10">geography</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>张三</td>
<td>99</td>
<td>98</td>
<td>58</td>
<td>60</td>
<td>52</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>李四</td>
<td>100</td>
<td>85</td>
<td>20</td>
<td>69</td>
<td>97</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>王五</td>
<td>89</td>
<td>32</td>
<td>50</td>
<td>66</td>
<td>78</td>
</tr>
<tr align="center" bgcolor="#FFFFFF">
<td>田七</td>
<td>100</td>
<td>82</td>
<td>59</td>
<td>79</td>
<td>85</td>
</tr>
</table>
name english chinese maths science geography
张三 99.0 98.0 58.0 60.0 52.0
李四 100.0 85.0 20.0 69.0 97.0
王五 89.0 32.0 50.0 66.0 78.0
田七 100.0 82.0 59.0 79.0 85.0
from 表
declare @t table([name] varchar(10),english dec(5,1),chinese dec(5,1),maths dec(5,1),science dec(5,1),geography dec(5,1))
insert into @t select '张三',99.0,98.0,58.0,60.0,52.0
union all select '李四',100.0,85.0,20.0,69.0,97.0
union all select '王五',89.0,32.0,50.0,66.0,78.0
union all select '田七',100.0,82.0,59.0,79.0,85.0
select [name],
[max]=(select max(col) from (select col=a.english union select a.chinese union select a.maths union select a.science union select a.geography)a),
[min]=(select min(col) from (select col=a.english union select a.chinese union select a.maths union select a.science union select a.geography)a),
[sum]=(select sum(col) from (select col=a.english union select a.chinese union select a.maths union select a.science union select a.geography)a),
[avg]=(select avg(col) from (select col=a.english union select a.chinese union select a.maths union select a.science union select a.geography)a)
from @t a
go