There is a problem with the query results when Mysql joins multiple tables-PHP Tutorial-php.cn

There is a problem with the query results when Mysql joins multiple tables

WBOY

Release： 2016-09-29 09:19:06

Original

1639 people have browsed it

Due to the needs of the company's products, we now need to implement a function. Table A stores the basic information of users, and Table B stores some behaviors of users in Table A. Table C and Table B have the same properties. When querying, we can sort by the count results in table B or table C, so we thought of join, but there was a problem.

First post the data structure of the three tables

<code>  CREATE TABLE `A` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=1;</code>

Copy after login

<code>  CREATE TABLE `B` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) default NULL,
  `dosomething` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `userid` USING BTREE (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=1;</code>

Copy after login

<code>  CREATE TABLE `C` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) default NULL,
  `dosomething` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `userid` USING BTREE (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=1;</code>

Copy after login

Try it yourself and find that the query results are different

<code>SELECT u.id, COUNT(s.id) AS sapply, COUNT(uu.id) AS ftotal FROM A AS u
 RIGHT JOIN B AS s ON u.id = s.userid
 RIGHT JOIN C AS uu ON u.id = `uu`.`userid`
 GROUP BY `u`.`id` ORDER BY `ftotal` DESC LIMIT 10</code>

Copy after login

There is a problem with the query results when Mysql joins multiple tables

There is obviously a problem with the data, please correlate it separately to see the results

<code>SELECT u.id, COUNT(s.id) AS sapply FROM A AS u
 RIGHT JOIN B AS s ON u.id = s.userid
 GROUP BY `u`.`id` ORDER BY `sapply` DESC LIMIT 10</code>

Copy after login

There is a problem with the query results when Mysql joins multiple tables

<code>SELECT u.id, COUNT(uu.id) AS ftotal FROM A AS u
 RIGHT JOIN C AS uu ON u.id = uu.userid
 GROUP BY `u`.`id` ORDER BY `ftotal ` DESC LIMIT 10</code>

Copy after login

There is a problem with the query results when Mysql joins multiple tables