SQL列在同一表中进行比较(自我联接) [英] SQL Column compare in the same table (self-join)

问题描述

我需要一个提示才能解决此SQL(自连接)问题:

I need a hint in order to solve this SQL (self-join) problem:

具有列value和category

 id    ||  value || category || foo
------------------------------------
 1     ||  1     || a        || 1
 2     ||  2     || a        || 4
 3     ||  3     || a        || 2
 4     ||  0     || b        || 2
 5     ||  1     || b        || 1
 6     ||  2     || b        || 4
 7     ||  3     || b        || 2
 8     ||  4     || b        || 2
 9     ||  5     || b        || 1
10     ||  5     || b        || 4
11     ||  6     || b        || 2
12     || 99     || z        || 2

我想比较类别b中的所有值和类别a中的所有值，并获取所有b中而不是a或其id中的值，所以:

I would like to compare all values from category b and all values from category a and get all values that are in b and not in a or their id, so:

(0,1,2,3,4,5,5,6)比较"(1,2,3)=>(0,4,5,5,6)

(0,1,2,3,4,5,5,6) "compare" (1,2,3) => (0,4,5,5,6)

推荐答案

ANSI SQL:

SELECT
*
FROM
tbl
WHERE
category = 'b'
AND value NOT IN (SELECT value FROM tbl WHERE category = 'a')

在此处观看.

这篇关于SQL列在同一表中进行比较(自我联接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！