根据其他列条件选择记录

时间：2022-12-01 07:55:12

I have a SQL table structure like:

我有一个SQL表结构,如:

ID UID  Name   PId  Date
1   2   Admin   1   2015-04-25 21:10:40.060
3   28  Admin   1   2015-04-25 21:10:53.873
4   3   Viewer  1   2015-04-25 22:49:12.497
5   7   Admin   1   2015-04-25 22:49:34.670
6   28  Admin   2   2015-05-04 10:41:17.000

ID-> autoincrement, UID->UserId, PID-> projectId

Here a user can be assigned to one or multiple projects. So, I have user 28 assigned to two projects PID:1 and 2.

这里可以将用户分配给一个或多个项目。因此,我将用户28分配给两个项目PID:1和2。

I want all the unassigned users to project 2. (i.e my result should contain UID 2, 3 and 7). And for project 1 it would be none.

我希望所有未分配的用户都进行项目2.(即我的结果应该包含UID 2,3和7)。对于项目1,它将是没有。

Any help would be appreciated.

任何帮助,将不胜感激。

EDIT

This is what I tried:

这是我试过的:

select UId 
from A 
group where PId!=2 by uId having COUNT(*)<2

and the data I am getting is:

我得到的数据是:

   UID  PID
    2   1
    3   1
    7   1
    28  1

The last record UID=28 is unwanted row

最后一条记录UID = 28是不需要的行

1 个解决方案

#1

You can use NOT IN operator in your WHERE clause

您可以在WHERE子句中使用NOT IN运算符

SELECT *
FROM yourtableName
WHERE UID NOT IN (SELECT UID
                  FROM #test
                  WHERE PID = 2)

SQLFiddle

#1

You can use NOT IN operator in your WHERE clause

您可以在WHERE子句中使用NOT IN运算符

SELECT *
FROM yourtableName
WHERE UID NOT IN (SELECT UID
                  FROM #test
                  WHERE PID = 2)

SQLFiddle

标签：

相关文章