请教一种在MySQL中的对数据的统计方法

Question

本帖最后由 WheatField 于 2009-09-17 16:06:38 编辑

Answer 1

select s.f_StudentID,
'A' as f_SubjectA,
sum(if(f_Subject='A',f_Score,0) as f_SumA,
'B' as f_SubjectB,
sum(if(f_Subject='A',f_Score,0) as f_SumB
from t_Student s,t_Score c
where s.f_StudentID=c.f_StudentID
group by s.f_StudentID

Answer 2

或者select s.f_Name,
'A' as f_SubjectA,
sum(if(f_Subject='A',f_Score,0) as f_SumA,
'B' as f_SubjectB,
sum(if(f_Subject='A',f_Score,0) as f_SumB
from t_Student s inner join t_Score c on s.f_StudentID=c.f_StudentID
group by s.f_Nameselect s.f_Name,
'A' as f_SubjectA,
sum(if(f_Subject='A',f_Score,0) as f_SumA,
'B' as f_SubjectB,
sum(if(f_Subject='A',f_Score,0) as f_SumB
from t_Student s inner join t_Score c  using( f_StudentID)
group by s.f_Name

Answer 3

mysql> select * from t_Student;
+-------------+--------+
| f_StudentID | f_Name |
+-------------+--------+
|           1 | 张三   |
|           2 | 李四   |
|           3 | 王五   |
|           4 | 赵六   |
+-------------+--------+
4 rows in set (0.00 sec)mysql> select * from t_Score;
+-------------+-----------+---------+
| f_StudentID | f_Subject | f_Score |
+-------------+-----------+---------+
|           1 | A         |      70 |
|           1 | A         |      60 |
|           1 | B         |      60 |
|           2 | B         |      60 |
|           2 | B         |      70 |
|           3 | A         |      80 |
|           3 | A         |      60 |
|           4 | A         |      50 |
|           4 | A         |      60 |
|           4 | B         |      70 |
|           4 | B         |      80 |
+-------------+-----------+---------+
11 rows in set (0.00 sec)mysql>
mysql> select s.f_Name,
    ->  'A' as f_SubjectA,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumA,
    ->  'B' as f_SubjectB,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumB
    -> from t_Student s,t_Score c
    -> where s.f_StudentID=c.f_StudentID
    -> group by s.f_Name;
+--------+------------+--------+------------+--------+
| f_Name | f_SubjectA | f_SumA | f_SubjectB | f_SumB |
+--------+------------+--------+------------+--------+
| 李四   | A          |      0 | B          |      0 |
| 王五   | A          |    140 | B          |    140 |
| 赵六   | A          |    110 | B          |    110 |
| 张三   | A          |    130 | B          |    130 |
+--------+------------+--------+------------+--------+
4 rows in set (0.06 sec)mysql> select s.f_Name,
    ->  'A' as f_SubjectA,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumA,
    ->  'B' as f_SubjectB,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumB
    -> from t_Student s inner join t_Score c on s.f_StudentID=c.f_StudentID
    -> group by s.f_Name;
+--------+------------+--------+------------+--------+
| f_Name | f_SubjectA | f_SumA | f_SubjectB | f_SumB |
+--------+------------+--------+------------+--------+
| 李四   | A          |      0 | B          |      0 |
| 王五   | A          |    140 | B          |    140 |
| 赵六   | A          |    110 | B          |    110 |
| 张三   | A          |    130 | B          |    130 |
+--------+------------+--------+------------+--------+
4 rows in set (0.00 sec)mysql> select s.f_Name,
    ->  'A' as f_SubjectA,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumA,
    ->  'B' as f_SubjectB,
    ->  sum(if(f_Subject='A',f_Score,0)) as f_SumB
    -> from t_Student s inner join t_Score c  using( f_StudentID)
    -> group by s.f_Name;
+--------+------------+--------+------------+--------+
| f_Name | f_SubjectA | f_SumA | f_SubjectB | f_SumB |
+--------+------------+--------+------------+--------+
| 李四   | A          |      0 | B          |      0 |
| 王五   | A          |    140 | B          |    140 |
| 赵六   | A          |    110 | B          |    110 |
| 张三   | A          |    130 | B          |    130 |
+--------+------------+--------+------------+--------+
4 rows in set (0.00 sec)mysql>

Answer 4

会有影响，这样的话需要设置相应的索引来。
t_Student (f_StudentID) 为主键。
t_Score(f_StudentID,f_Subject) 为主键的话。
应该就没什么可再优化的了。

Answer 5

就是交叉表嘛
select s.f_Name, 'A' as f_SubjectA,
    sum(if(f_Subject='A',f_Score,0) as f_SumA,
    'B' as f_SubjectB,
    sum(if(f_Subject='A',f_Score,0) as f_SumB
from t_Student s left join t_Score c on s.f_StudentID=c.f_StudentID
group by s.f_NameOR
将IF－＞CASE　WHEN

Answer 6

多谢WWWWA，明天给分。
另外我想，如果建立两个临时表，从f_Score中把数据统计出来插入临时表，再从临时表中读取数据，那样的话应该效率会比IF判断高。

Answer 7

mysql> select * from t_student;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
|    4 | 赵六 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from score;
+-----------+---------+-------+
| studentid | subject | score |
+-----------+---------+-------+
|         1 | A       |    70 |
|         1 | A       |    60 |
|         1 | B       |    60 |
|         2 | B       |    60 |
|         2 | B       |    70 |
|         3 | A       |    80 |
|         3 | A       |    60 |
|         4 | A       |    50 |
|         4 | A       |    60 |
|         4 | B       |    70 |
|         4 | B       |    80 |
+-----------+---------+-------+
11 rows in set (0.00 sec)
mysql> select s.name,sa.subject subject_a,sa.tscore suma,sb.subject subject_b,
    -> sb.tscore sumb
    -> from t_student s
    -> left join
    -> (select studentid,sum(score) tscore,'A' as subject
    -> from score
    -> where subject='A'
    -> group by studentid)sa
    -> on s.id=sa.studentid
    -> left join
    -> (select studentid,sum(score) tscore,'B' as subject
    -> from score
    -> where subject='B'
    -> group by studentid)sb
    -> on s.id=sb.studentid;
+------+-----------+------+-----------+------+
| name | subject_a | suma | subject_b | sumb |
+------+-----------+------+-----------+------+
| 张三 | A         |  130 | B         |   60 |
| 李四 | NULL      | NULL | B         |  130 |
| 王五 | A         |  140 | NULL      | NULL |
| 赵六 | A         |  110 | B         |  150 |
+------+-----------+------+-----------+------+
4 rows in set (0.02 sec)

Answer 8

你可以试试，估计不会比直接运行快
create table lsb as
select s.f_Name, 'A' as f_SubjectA, 
    sum(if(f_Subject='A',f_Score,0) as f_SumA, 
    'B' as f_SubjectB, 
    sum(if(f_Subject='A',f_Score,0) as f_SumB 
from t_Student s left join t_Score c on s.f_StudentID=c.f_StudentID 
group by s.f_Name

Answer 9

select f_name,f_subject,count(f_score) from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='A'group by f_name

Answer 10

select f_name,f_subject,count(f_score) from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='B'group by f_name 这两个结果集out join就可以了

Answer 11

select a.f_name,if(a.f_subject is null,'A',a.f_subject) as f_subjectA,if(a.c is null,0,a.c) as f_scoreA,if(b.f_subject is null,'B',b.f_subject) as f_subjectB,if(b.c is null,0,b.c) as f_scoreB 
from
(select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='A'group by f_name) a
left join
(select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='B'group by f_name) b
on a.f_name=b.f_name
union
select b.f_name,if(a.f_subject is null,'A',a.f_subject ) as f_subjectA,if(a.c is null,0,a.c) as f_scoreA,if(b.f_subject is null,'B',b.f_subject )as f_subjectB,if(b.c is null,0,b.c) as f_scoreB 
from
(select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='A'group by f_name) a
right join
(select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where t.f_studentid=t1.f_studentid and f_subject='B'group by f_name) b
on a.f_name=b.f_name
;
mysql> select a.f_name,if(a.f_subject is null,'A',a.f_subject) as f_subjectA,if(
a.c is null,0,a.c) as f_scoreA,if(b.f_subject is null,'B',b.f_subject) as f_subj
ectB,if(b.c is null,0,b.c) as f_scoreB
    -> from
    -> (select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where
 t.f_studentid=t1.f_studentid and f_subject='A'group by f_name) a
    -> left join
    -> (select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where
 t.f_studentid=t1.f_studentid and f_subject='B'group by f_name) b
    -> on a.f_name=b.f_name
    -> union
    -> select b.f_name,if(a.f_subject is null,'A',a.f_subject ) as f_subjectA,if
(a.c is null,0,a.c) as f_scoreA,if(b.f_subject is null,'B',b.f_subject )as f_sub
jectB,if(b.c is null,0,b.c) as f_scoreB
    -> from
    -> (select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where
 t.f_studentid=t1.f_studentid and f_subject='A'group by f_name) a
    -> right join
    -> (select f_name,f_subject,sum(f_score) c from t_student t,t_score t1 where
 t.f_studentid=t1.f_studentid and f_subject='B'group by f_name) b
    -> on a.f_name=b.f_name
    -> ;
+----------+------------+----------+------------+----------+
| f_name   | f_subjectA | f_scoreA | f_subjectB | f_scoreB |
+----------+------------+----------+------------+----------+
| WangWu   | A          |      140 | B          |        0 |
| ZhangSan | A          |      130 | B          |       60 |
| ZhaoLiu  | A          |      110 | B          |      150 |
| LiSi     | A          |        0 | B          |      130 |
+----------+------------+----------+------------+----------+
4 rows in set (0.00 sec)
没想到这两个union代码这么长