Home > Database > Mysql Tutorial > MySQL数据库中如何解决分组统计的问题_MySQL

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

WBOY
Release: 2016-06-01 14:00:22
Original
1037 people have browsed it


首先准备四张表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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template