Выбор родительских записей, когда критерии соответствуют дочерним
Я пытаюсь ограничить возвращаемые результаты пользователей результатами, которые являются "недавними", но там, где у пользователей есть родитель, мне также нужно вернуть родителя.
CREATE TABLE `users` (
`id` int(0) NOT NULL,
`parent_id` int(0) NULL,
`name` varchar(255) NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `times` (
`id` int(11) NOT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `users`(`id`, `parent_id`, `name`) VALUES (1, NULL, 'Alan');
INSERT INTO `users`(`id`, `parent_id`, `name`) VALUES (2, 1, 'John');
INSERT INTO `users`(`id`, `parent_id`, `name`) VALUES (3, NULL, 'Jerry');
INSERT INTO `users`(`id`, `parent_id`, `name`) VALUES (4, NULL, 'Bill');
INSERT INTO `users`(`id`, `parent_id`, `name`) VALUES (5, 1, 'Carl');
INSERT INTO `times`(`id`, `time`) VALUES (2, '2019-01-01 14:40:38');
INSERT INTO `times`(`id`, `time`) VALUES (4, '2019-01-01 14:40:38');
В этом случае я бы хотел вернуть Алана, Джона и Билла, но не Джерри, потому что у Джерри нет записи в times стол, и при этом он не является родителем кого-то с записью. Я на грани того, что делать с Карлом, я не против получить для него результаты, но они мне не нужны.
Я фильтрую десятки тысяч пользователей с сотнями тысяч times записи, поэтому производительность важна. В целом у меня около 3000 уникальных идентификаторов times это может быть idили parent_id,
Выше приведен упрощенный пример того, что я пытаюсь сделать, полный включает больше объединений и операторов case, но в целом приведенный выше пример должен быть тем, с чем мы работаем, но вот пример запроса, который я использую (полный запрос составляет почти 100 строк):
SELECT id AS reference_id,
CASE WHEN (id != parent_id)
THEN
parent_id
ELSE null END AS parent_id,
parent_id AS family_id,
Rtrim(last_name) AS last_name,
Rtrim(first_name) AS first_name,
Rtrim(email) AS email,
missedappt AS appointment_missed,
appttotal AS appointment_total,
To_char(birth_date, 'YYYY-MM-DD 00:00:00') AS birthday,
To_char(first_visit_date, 'YYYY-MM-DD 00:00:00') AS first_visit,
billing_0_30
FROM users AS p
RIGHT JOIN(
SELECT p.id,
s.parentid,
Count(p.id) AS appttotal,
missedappt,
billing0to30 AS billing_0_30
FROM times AS p
JOIN (SELECT missedappt, parent_id, id
FROM users) AS s
ON p.id = s.id
LEFT JOIN (SELECT parent_id, billing0to30
FROM aging) AS aging
ON aging.parent_id = p.id
WHERE p.apptdate > To_char(Timestampadd(sql_tsi_year, -1, Now()), 'YYYY-MM-DD')
GROUP BY p.id,
s.parent_id,
missedappt,
billing0to30
) AS recent ON recent.patid = p.patient_id
Этот пример относится к базе данных Faircom C-Tree, но мне также необходимо реализовать аналогичное решение в Sybase, MySql и Pervasive, поэтому я просто пытаюсь понять, что я должен делать для достижения максимальной производительности.
По сути, мне нужно как-то получить RIGHT JOIN также включить родителя пользователей.
1 ответ
ЗАМЕТКИ:
основываясь на вашей конфигурации fiddle, я предполагаю, что вы используете MySQL 5.6 и, следовательно, не имеете поддержки выражений общих таблиц (CTE)
Я предполагаю, что каждое имя (дочернее или родительское) должно быть представлено как отдельные записи в окончательном наборе результатов
Мы хотим ограничить количество раз, когда мы должны присоединиться к times а также users таблицы (CTE сделает это немного проще для кодирования / чтения).
Основной запрос (times -> users(u1) -> users(u2)) даст нам имена дочерних и родительских элементов в отдельных столбцах, поэтому мы будем использовать двухстрочную динамическую таблицу плюс case оператор to для поворота столбцов в их собственные строки (ПРИМЕЧАНИЕ: я не работаю с MySQL, и у меня не было времени исследовать, есть ли pivot возможность в MySQL 5.6)
-- we'll let 'distinct' filter out any duplicates (eg, 2 'children' have same 'parent')
select distinct
final.name
from
-- cartesian product of 'allnames' and 'pass' will give us
-- duplicate lines of id/parent_id/child_name/parent_name so
-- we'll use a 'case' statement to determine which name to display
(select case when pass.pass_no = 1
then allnames.child_name
else allnames.parent_name
end as name
from
-- times join users left join users; gives us pairs of
-- child_name/parent_name or child_name/NULL
(select u1.id,u1.parent_id,u1.name as child_name,u2.name as parent_name
from times t
join users u1
on u1.id = t.id
left
join users u2
on u2.id = u1.parent_id) allnames
join
-- poor man's pivot code:
-- 2-row dynamic table; no join clause w/ allnames will give us a
-- cartesian product; the 'case' statement will determine which
-- name (child vs parent) to display
(select 1 as pass_no
union
select 2) pass
) final
-- eliminate 'NULL' as a name in our final result set
where final.name is not NULL
order by 1
Набор результатов:
name
==============
Alan
Bill
John