Mysql 多对多关系查询

Mysql 多对多关系查询统计@TOC

有三张表：

1. user（用户表，sex=0表示男生，1表示女生）
2. ach（成就表）
3. user_ach（关系表）

每个用户可以获得多个成就，每个成就对应多个用户。用一条sql查询出每个成就下男生和女生的人数。

user表：

CREATE TABLE `user` (
	`id` int(11) NOT NULL,
	`name` varchar(45) DEFAULT NULL,
	`sex` tinyint(1) DEFAULT NULL,
	  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4';

ach表：

CREATE TABLE `ach` (
    `id` int(11) NOT NULL,
    `ach_name` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4';

user_ach表：

CREATE TABLE `user_ach` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `ach_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_idx` (`user_id`),
  KEY `fk_ach_idx` (`ach_id`),
  CONSTRAINT `fk_ach` FOREIGN KEY (`ach_id`) REFERENCES `ach` (`id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询语句：

select 
	t.ach_name,
	max(case t.sex when 0 then t.num else 0 end) "男生",
    max(case t.sex when 1 then t.num else 0 end) "女生"
from
	(
	select b.ach_name ach_name,a.sex sex,count(c.ach_id) num
	from user a,ach b,user_ach c 
	where a.id = c.user_id and b.id = c.ach_id
    group by c.ach_id,a.sex) t
group by t.ach_name
order by ach_name;