依靠具有相同标识符的多个表mysql(Count on multiple tables mysql with same identificator)

依靠具有相同标识符的多个表mysql(Count on multiple tables mysql with same identificator)

我正试图在四张桌子上做一些计数，我正在努力做到这一点！

这是我的表脚本（4个简单的表，相同的表）

CREATE TABLE `TableA` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableA` (`id`, `zone`) VALUES (1, 'ca'),(2, 'ca'),(3, 'fr'),(4, 'ca'),(5, 'ca'),(6, 'fr'); CREATE TABLE `TableB` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableB` (`id`, `zone`) VALUES (1, 'ca'),(2, 'it'),(3, 'de'),(4, 'ca'),(5, 'it'),(6, 'fr'); CREATE TABLE `TableC` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableC` (`id`, `zone`) VALUES (1, 'ca'),(2, 'ma'),(3, 'fr'),(4, 'pl'),(5, 'usa'),(6, 'fr'); CREATE TABLE `TableD` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableD` (`id`, `zone`) VALUES (1, 'ca'),(2, 'pl'),(3, 'it'),(4, 'pl'),(5, 'ca'),(6, 'it');

这是我到目前为止所尝试的

SELECT DISTINCT Zone, TableAa, TableBb, TableCc, TableDd FROM ( SELECT DISTINCT Ta.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableA Ta LEFT JOIN TableB Tb ON Ta.zone = Tb.zone LEFT JOIN TableC Tc ON Ta.zone = Tc.zone LEFT JOIN TableD Td ON Ta.zone = Td.zone GROUP BY Ta.zone UNION SELECT DISTINCT Tb.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableB Tb LEFT JOIN TableA Ta ON Tb.zone = Ta.zone LEFT JOIN TableC Tc ON Tb.zone = Tc.zone LEFT JOIN TableD Td ON Tb.zone = Td.zone GROUP BY Tb.zone UNION SELECT DISTINCT Tc.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableC Tc LEFT JOIN TableB Tb ON Tc.zone = Tb.zone LEFT JOIN TableA Ta ON Tc.zone = Ta.zone LEFT JOIN TableD Td ON Tc.zone = Td.zone GROUP BY Tc.zone UNION SELECT DISTINCT Td.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableA Td LEFT JOIN TableB Tb ON Td.zone = Tb.zone LEFT JOIN TableC Tc ON Td.zone = Tc.zone LEFT JOIN TableA Ta ON Td.zone = Ta.zone GROUP BY Td.zone ) A ORDER BY Zone ;

我有一些结果，但结果不连贯。

我应该

zone | TableAa | TableBb | TableCc | TableDd fr | 2 | 1 | 2 | 0 ca | 4 | 2 | 1 | 2 it | 0 | 2 | 0 | 2 de | 0 | 1 | 0 | 0 ma | 0 | 0 | 1 | 0 pl | 0 | 0 | 1 | 2 usa | 0 | 0 | 1 | 0

相反，我有

zone | TableAa | TableBb | TableCc | TableDd ca | 16 | 16 | 16 | 16 ca | 32 | 32 | 32 | 32 de | 0 | 1 | 0 | 0 fr | 4 | 4 | 4 | 0 fr | 8 | 8 | 8 | 8 it | 0 | 4 | 0 | 4 ma | 0 | 0 | 1 | 0 pl | 0 | 0 | 2 | 2 usa | 0 | 0 | 1 | 0

结果完全是假的，我真的不知道为什么！ 这是一个用于测试的SQL小提琴： http ：//sqlfiddle.com/#！9/1cc0ab / 3

需要任何帮助。

谢谢。

PS：我正在使用mysql

I am trying to do some counts on four tables, I am struggling doing it !

Here are my table script (4 simple tables, same tables)

CREATE TABLE `TableA` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableA` (`id`, `zone`) VALUES (1, 'ca'),(2, 'ca'),(3, 'fr'),(4, 'ca'),(5, 'ca'),(6, 'fr'); CREATE TABLE `TableB` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableB` (`id`, `zone`) VALUES (1, 'ca'),(2, 'it'),(3, 'de'),(4, 'ca'),(5, 'it'),(6, 'fr'); CREATE TABLE `TableC` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableC` (`id`, `zone`) VALUES (1, 'ca'),(2, 'ma'),(3, 'fr'),(4, 'pl'),(5, 'usa'),(6, 'fr'); CREATE TABLE `TableD` ( `id` int(45) NOT NULL, `zone` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `TableD` (`id`, `zone`) VALUES (1, 'ca'),(2, 'pl'),(3, 'it'),(4, 'pl'),(5, 'ca'),(6, 'it');

Here is what I have tried so far

SELECT DISTINCT Zone, TableAa, TableBb, TableCc, TableDd FROM ( SELECT DISTINCT Ta.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableA Ta LEFT JOIN TableB Tb ON Ta.zone = Tb.zone LEFT JOIN TableC Tc ON Ta.zone = Tc.zone LEFT JOIN TableD Td ON Ta.zone = Td.zone GROUP BY Ta.zone UNION SELECT DISTINCT Tb.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableB Tb LEFT JOIN TableA Ta ON Tb.zone = Ta.zone LEFT JOIN TableC Tc ON Tb.zone = Tc.zone LEFT JOIN TableD Td ON Tb.zone = Td.zone GROUP BY Tb.zone UNION SELECT DISTINCT Tc.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableC Tc LEFT JOIN TableB Tb ON Tc.zone = Tb.zone LEFT JOIN TableA Ta ON Tc.zone = Ta.zone LEFT JOIN TableD Td ON Tc.zone = Td.zone GROUP BY Tc.zone UNION SELECT DISTINCT Td.zone AS Zone, COUNT( Ta.id ) AS TableAa, COUNT( Tb.id ) AS TableBb, COUNT( Tc.id ) AS TableCc, COUNT( Td.id ) AS TableDd FROM TableA Td LEFT JOIN TableB Tb ON Td.zone = Tb.zone LEFT JOIN TableC Tc ON Td.zone = Tc.zone LEFT JOIN TableA Ta ON Td.zone = Ta.zone GROUP BY Td.zone ) A ORDER BY Zone ;

I have some results, but incoherent results.

I should have

zone | TableAa | TableBb | TableCc | TableDd fr | 2 | 1 | 2 | 0 ca | 4 | 2 | 1 | 2 it | 0 | 2 | 0 | 2 de | 0 | 1 | 0 | 0 ma | 0 | 0 | 1 | 0 pl | 0 | 0 | 1 | 2 usa | 0 | 0 | 1 | 0

Instead, I have

zone | TableAa | TableBb | TableCc | TableDd ca | 16 | 16 | 16 | 16 ca | 32 | 32 | 32 | 32 de | 0 | 1 | 0 | 0 fr | 4 | 4 | 4 | 0 fr | 8 | 8 | 8 | 8 it | 0 | 4 | 0 | 4 ma | 0 | 0 | 1 | 0 pl | 0 | 0 | 2 | 2 usa | 0 | 0 | 1 | 0

The result are completely false, I really don't know why ! Here is an SQL Fiddle for testing : http://sqlfiddle.com/#!9/1cc0ab/3

Any help is needed.

Thanks.

PS : I am using mysql

最满意答案

您需要稍微分离代码并分别计算每个总和，然后加入区域列表。

SELECT T.ZONe,IFNULL(A.TableAa,0) AS TableAa,IFNULL(B.TableBb,0) AS TableBb,IFNULL(C.TableCc,0) AS TableCc,IFNULL(D.TableDd,0) AS TableDd FROM ( SELECT DISTINCT Ta.zone AS Zone FROM TableA Ta UNION SELECT DISTINCT Tb.zone AS Zone FROM TableB Tb UNION SELECT DISTINCT Tc.zone AS Zone FROM TableC Tc UNION SELECT DISTINCT Td.zone AS Zone FROM TableD Td ) T LEFT JOIN ( SELECT Zone, COUNT(*) AS TableAa FROM TableA GROUP BY Zone ) A ON A.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableBb FROM TableB GROUP BY Zone ) B ON B.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableCc FROM TableC GROUP BY Zone ) C ON C.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableDd FROM TableD GROUP BY Zone ) D ON D.Zone=T.ZOne ;

SQL小提琴

You need to separate the code a bit and calculate each sum separatly and then join on the list of zones.

SELECT T.ZONe,IFNULL(A.TableAa,0) AS TableAa,IFNULL(B.TableBb,0) AS TableBb,IFNULL(C.TableCc,0) AS TableCc,IFNULL(D.TableDd,0) AS TableDd FROM ( SELECT DISTINCT Ta.zone AS Zone FROM TableA Ta UNION SELECT DISTINCT Tb.zone AS Zone FROM TableB Tb UNION SELECT DISTINCT Tc.zone AS Zone FROM TableC Tc UNION SELECT DISTINCT Td.zone AS Zone FROM TableD Td ) T LEFT JOIN ( SELECT Zone, COUNT(*) AS TableAa FROM TableA GROUP BY Zone ) A ON A.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableBb FROM TableB GROUP BY Zone ) B ON B.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableCc FROM TableC GROUP BY Zone ) C ON C.Zone=T.ZOne LEFT JOIN ( SELECT Zone, COUNT(*) AS TableDd FROM TableD GROUP BY Zone ) D ON D.Zone=T.ZOne ;

SQL Fiddle