关于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

结果：

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