求个sql语句,该如何解决
日期：2014-05-16 浏览次数：20640 次
求个sql语句
表结构
1条sql语句可以统计出合计值吗?
比如说
教师 4
a1 41
a2 23
a3 18
....
18 26
------解决方案--------------------
select mc,sum(a1) from (
select a1,'a1' as mc from tt
union all
select a2,'a2' as mc from tt
union all
select a3,'a3' as mc from tt
union all
select a4,'a4' as mc from tt) a group by mc
------解决方案--------------------
表结构
- SQL code
Field Type Collation Null Key Default ------------------ ------------ -------------- ------ ------ ------- id int(11) (NULL) NO PRI (NULL) PROJECTID varchar(50) gbk_chinese_ci YES (NULL) USERID varchar(50) gbk_chinese_ci YES (NULL) select,insert,update,references USERNAME varchar(50) gbk_chinese_ci YES (NULL) select,insert,update,references a1 int(11) (NULL) YES 0 select,insert,update,references a2 int(11) (NULL) YES 0 select,insert,update,references a3 int(11) (NULL) YES 0 select,insert,update,references a4 int(11) (NULL) YES 0 select,insert,update,references a5 int(11) (NULL) YES 0 select,insert,update,references a6 int(11) (NULL) YES 0 select,insert,update,references a7 int(11) (NULL) YES 0 select,insert,update,references a8 int(11) (NULL) YES 0 select,insert,update,references
- SQL code
1 1 10001 教师1 10 10 2 0 0 0 3 8 2 1 10002 教师2 10 1 2 0 13 0 34 6 3 1 10003 教师3 10 12 2 0 30 3 12 4 4 1 10004 教师4 11 0 12 0 21 0 21 8
1条sql语句可以统计出合计值吗?
比如说
教师 4
a1 41
a2 23
a3 18
....
18 26
------解决方案--------------------
select mc,sum(a1) from (
select a1,'a1' as mc from tt
union all
select a2,'a2' as mc from tt
union all
select a3,'a3' as mc from tt
union all
select a4,'a4' as mc from tt) a group by mc
------解决方案--------------------
- SQL code
select count(distinct USERNAME) as 教师数,
sum(a1) as a1_sum,
sum(a2) as a2_sum,
sum(a3) as a3_sum,
sum(a4) as a4_sum,
sum(a5) as a5_sum,
sum(a6) as a6_sum,
sum(a7) as a7_sum,
sum(a8) as a8_sum from tb_name
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
