关于MYSQL多对多查询的一道面试题……

问：

现有用户表A，和用户标签表B，现要达成多对多关联，还需要什么？用SQL语句尝试查出每个用户标签下的男生，女生分别有多少人？

答：

1.需要关联表C，记录A表和B表的关联关系

2.

A：users

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `sex` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

B:tabs

CREATE TABLE `tabs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `tab_name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

C:users_tabs

CREATE TABLE `users_tabs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL,
  `tab_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

 

SQL1:

SELECT
	a.tab_name,
	IFNULL(a.女, 0) AS 女,
	IFNULL(b.男, 0) AS 男
FROM
	(
		SELECT
			tab.tab_name,
			user.`name`,
			user.sex,
			ut.*, count(user.sex) AS 女
		FROM
			users_tabs AS ut
		LEFT JOIN tabs AS tab ON ut.tab_id = tab.id
		LEFT JOIN users AS user ON user.id = ut.user_id
		WHERE
			user.sex = 0
		GROUP BY
			tab.tab_name
	) AS a
LEFT JOIN (
	SELECT
			tab.tab_name,
			user.`name`,
			user.sex,
			ut.*, count(user.sex) AS 男
		FROM
			users_tabs AS ut
		LEFT JOIN tabs AS tab ON ut.tab_id = tab.id
		LEFT JOIN users AS user ON user.id = ut.user_id
		WHERE
			user.sex = 1
		GROUP BY
			tab.tab_name
) AS b ON a.tab_id = b.tab_id

SQL2：

SELECT
	tab.tab_name,
	COUNT( DISTINCT IF (user.sex = 1, user.id, NULL) ) 男,
	COUNT( DISTINCT IF (user.sex = 0, user.id, NULL) ) 女
FROM
	users_tabs ut
LEFT JOIN `users` user ON ut.user_id = user.id
LEFT JOIN tabs tab ON ut.tab_id = tab.id
GROUP BY
	tab.id

结果：

不知道还有没有什么更好的方式，有知道的请留言！