mysql - 两个表之间的简单连接

Question

我需要一些有关基本 MySQL 语句的帮助。

下面有两张表:餐食和人员。

TABLE meals
================================
=== id === name === meal     ===
================================
--- 1  --- John --- Pizza    ---
--------------------------------
--- 2  --- Jill --- Pasta    ---
--------------------------------
--- 3  --- Jill --- Pizza    ---
--------------------------------
--- 4  --- John --- Sandwich ---
--------------------------------
--- 5  --- Jill --- Rice     ---
--------------------------------

TABLE people
=========================================
=== name  === email           === age ===
=========================================
--- John  --- john@test.com   --- 24  ---
-----------------------------------------
--- Jill  --- jill@test.com   --- 24  ---
-----------------------------------------
--- Alice --- alice@test.com  --- 32  ---
-----------------------------------------

我想做的是获取 names 的不同列表从表中meals ，然后仅抓取 people 中的那些行表，输出排序顺序为age然后email (所以如果年龄相同，它会通过电子邮件进行二次排序)。

我很困惑!

Answer 1

由于 people 和 meals 表之间存在简单的 1 到 n 关系，因此我认为您不必执行distinct，然后是group by。您所要做的就是:

  SELECT p.name 
    FROM people p
   WHERE EXISTS (SELECT 1
                   FROM meals m
                  WHERE m.name = p.name)
ORDER BY p.age, p.email