How to solve grouping statistics in MySQL database _ MySQL

How to solve the problem of grouping statistics in the MySQL database first, prepare four tables A, B, C, and D,

------------------------------ A | B | C | D a B | a c | a d | d e 1 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 --------------------------------

Next we need to use an SQL statement to calculate count for all the columns in Table A and the columns associated with field A in Table B through Table C, associate Table D with table A. The result is as follows:

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

Mysql statement solution:

Select. 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, 3 as c union allselect 2 as a, 3 as c) B group by a) as Bon. a = B. a join (select 1 as a, 'A' as d union allselect 2 as A, 'B' as d union allselect 3 as, 'C' as d union allselect 4 as a, 'D' as D) as C on. a = C. a join (select 1 as a, 'A' as d union allselect 2 as A, 'B' as d union allselect 3 as, 'C' as d union allselect 4 as a, 'D' as D) as Don d. d = C. d