代码之家 › 专栏 › 技术社区 › Tutu Kaeen

搜索一个列值,该列值包含多个具有不同指定值的记录

postgresql sql

-1

Tutu Kaeen · 技术社区 · 8 年前

我有一个数据库:

|id|surname|name
| 1|Smith  |John
| 2|Smith  |Mike
| 3|Smith  |Bob
| 4|Knope  |John
| 5|Knope  |Mike
| 6|Knope  |Dick
| 7|Pratt  |John
| 8|Pratt  |Jill
| 9|Pratt  |James

我想找到一个有约翰、迈克和鲍勃的姓氏。我想把它还给史密斯。或者我想寻找一个有约翰和迈克的家庭,我想让它归还史密斯和诺普。我该怎么做?

我想要得到的结果如上所述,但对于更好的形式,这里是相同的: 我在找有乔恩、迈克和鲍勃的家庭。我想得到:

|surname|
|Smith  |

然后我只想找约翰和迈克,我想:

|surname|
|Smith  |
|Knope  |

4 回复 | 直到 8 年前

SqlKindaGuy 8 年前

根据你的解释,你可以这样做:

declare @table table (it int, surename varchar(50),name1 varchar(50))

insert into @table

values
(1,'Smith','John'),
(2,'Smith','Mike'),
(3,'Smith','Bob' ),
(4,'Knope','John'),
(5,'Knope','Mike'),
(6,'Knope','Dick'),
(7,'Pratt','John'),
(8,'Pratt','Jill'),
(9,'Pratt','James')

select * from @table
where name1 in ('john','mike','bob') and surename = 'smith'

union 

select * from @table
where name1 in('john','mike') and surename in ('smith','knope')

后果

George Menoutis 8 年前

您的输入动态地具有多个值。因此,我假设您已将希望该族包含的所有名称放入带有字段“name”的表#I中。然后,需要输入中没有指定名称的所有族。

select *
from (select distinct surename from yourtable)surnames
where not exists
(
    select 1 from #i
    where not exists
    (
        select 1 
        from yourtable t
        where 
            t.surename=surnames.surename
            and #i.name=t.name
    )
)

Nugsson 8 年前

这在SQL Server中有效——在您的问题被PostgreSQL标记之前编写。

设置测试数据:

DECLARE @Names TABLE (ID INTEGER IDENTITY, Surname VARCHAR(50), Forenames VARCHAR(50));

INSERT
    @Names (Surname, Forenames)
VALUES
    ('Smith', 'John'),
    ('Smith', 'Mike'),
    ('Smith', 'Bob' ),
    ('Knope', 'John'),
    ('Knope', 'Mike'),
    ('Knope', 'Dick'),
    ('Pratt', 'John'),
    ('Pratt', 'Jill'),
    ('Pratt', 'James');

声明一个包含要匹配的名字的表变量。这是一个参数,因此您应该编辑我们插入的值以测试结果:

DECLARE @ForenamesToSearch TABLE (Forenames VARCHAR(50));

INSERT
    @ForenamesToSearch
VALUES
    ('John')
    , ('Mike')
    , ('Bob');

最后,我们使用GROUP BY和HAVING COUNT来确保名称的数量完全匹配。

SELECT
    Surname
FROM
    (SELECT DISTINCT Forenames, Surname FROM @Names) Names
    INNER JOIN @ForenamesToSearch Forenames ON Names.Forenames = Forenames.Forenames
GROUP BY
    Surname
HAVING
    COUNT(1) = (SELECT COUNT(1) FROM @ForenamesToSearch);

Ankur Patel 8 年前

这可能不是最好的方法,但您可以为Postgresql尝试以下方法:

select * 
from
(
select 
    concat(',' , string_agg(name1,',') , ',') as X,
    surname
from 
    table_name as A
group BY 
    surname
) As B
Where B.X like '%,John,%' And B.X like '%,Mike,%' And B.X like '%,Bob,%';

SQLFIDDLE DEMO

以下内容适用于SQL server:

select * from
(
select 
    ', ' + STUFF((SELECT ', ' + name1 FROM table_name WHERE surname = A.surname FOR XML PATH('')),1,2,'') + ',' as X,
    surname
from 
    table_name as A
group BY 
    surname
) as B
Where B.X like '%, John,%' And B.X like '%, Mike,%' And B.X like '%, Bob,%';

SQLFIDDLE DEMO