使用SQL查询具有相同A列但不同B列的记录。

Question

假设我们有下面这个名为 meals 的表：

| meal  | stars |
-----------------
| steak |   1   |
| steak |   2   |
| fish  |   4   |
| fish  |   4   |
| salad |   5   |

如何获取同一道菜，但星级不同的记录？我需要只有星级不同的记录。 上述表的结果应该如下：

| meal  | stars |
-----------------
| steak |   1   |
| steak |   2   |

我尝试了以下查询：

SELECT DISTINCT t1.*
FROM meals t1
INNER JOIN meals t2 ON t1.meal = t2.meal
AND t1.stars <> t2.stars;

但是这样做会消耗大量时间和显著的内存。

我的表的实际大小为:

SELECT pg_size_pretty(pg_relation_size('table_name')); 
 pg_size_pretty 
----------------
 2295 MB

所以我需要想出其他办法，请求您的帮助！

- melekes

Answer 1

SELECT  a.*
FROM    meals a
        INNER JOIN
        (
            SELECT  meal
            FROM    meals
            GROUP   BY meal
            HAVING  COUNT(DISTINCT stars) > 1
        ) b ON a.meal = b.meal

• SQLFiddle演示

输出

╔═══════╦═══════╗
║ MEAL  ║ STARS ║
╠═══════╬═══════╣
║ steak ║     1 ║
║ steak ║     2 ║
╚═══════╩═══════╝

- John Woo

Answer 2

 SELECT meal,stars FROM meals
 GROUP BY meal,stars
 HAVING count(*)=1 and meal in (
    SELECT meal FROM meals
    GROUP BY meal
    HAVING count(*)>1 )

- Alex

---

你和@j-w的解决方案都非常好，但第二个稍微快一点，所以我选择了它。 - melekes

Answer 3

这应该是最快的方法：

SELECT  *
FROM    meals m
WHERE   EXISTS (SELECT FROM meals WHERE meal = m.meal AND stars <> m.stars);

"获取所有行，其中至少存在一种其他餐点，其名称相同但星级不同。"

假设不存在NULL值。

SQL Fiddle.

- Erwin Brandstetter

Answer 4

一种简单而更快速地实现相同结果的方法

SELECT  
  m.*
FROM    
  meals m
  JOIN meals m2 ON ( m2.meal = m.meal AND m2.stars<>m.stars)

- Akash