.net压轴.docVIP

? 有点跑题了，不再啰嗦----直接晾出压轴题。 压轴题第一问 1.把表一转换为表二 表一：????????????????????????????????????????????????????????????????????? 表二： 数据库代码如下： 代码 ?1?DROP?table?#student?2?CREATE?TABLE?#student?(stdname?nvarchar(10),stdsubject?nvarchar(10),result?int)?3?INSERT?INTO?#student?VALUES?(张三,语文,80)?4?INSERT?INTO?#student?values?(张三,数学,90)?5?INSERT?INTO?#student?VALUES?(张三,物理,85)?6?INSERT?INTO?#student?VALUES?(李四,语文,85)?7?INSERT?INTO?#student?values?(李四,数学,92)?8?INSERT?INTO?#student?VALUES?(李四,物理,82)?9?INSERT?INTO?#student?VALUES?(李四,化学,82)10?INSERT?INTO?#student?VALUES?(李四,化学,82)11?SELECT?*?FROM?#student ? ? ?可能很多老手们，一看到这题目就有了答案。当然，贴出答案来不是我的目的，我要带着SQL新手们重构到答案。用李建忠老师最爱说的话就是------我不建议一上来就套用模式，而应该从重构到模式。 首先大家会想到分两组 1?select?stdname，····，from?#student?group?by?stdname ? ? 然后······中间该写什么呢？ 代码 1?case?stdsubject?when?化学?then?Result?end2??case?stdsubject?when?语文?then?Result?end3??case?stdsubject?when?···?then?Result?end4??case?stdsubject?when?···?then?Result?end5??case?stdsubject?when?···?then?Result?end ? 表二里面得0是哪里来的呢？ 代码 1?isnull(sum（case?stdsubject?when?化学?then?Result?end）,0)2?isnull(sum(case?stdsubject?when?语文?then?Result?end),0)3?isnull(sum(case?stdsubject?when?···?then?Result?end),0)4?isnull(sum(case?stdsubject?when?···?then?Result?end),0)5?isnull(sum(case?stdsubject?when?···?then?Result?end),0) ? ? 所以得出： 代码 1?select?stdname,2?isnull(sum(case?stdsubject?when?化学?then?Result?end),0)?[化学],3?isnull(sum(case?stdsubject?when?数学?then?Result?end),0)?[数学],4?isnull(sum(case?stdsubject?when?物理?then?Result?end),0)?[物理],5?isnull(sum(case?stdsubject?when?语文?then?Result?end),0)?[语文]?6?from?#student?7?group?by?stdname ? ? 然后得出答案： 代码 1?declare?@sql?varchar(4000)?2??set?@sql?=?select?stdname?3?select?@sql?=?@sql?+?,isnull(sum(case?stdsubject?when?+stdsubject+?then?Result?end),0)?[+stdsubject+]?4?from?(select?distinct?stdsubject?from?#student)?as?a?5?select?@sql?=?@sql+?from?#student?group?by?stdname?6?print?@sql7?exec(@sql)8?? ? ? ? ? 压轴题第二问：把表二转化为表一 表一： 表二： 数据库代码如下： 代码 1?DROP