我有下表存储EAV模型中的数据:
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | MUL | NULL | |
| question_id | int(11) | NO | MUL | NULL | |
| answer | blob | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
有一个表格来存放不同类型的问题:
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| question | blob | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
以及用户表:
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_name | varchar(128) | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
我该如何编写一个查询,为每个用户当前没有行的每个question_id
插入一个answer
为空值的行?
例如,如果我有question_id
和1,2,3,4
,并且我的存储数据的表看起来像:
+--------------+---------------+--------+
| user_id | question_id | answer |
+--------------+---------+-----+--------+
| 1 | 1 | example|
| 1 | 3 | example|
| 1 | 4 | example|
+--------------+---------+-----+--------+
我想插入一行,看起来像:
+--------------+---------------+--------+
| user_id | question_id | answer |
+--------------+---------+-----+--------+
| 1 | 2 | NULL |
+--------------+---------+-----+--------+
我试过这样的东西:
INSERT INTO profile_answers
(
user_id,
question_id,
answer
)
SELECT
id,
profile_answers.question_id,
null
FROM users
LEFT JOIN profile_answers ON profile_answers.user_id = users.id
WHERE NOT EXISTS (
SELECT answer
FROM profile_answers
WHERE user_id = id
AND question_id IN (
SELECT GROUP_CONCAT(id SEPARATOR ',') FROM profile_questions
)
)
但我最终插入了question id
为0的行。
我已经考虑过了,但我找不到比在用户和问题之间使用笛卡尔乘积和过滤子查询更好的东西了:
SELECT u.id, q.id
FROM users u,
questions q
WHERE NOT EXISTS(
SELECT *
FROM profile_answers a
WHERE a.question_id = q.id AND a.user_id = u.id
);
演示