Выбор родительских записей, когда дочерние математические критерии
Я пытаюсь ограничить возвращаемые пользователями результаты результатами, которые являются «недавними», но если у пользователей есть родитель, мне также нужно вернуть родителя.
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');
http://sqlfiddle.com/#!9/91db19
В этом случае я хотел бы вернуть Алана, Джона и Билла, но не Джерри, потому что у Джерри нет записи в таблице 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
ПРИМЕЧАНИЯ:
на основе вашей конфигурации скрипта я предполагаю, что вы используете MySQL 5.6 и, следовательно, не поддерживаете Common Table Expressions (CTE)
Я предполагаю, что каждое имя (дочернее или родительское) должно быть представлено в виде отдельных записей в конечном наборе результатов.
Мы хотим ограничить количество раз, когда нам нужно присоединиться к таблицам times и users (CTE сделает это немного проще для кодирования/чтения).
Основной запрос (times -> users(u1) -> users(u2)) даст нам дочерние и родительские имена в отдельных столбцах, поэтому мы будем использовать динамическую таблицу с двумя строками плюс оператор case, чтобы повернуть столбцы в их собственные строки (ПРИМЕЧАНИЕ: я не работаю с 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
Я бы начал с того, что упростил ваш запрос, чтобы он соответствовал вашим урезанным таблицам, добавил пример набора результатов, который вы ищете, и, пока вы это делаете, как насчет версий вашей базы данных (например, MySQL 5.6 не поддерживает CTES, но поддерживает MySQL 8+), а в случае 'sybase' => какой продукт Sybase RDBMS (ASE? SQLAnywhere? IQ? ADS?)