使用关系表MS-SQL筛选表

I need to filter a table with relation table in a MS-SQL database.

我需要过滤一个MS-SQL数据库中的关系表。

I get:

我得到:

persId  persName1   catId

 4        Hans        0
 4        Hans        51
 4        Hans        73
 5        Torleif     0
 5        Torleif     5
 5        Torleif    73
 5        Torleif    76
 6        Peter       0
 6        Peter      51
 6        Peter      73
 7        Jonas       0
 7        Jonas      16
 7        Jonas      73

with:

:

SELECT   distinct  tblPerson.persId
, tblPerson.persName1
, relCatPers.catId
FROM         tblPerson LEFT OUTER JOIN
             relCatPers ON tblPerson.persId = relCatPers.persId

---

But i need. Remove those with catId 51. And only 1 occurrence of a name and Id:

但我所需要的。去掉catId 51。只有一个名称和Id出现:

persId  persName1

  5         Torleif
  7         Jonas

Table:

表:

CREATE TABLE [dbo].[tblPerson](
    [persId] [int] IDENTITY(1,1) NOT NULL,
    [persName1] [varchar](255) NULL,
CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED 
)


CREATE TABLE [dbo].[relCatPers](
    [rcpId] [int] IDENTITY(1,1) NOT NULL,
    [catId] [int] NOT NULL,
    [persId] [int] NOT NULL,
CONSTRAINT [PK_relCatPers] PRIMARY KEY CLUSTERED
)

3 个解决方案

#1

Try this:

试试这个:

Fiddle 1 demo here

小提琴1演示

select distinct p.persId, p.persName1 
from   tblPerson p left join 
         relCatPers c on p.persId = c.persId
where  p.persId not in
       (select persId from relCatPers where catId = 51)

Or you can ignore relCatPers table and do like below

或者您可以忽略relCatPers表，如下所示

Fiddle 2 demo here

小提琴2演示

select p.persId, p.persName1 
from   tblPerson p 
where  p.persId not in
       (select persId from relCatPers where catId = 51)

#2

Try this :

试试这个:

With cte as 
(Select persId,persName1,
        row_number() over (partition by persId,persName1 order by persID) as rn
 from tblPerson 
)
Select cte.persID,cte.persName1
from relCatPers
left join cte
on tblPerson.persId = relCatPers.persId
where relCatPers.catid=51  and
rn=1

#3

I've edited the query so it does not return a catId, as defined in your question:

我编辑了查询，所以它不返回catId，如您的问题所定义:

SELECT distinct
       tblPerson.persId
       ,tblPerson.persName1
FROM   tblPerson
LEFT OUTER JOIN relCatPers ON tblPerson.persId = relCatPers.persId
WHERE  tblPerson.persId NOT IN 
       (SELECT persId FROM relCatPers WHERE catId = 51);

If you want to add additional catId's that should leave out persons, you can change the WHERE clause in the subquery to for example WHERE catId IN (16, 23, 51).

如果您想添加额外的catId，而catId应该省略person，您可以将子查询中的WHERE子句更改为catId所在的位置(16,23,51)。