mysql - 在功能意义上压缩两个查询的 SQL 习语是什么？

Question

例如，如果我有一组类(class)和一组教室，我想通过一些任意配对将两者配对:

> SELECT class_name FROM classes ORDER BY class_name
Calculus
English
History
> SELECT room_name FROM classrooms ORDER BY room_name
Room 101
Room 102
Room 201

我想像这样“压缩”它们:

> SELECT class_name FROM classes ORDER … ZIP SELECT room_name FROM classrooms ORDER …
Calculus | Room 101
English  | Room 102
History  | Room 201

目前我正在处理 MySQL ......但可能 - 乐观？ — 是否有一种合理的符合标准的方式来做到这一点？

Answer 1

在 MySql 中实现的一种方法

SELECT c.class_name, r.room_name
  FROM
(
  SELECT class_name, @n := @n + 1 rnum
    FROM classes CROSS JOIN (SELECT @n := 0) i
   ORDER BY class_name
) c JOIN
(
  SELECT room_name, @m := @m + 1 rnum
    FROM classrooms CROSS JOIN (SELECT @m := 0) i
   ORDER BY room_name
) r 
   ON c.rnum = r.rnum 

输出:

| CLASS_NAME | ROOM_NAME |-------------|-----------||   Calculus |  Room 101 ||    English |  Room 102 ||    History |  Room 201 |

Here is SQLFIddle demo

---

Same thing in Postgres will look like

SELECT c.class_name, r.room_name
  FROM
(
  SELECT class_name, 
         ROW_NUMBER() OVER (ORDER BY class_name) rnum
    FROM classes 
) c JOIN
(
  SELECT room_name,
         ROW_NUMBER() OVER (ORDER BY room_name) rnum
    FROM classrooms 
) r 
   ON c.rnum = r.rnum 

这是 SQLFiddle 演示

---

在 SQLite 中

SELECT c.class_name, r.room_name
  FROM
(
  SELECT class_name, 
         (SELECT COUNT(*) 
            FROM classes 
           WHERE c.class_name >= class_name) rnum
    FROM classes c
) c JOIN
(
  SELECT room_name,
         (SELECT COUNT(*) 
            FROM classrooms 
           WHERE r.room_name >= room_name) rnum
    FROM classrooms r
) r 
   ON c.rnum = r.rnum 

这是 SQLFiddle 演示