Mysql排除记录 [英] Mysql exclude records

问题描述

我有两个表，用户和角色，一个用户可以有多个角色

<块引用>

用户:ID |名字 |姓氏 |等等..1 |彼得 |布隆普 |角色:ID |罗莱德 |USERID(即用户 ID)70 |5 |1(彼得)71 |2 |1

我必须做但无法弄清楚的是，如何检索角色标识不是的用户的数据甚至到某个整数，对于.前任.用户 PETER 可以拥有角色 ID 5 和 2，我想得到的是，如果彼得的角色 ID 为 3，他会从结果集中排除，无论他是否有角色 ID 5.

解决方案

SELECT用户 ID、用户名、用户名从用户在哪里用户 ID 不在 (SELECT ID FROM role WHERE role.RoleID = '3')

这使用 MySQL 中所谓的 子查询.WHERE 子句中的子查询将选择 RoleID 为 3 的所有 ID(例如 Peter).然后使用 NOT IN()，来自用户的选择.

I have two tables, user and role, one user can have more than 1 role

user: ID | FIRSTNAME | LASTNAME | etc..
       1 | PETER     | Blomp    | 


role: ID |  ROLEID   | USERID (which is user ID)
      70 |    5      |    1 (peter)
      71 |    2      |    1

What I have to do and cant figure out is, how can retrieve data of users whos roleid is not even to some integer, for. ex. user PETER can have roleID's 5 and 2, what i am trying to get is that IF Peter has roleid 3, he excludes from resultset, no matter if he has roleid 5.

解决方案

SELECT
    user.ID, user.FirstName, user.LastName
FROM
    user
WHERE
    user.ID NOT IN (
                    SELECT ID FROM role WHERE role.RoleID = '3'
                   )

This uses whats known as a subquery in MySQL. The subquery in the WHERE clause will select all the IDs (such as Peter) who have a RoleID of 3. It will then exclude those ID's (Peter), using NOT IN(), from the selection of users.

这篇关于Mysql排除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！