创建一个虚拟列以验证其他数据是否存在 [英] Create a virtual column to verify if data exists on anothers

问题描述

mt_user :

usr_id               usr_login          usr_passwd
  1                   user1               user1
  2                   user2               user2
  3                   user3               user3
  4                   user4               user4

mt_settings :

sttgs_id                        sttgs_description
   1                                 Setting 1
   2                                 Setting 2
   3                                 Setting 3
   4                                 Setting 4
   5                                 Setting 5

mt_user_settings :

uss_id               usr_id                   sttgs_id
  1                    1                         1
  2                    1                         2
  3                    2                         5
  4                    3                         2
  5                    3                         3
  6                    3                         5
  7                    4                         4

我需要显示的是这样的数据:

What I need to show is data like this:

WHERE usr_id='2':

sttgs_id                   sttgs_description                 has it? (virtual_column)
   1                          Setting 1                              0
   2                          Setting 2                              0
   3                          Setting 3                              0
   4                          Setting 4                              0
   5                          Setting 5                              1

WHERE usr_id='3':

sttgs_id                   sttgs_description                 has it? (virtual_column)
   1                          Setting 1                              0
   2                          Setting 2                              1
   3                          Setting 3                              1
   4                          Setting 4                              0
   5                          Setting 5                              1

我该怎么做呢?

推荐答案

SELECT s.sttgs_id, s.sttgs_description, IF(st.uss_id,1,0) as `has it`
FROM mt_user u
JOIN mt_settings s
LEFT JOIN mt_user_settings us ON us.usr_id = u.usr_id AND us.sttgs_id = s.sttgs_id
WHERE u.usr_id = 2

您也可以在没有用户表的情况下完成此操作

you can also do it without the users table

SELECT s.sttgs_id, s.sttgs_description, IF(st.uss_id,1,0) as `has it`
FROM  mt_settings s
LEFT JOIN mt_user_settings us ON us.usr_id = 2 AND us.sttgs_id = s.sttgs_id

这篇关于创建一个虚拟列以验证其他数据是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！