Heim >Datenbank >MySQL-Tutorial >MySQL数据库中如何解决分组统计的问题_MySQL

MySQL数据库中如何解决分组统计的问题_MySQL

WBOY
WBOYOriginal
2016-06-01 14:00:221033Durchsuche


首先准备四张表A、B、C、D,

--------------------------------     A      | B  | C   | D     a   b   |   a   c   |   a   d   |   d   e     1   1   |   1   1   |   1   A   |   A   1     2   2   |   1   3   |   2   B   |   B   2     3   4   |   1   4   |   3   C   |   C   3     4   6   |   2   1   |   4   D   |   D   4     --------------------------------
 


下面我们需要用一条sql语句将A表所有的列,B表对与A表a字段相关联的列求count,通过C表,将D表与A表关联起来,得到的结果如下:

1   1   3   1     2   2   1   2     3   4   0   3     4   6   0   4
 
 

Mysql语句解决:

select A.a,A.b,IFNULL(c,0) as c,D.a from (select 1 as a,2 as b union allselect 2 as a,2 as b union allselect 3 as a,4 as b union allselect 4 as a,6 as b) as A left outer join     (select a,count(a) as c from(select 1 as a,3 as c union allselect 1 as a,3 as c union allselect 1 as a,3 as c union allselect 2 as a,3 as c ) B group by a) as Bon A.a=B.a join (select 1 as a,'A' as d union allselect 2 as a,'B' as d union allselect 3 as a,'C' as d union allselect 4 as a,'D' as d ) as C on A.a=C.a join(select 1 as a,'A' as d union allselect 2 as a,'B' as d union allselect 3 as a,'C' as d union allselect 4 as a,'D' as d ) as Don D.d=C.d

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn