mysql - 在 MYSQL 中的两个或多个临时表的表末尾显示总和/总计

Question

我得到了两个临时表，如下所示:

Tmp1:

 groups______active_members
   a              2
   b              3
   c              7

Tmp2:

 groups_______participants
   a              1
   b              2
   c              4

我用看起来像这样的代码加入了他们:

SELECT
  tmp1.group AS groups,
  tmp1.active AS active_members,
  tmp2.participiants 
    FROM(
      (SELECT  name AS 'group',
       COUNT(`id`) AS 'active'
       FROM table1
      ) tmp1
      INNER JOIN
      (SELECT  name AS 'group',
       COUNT(`id`) AS 'participiants'   
       FROM table2
      ) tmp2 
ON tmp1.group=tmp2.group)   

得到的结果是这样的:

 groups___active_members___participants
   a             2             1
   b             3             2
   c             7             4

我现在想要这样的结果:

 groups___active_members___participants
   a             2             1
   b             3             2
   c             7             4
 Total          12             7

请帮忙!!我也尝试过使用 UNION 和 WITH ROLLUP，但作为新手，我变得一无所知。非常感谢任何帮助。

Answer 1

SQL

SELECT tbl1.groups, tbl1.active_members, tbl2.participants
FROM tbl1
LEFT JOIN tbl2 ON tbl1.groups = tbl2.groups
UNION ALL
SELECT "Total", SUM(active_members), SUM(participants) 
FROM (SELECT tbl1.groups, tbl1.active_members, tbl2.participants
      FROM tbl1
      LEFT JOIN tbl2 ON tbl1.groups = tbl2.groups) AS tmptbl;

舍玛:

CREATE TABLE IF NOT EXISTS `tbl1` (
`id` int(11) NOT NULL,
  `groups` char(11) NOT NULL,
  `active_members` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
`id` int(11) NOT NULL,
  `groups` char(11) NOT NULL,
  `participants` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

结果