令人难以置信的SQL困境

Question

很抱歉这个标题。我现在不能带来任何有意义的东西，但如果你有什么建议，我会更新它。

好吧，这个问题已经困扰我好几天了，快把我逼疯了。

我有一个数据库，用来保存与教育相关的过滤器。

table: FILTERS

filterid filtertype value
1        school     MIT
1        school     UT
1        school     Lund
1        major      econ
1        major      civil
1        graduate   1
2        school     Harvard

因此，这里我定义了两个过滤器，1和2。如果学校是(麻省理工学院、德克萨斯大学或隆德大学)之一，专业是(经济或民间)之一，并且是研究生课程，那么教育记录就会通过filter1。要想通过filter2考试，教育记录只需要有哈佛作为学校就可以了。

我还有另一个表，其中包含用户的教育记录。用户可以有多个教育记录(例如，一个用于她的本科学习，一个用于研究生学习)

table: EDUCATION
educid uid    school   major   graduate
1      1      MIT      econ    1
2      1      Lund     cs      0

因此，人员1有两个教育记录。这里，教育记录1通过了过滤器1，因为学校(MIT)属于集合{MIT，UT，Lund)，专业(econ)属于集合{econ，civil}，并且是研究生课程。但是，教育记录1无法通过过滤器2，因为学校是麻省理工学院，而不是哈佛大学。

教育记录2没有通过过滤器1，因为专业(cs)不在{econ，civil}中，也没有通过过滤器2，因为学校是隆德，而不是哈佛。

如果人员1的教育记录都没有通过筛选器1，则人员1不会通过筛选器1。因此，人员1通过筛选器1(因为她的第一个教育记录通过筛选器1)，但不会通过筛选器2，因为他的教育记录都没有通过筛选器2。

我的目标是选择person 1未通过的过滤器的filterid。

这就是我到目前为止所得到的：

SELECT filterid FROM filters
LEFT JOIN education ON education.uid=1 AND (
(filtertype='school' AND filters.value=education.school) OR
(filtertype='major' AND filters.value=education.major) OR
(filtertype='graduate' AND filters.value=education.graduate) 
)
GROUP BY filterid
HAVING ( SUM(educid IS NULL)>0 )

这显然不起作用，因为根据这个查询，如果她的一个教育记录没有通过过滤器1，那么person 1就不会通过过滤器1。(而如果她的任何记录都没有通过过滤器1，则应该是这样)。

我真的非常感谢任何解决这个问题的方法，因为它真的让我很困惑。

Answer 1

一种方法就是这样做

SELECT f.filterid
  FROM filters f 
  LEFT JOIN education e1
    ON e1.uid = 1
   AND f.filtertype = 'school'
   AND f.value = e1.school
  LEFT JOIN education e2
    ON e2.uid = 1
   AND f.filtertype = 'major'
   AND f.value = e2.major
  LEFT JOIN education e3
    ON e3.uid = 1
   AND f.filtertype = 'graduate'
   AND f.value = e3.graduate
 GROUP BY f.filterid
HAVING MAX(e1.educid IS NOT NULL) +
       MAX(e2.educid IS NOT NULL) +
       MAX(e3.educid IS NOT NULL) < 3

或

SELECT f.filterid
  FROM filters f LEFT JOIN
(
  SELECT uid, educid, 'school' type, school value
    FROM education
   WHERE uid = 1
  UNION ALL
  SELECT uid, educid, 'major' type, major value
    FROM education
   WHERE uid = 1
  UNION ALL
  SELECT uid, educid, 'graduate' type, graduate value
    FROM education
   WHERE uid = 1
) e 
    ON f.filtertype = e.type
   AND f.value = e.value
 GROUP BY f.filterid
HAVING MAX(f.filtertype = 'school' AND e.educid IS NOT NULL) +
       MAX(f.filtertype = 'major' AND e.educid IS NOT NULL) +
       MAX(f.filtertype = 'graduate' AND e.educid IS NOT NULL) < 3;

这是演示

Answer 2

考虑您的表如下

create table FILTERS(filterid int, filtertype varchar(100),value varchar(10))

insert into FILTERS values (1,'school','MIT')

insert into FILTERS values (1,'school','UT')

insert into FILTERS values (1,'school','Lund')

insert into FILTERS values (1,'major','econ')

insert into FILTERS values (1,'major','civil')

insert into FILTERS values (1,'graduate','1')

insert into FILTERS values (1,'school','Harvard')

create table EDUCATION (uid int, educid int, school varchar(100),major varchar(50),graduate varchar(10))

insert into education values (1,1,'MIT','econ','1')

insert into education values (1,2,'Lund','cs','0')

请看下面的SQL-如果它们能有任何帮助的话...

select * from (
select a.uid, a.educid,a.filtertype,a.value,b.filterid, 
b.filtertype filtertype_1,b.value value_1 from (
select uid, educid,'school' filtertype,school value from education
union all
select uid, educid,'major' filtertype,major value from education
union all
select uid, educid,'graduate' filtertype,graduate value from education
)a right outer join filters b
on a.filtertype=b.filtertype and a.value=b.value
) c where c.uid is null

select * from (
select a.uid, a.educid,a.filtertype,a.value,
b.filterid from (
select uid, educid,'school' filtertype,school value 
from education
union all
select uid, educid,'major' filtertype,major value 
from education
union all
select uid, educid,'graduate' filtertype,graduate value 
from education
)a left outer join filters b
on a.filtertype=b.filtertype and a.value=b.value
) c where c.filterid is null

Answer 3

不确定这是否是你要找的，但是你会明白的，fiddle example here (link)

SELECT E.*, CASE WHEN (school.VALUE+major.VALUE+grad.VALUE) IS NOT NULL THEN 'PASS!'
    ELSE 'Fail: ' + REVERSE(SUBSTRING(REVERSE(CASE WHEN school.VALUE IS NULL THEN 'school, ' ELSE '' END
        + CASE WHEN major.VALUE IS NULL THEN 'major, ' ELSE '' END
        + CASE WHEN grad.VALUE IS NULL THEN 'graduate, ' ELSE '' END), 3, 1000))
    END as Result
FROM EDUCATION E
LEFT JOIN FILTERS school ON school.FILTERID = 1 AND school.VALUE = E.school
LEFT JOIN FILTERS major ON major.FILTERID = 1 AND major.VALUE = E.major
LEFT JOIN FILTERS grad ON grad.FILTERID = 1 AND grad.VALUE = E.graduate

或者只是接受没有通过的教育，并解释原因，就像这样：

SELECT E.*, CASE WHEN (school.VALUE+major.VALUE+grad.VALUE) IS NOT NULL THEN 'PASS!'
    ELSE 'Fail: ' + REVERSE(SUBSTRING(REVERSE(CASE WHEN school.VALUE IS NULL THEN 'school, ' ELSE '' END
        + CASE WHEN major.VALUE IS NULL THEN 'major, ' ELSE '' END
        + CASE WHEN grad.VALUE IS NULL THEN 'graduate, ' ELSE '' END), 3, 1000))
    END as Result
FROM EDUCATION E
LEFT JOIN FILTERS school ON school.FILTERID = 1 AND school.VALUE = E.school
LEFT JOIN FILTERS major ON major.FILTERID = 1 AND major.VALUE = E.major
LEFT JOIN FILTERS grad ON grad.FILTERID = 1 AND grad.VALUE = E.graduate
WHERE (school.VALUE+major.VALUE+grad.VALUE IS NULL)