У меня 3 стола. Один со списком пользователей, один с элементами и один со связью между пользователями и элементами. Пользователь может иметь элемент (stat = 0/1) или дубликат (dup = 0/1). Если вы нажмете кнопку элемента или дубликата, он запустит запрос, который добавит или обновит строку.
Это выглядит так:
| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17 | 1 | 1 | 1 |
| 5 | 2 | 1 | 0 |
| 8 | 1 | 0 | 1 |
| 9 | 4 | 1 | 0 |
Я сравниваю эти данные, у кого есть элемент, которого нет у другого, и наоборот, с этим запросом:
SELECT t2.user_id,
GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `My Item List`,
GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `Item List`
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
AND t2.user_id <> @current_user
GROUP BY t2.user_id
Моя проблема в том, что я могу сравнивать строки только в том случае, если пользователь уже нажал кнопку элемента хотя бы один раз, поэтому строка сгенерирована, но мне также нужны строки stat=0, даже если они не существуют.
После запуска кода таблица должна выглядеть так, чтобы я мог видеть отношения, даже если у пользователя нет (stat=0) этого элемента:
| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17 | 1 | 1 | 1 |
| 5 | 2 | 0 | 0 |
| 5 | 6 | 1 | 0 |
| 8 | 1 | 0 | 1 |
| 9 | 4 | 1 | 0 |
| 9 | 6 | 0 | 0 |
| 17 | 13 | 0 | 0 |
Как я могу интегрировать это в запрос выше?
Мои столы:
Таблица 1
CREATE TABLE table1(
id NOT NULL AUTO_INCREMENT,
user_name varchar(255),
);
Таблица 2
CREATE TABLE table2(
id NOT NULL AUTO_INCREMENT,
item_no varchar(255),
item_name varchar(255),
item_group varchar(255)
);
Таблица 3
CREATE TABLE table3 (
id int NOT NULL AUTO_INCREMENT,
user_id int NOT NULL,
item_id int NOT NULL,
stat tinyint NOT NULL,
dup tinyint NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES table1(id),
FOREIGN KEY (item_id) REFERENCES table2(id)
);
Спасибо!
Добавьте образцы данных как INSERT INTO. Покажите желаемый результат для ваших образцов данных.
обновлен выходными данными, которые я хочу
Что, если вы просто возьмете все уникальные комбинации user и item_id, а затем сопоставите только имеющиеся комбинации, а остальные оставите нулевыми? Мы можем заменить ноль на 0.
Что-то вроде,
SELECT
all_combos.user,
all_combos.item_id,
COALESCE(T.stat, 0) AS stat,
COALESCE(T.dup) AS dup
FROM
(
SELECT
user,
item_id
FROM
(SELECT DISTINCT user FROM table3) u,
(SELECT DISTINCT item_id FROM table3) i
) AS all_combos
LEFT OUTER JOIN table3 T ON T.user = all_combos.user
AND T.item_id = all_combos.item_id
Если вы хотите создать комбинации пользователей и элементов, которых никогда не было в таблице 3, вы можете объединить их, изменив подзапросы для u
и i
.
SELECT
all_combos.user,
all_combos.item_id,
COALESCE(T.stat, 0) AS stat,
COALESCE(T.dup) AS dup
FROM
(
SELECT
user,
item_id
FROM
(SELECT DISTINCT id as user FROM table1) u,
(SELECT DISTINCT id as item_id FROM table2) i
) AS all_combos
LEFT OUTER JOIN table3 T ON T.user = all_combos.user
AND T.item_id = all_combos.item_id
он говорит, что столбец «пользователь» недействителен в «списке полей». как я могу это исправить?
Может быть, это user_id? Вам придется исправить мой SQL в соответствии с вашими данными - в вашем результате указано USER, но в таблице указан USERID, поэтому я точно не знаю, как вы получили USER
оно работает! еще одна вещь: он генерирует строку для каждого элемента и пользователя только в том случае, если элемент был нажат (попал в базу данных). любой способ перечислить их, не нажимая на него?
@gibrish_diodo подзапросы u и i — это то, на чем вы хотите сосредоточиться. если вы измените каждого из них на уникальных пользователей из другой таблицы (не таблицы 3), вы можете напрямую получать элементы и пользователей независимо от того, существовали они в таблице 3 или нет.
так? (SELECT DISTINCT id FROM table1) u, (SELECT DISTINCT id FROM table2)
. если я сделаю это, это не сработает, потому что первичный ключ обеих таблиц - id
, и я не могу отделить его от all_combos.id
@gibrish_diodo я отредактировал ответ выше, чтобы дать вам эту альтернативу. Вы просто можете использовать псевдоним для идентификатора, чтобы у all_combos не было 2 столбцов с одинаковыми именами. И если вы присвоите им то же имя, что и в таблице 3, вам даже не нужно менять синтаксис соединения.
оно работает! Спасибо! я застрял на этом в течение нескольких месяцев. Спасибо! теперь мне нужно объединить эти результаты с моим другим запросом.
мне было интересно, этот запрос генерирует те же данные с той же структурой, что и table3. можно ли запустить мой сравнительный запрос на результат?
Конечно, поскольку мы уже присоединяемся к table3, вы можете добавить фильтр WHERE T.user IS NULL, и он покажет вам только созданные нами "новые" строки, которых еще не было в table3. Изучите LEFT OUTER JOIN, и вы поймете, как это работает.
Покажите нам образцы данных для всех таблиц, а также ожидаемый результат. (То есть минимальный воспроизводимый пример.)