Question

Here is a table with id and permission as composite primary key, which means one can have some permissions.

id    permission
1      A 
1      B 
2      A 
3      B

现在我想选择符合某些条件的<代码>id。例如:

id WITH permission A AND B .
id WITH permission A AND WITHOUT permission B .

I ve come up with a kind of sql which uses exists clause.

:

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission =  A )
AND EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission =  B );

:

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission =  A )
AND NOT EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission =  B );

它虽然运作良好,但对于大型表格而言,进展缓慢,因为它为每个记录翻了一番。因此,我想知道,实现我的目标是否有更快、更清楚的途径。

Answer 1

页: 1 样本如下:

SELECT id
FROM permissions
WHERE permission IN ( A ,  B )
GROUP BY id
HAVING COUNT(DISTINCT permission) = 2;

也可使用<代码>NOT IN。样本如下:

SELECT DISTINCT id
FROM permissions
WHERE permission =  A 
AND id NOT IN (
    SELECT id
    FROM permissions
    WHERE permission =  B 
);

这应当比你最初的询问更快,即存在盘问和(或)存在两次。这里是fiddlelink 。

友情链接