mysql存储过程循环遍历2个表,选择值,并存储在第3个表中

a7qyws3x  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(307)

我有三张table users , memos ,和 memos_read . users :

+---+----------+
|ID | NAME     |
+---+----------+
| 1 | Mike     |
| 4 | Susan    |
| 5 | Peter    |
| 6 | Mary     |
+---+----------+
``` `memos` :

+---+----------+
|ID | CONTENT |
+---+----------+
| 15| Hello |
| 16| World |
| 17| Foo |
| 23| Bar |
+---+----------+

我想创建一个带有嵌套循环的存储过程,为已读的备忘录创建一个“读取Map”,创建如下内容: `memos_read` :

+---------+----------+
| MEMO_ID | USER_ID |
+---------+----------+
| 15 | 1 |
| 15 | 4 |
| 15 | 5 |
| 15 | 6 |
| 16 | 1 |
| 16 | 4 |
| 16 | 5 |
| 16 | 6 |
| 17 | 1 |
| 17 | 4 |
| 17 | 5 |
| 17 | 6 |
| 23 | 1 |
| 23 | 4 |
| 23 | 5 |
| 23 | 6 |
+---------+----------+

这可能吗?提前谢谢!
qmb5sa22

qmb5sa221#

@尼克的回答应该对你有用;但是,如果您对创建新表不感兴趣,而是在查询中动态地使用“readmap”;可以使用派生表。
一般查询结构如下:

SELECT
  read_map.MEMO_ID,
  read_map.USER_ID,
  ...
FROM 
(
  SELECT 
    m.ID AS MEMO_ID, 
    u.ID AS USER_ID
  FROM users AS u 
  CROSS JOIN memos AS m 
) AS read_map 

...  -- here you can join with other tables as required
WHERE ...
xdyibdwo

xdyibdwo2#

你可以用 CREATE TABLE ... SELECT 创建 memos_read 表格:

CREATE TABLE memos_read AS
SELECT m.ID AS MEMO_ID, u.ID AS USER_ID
FROM memos m 
CROSS JOIN users u

输出符合您的要求。在dbfiddle上演示

相关问题