Mysql从多个表中查询选择数据并进行比较

时间:2021-11-08 01:08:41

I have a three tables namely profile, academic,payment and these tables having two same columns that are username and status.

我有三个表,即个人资料,学术,付款和这些表有两个相同的列,用户名和状态。

my problem is how to select username from the tables where status=1 in all the tables

我的问题是如何从所有表中status = 1的表中选择用户名

1 个解决方案

#1


Typically it works like this:

通常它的工作原理如下:

SELECT * FROM profile
  LEFT JOIN academic ON profile.username=academic.username
  LEFT JOIN payment ON profile.username=payment.username
  WHERE profile.status=1 AND academic.status=1 AND payment.status=1

As a note having username as a key is usually a bad thing, often super bad since if someone's able to change their name you need to update N other tables. You may have a circumstance where you forget to update one or more tables, then subsequently someone registers with the former name and "inherits" this data.

因为将用户名作为密钥的注释通常是一件坏事,通常非常糟糕,因为如果有人能够更改其名称,则需要更新N个其他表。您可能遇到忘记更新一个或多个表的情况,然后有人注册了以前的名称并“继承”了这些数据。

It's also typically very inefficient to use a string INDEX key when a user_id integer value would suffice.

当user_id整数值足够时,使用字符串INDEX键通常也是非常低效的。

#1


Typically it works like this:

通常它的工作原理如下:

SELECT * FROM profile
  LEFT JOIN academic ON profile.username=academic.username
  LEFT JOIN payment ON profile.username=payment.username
  WHERE profile.status=1 AND academic.status=1 AND payment.status=1

As a note having username as a key is usually a bad thing, often super bad since if someone's able to change their name you need to update N other tables. You may have a circumstance where you forget to update one or more tables, then subsequently someone registers with the former name and "inherits" this data.

因为将用户名作为密钥的注释通常是一件坏事,通常非常糟糕,因为如果有人能够更改其名称,则需要更新N个其他表。您可能遇到忘记更新一个或多个表的情况,然后有人注册了以前的名称并“继承”了这些数据。

It's also typically very inefficient to use a string INDEX key when a user_id integer value would suffice.

当user_id整数值足够时,使用字符串INDEX键通常也是非常低效的。