按不同组织来分类统计的通用语句

求一个按不同组织来分类统计的通用语句
数据：

SQL code

DECLARE @t_class TABLE (id  INT, name VARCHAR(100),parent_id INT )INSERT INTO @t_classSELECT 1,'高中部',0 UNION ALL SELECT 2,'高一年级',1 UNION ALL SELECT 3,'高二年级',1 UNION ALL SELECT 4,'高二一班',3 UNION ALL SELECT 5,'高二二班',3 UNION ALLSELECT 6,'高一二班',2 UNION ALL SELECT 7,'高一一班',2  DECLARE @t_grade TABLE (id INT ,student_id INT ,class_id INT ,course_id INT ,grade INT )INSERT INTO @t_gradeSELECT 1,001,4,001,60 UNION ALLSELECT 2,002,5,001,30 UNION ALLSELECT 3,003,4,001,80 UNION ALLSELECT 4,004,5,001,90 UNION ALLSELECT 4,004,6,001,90 UNION ALLSELECT 4,004,7,001,90 

按照班级统计成绩：

SQL code

SELECT c3.name,c2.name,c1.name,ga.course_id,ga.grade  from (select c.id,g.course_id ,sum(g.grade) as grade  from  @t_grade g join @t_class c on g.class_id = c.idgroup  by c.id,course_id) as gajoin @t_class c1 on ga.id = c1.idjoin @t_class c2 on c2.id = c1.parent_id join @t_class c3 on c3.id = c2.parent_id  

按照年级统计成绩：

SQL code

SELECT c3.name,c2.name,'' AS name,ga.course_id,ga.grade  from (select c2.id,g.course_id ,sum(g.grade) as grade  from  @t_grade g join @t_class c on g.class_id = c.id join @t_class c2 on c2.id = c.parent_id group  by c2.id,g.course_id) as gajoin @t_class c2 on c2.id = ga.idjoin @t_class c3 on c3.id = c2.parent_id

按照学习部统计成绩：

SQL code

SELECT c3.name,'' AS name  ,'' AS name,ga.course_id,ga.grade  from (select c3.id,g.course_id ,sum(g.grade) as grade  from  @t_grade g join @t_class c on g.class_id = c.id join @t_class c2 on c2.id = c.parent_id join @t_class c3 on c3.id = c2.parent_idgroup  by c3.id,g.course_id) as gajoin @t_class c3 on c3.id = ga.id

以上三种方式会在一个存储过程中，看着闹心啊 求一能整合这三条语句的通用语句....
先表示 感谢！

[解决办法]
你这个怎么整合哦 用着吧 孩子。
[解决办法]
可以为三条查询语句各建一个存储过程，然后调用时直接调用这些存储过程来返回数据就可以了
[解决办法]
这个除了拼接字符串的形式外，你还能想到更好的方法吗？
[解决办法]
就这样用着吧。没必要找麻烦

查看更多 下一篇