SQL 内联相当于相交

Question

是否有与 intersect 类似或产生相同结果的内联 sql 命令？如果没有，是否有一种方法可以重写查询，使其在一个查询中而不是使用 intersect？

我当前的 sql 查询是:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)

但我真正想要的是:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 95 
INTERSECT 
SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 1

谢谢

Answer 1

你总是可以这样做:

SELECT DISTINCT
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv1 ON c.Class_Link = cv1.Class_Link INNER JOIN 
  ClassSelection cs1 ON cv1.ClassSelection_Link = cs1.ClassSelection_Link

INNER JOIN
  ClassValues cv2 ON c.Class_Link = cv2.Class_Link INNER JOIN 
  ClassSelection cs2 ON cv2.ClassSelection_Link = cs2.ClassSelection_Link

WHERE 
    cs1.classselection_link = 95 
  AND
    cs2.classselection_link = 1

或使用GROUP BY:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

并且由于您实际上并不需要 ClassSelection 表:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link
WHERE 
  cv.ClassSelection_Link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

正如 gbn 指出的那样，我假设 (Class_Link, ClassSelection_Link) 在表 ClassValues 中是UNIQUE。

如果不是这样，那么最后两个查询中的 HAVING 子句应更改为:

HAVING
  COUNT(DISTINCT cv.ClassSelection_Link) = 2