Столбец ответа на запрос, полученный из комбинации столбцов в другой таблице
TNM Staging Я делаю свой первый проект. У меня есть некоторые данные: t_stage — от t0 до t4, n_stage — от n0 до n3, а m_stage — от m0 и m1. Разные их комбинации дают разные результаты. Таким образом, t1,n2,m0 — это этап 3a, t4,n1,m1 — это этап 4.
Я хочу, чтобы запрос содержал эти конкретные шаблоны уже в синтаксисе, чтобы при запуске запроса появлялся дополнительный столбец, определяющий этап, зависящий от чисел t, n и m, которые находятся в таблице для каждой записи. .
Как и выше, если t равно t1, n равно n2 и m равно 0, для этой записи будет дополнительный столбец с заголовком «Этап» с этапом 3a и так далее для вариантов. (Комбинации см. на изображении).
Я просто не могу понять, как создать этот дополнительный столбец «Этап» и заполнить его ответом при выполнении запроса.
Я, вероятно, упускаю что-то очень очевидное. Как я уже сказал, это мой первый проект с MySQL. Я привык к MSAccess и продолжаю думать, что значительная часть автоматизации все еще будет там.
Я ДОЛЖЕН хранить отдельные оценки T, N и M для каждой записи в схеме для возможного последующего запроса.
Я посмотрел на DERIVED, UNION, WHERE и HAVING и просто не знаю, на правильном ли я пути.
CREATE TABLE pathology (
pathology_id VARCHAR(100) NOT NULL PRIMARY KEY,
medical_data_id VARCHAR(100),
grade INT
CONSTRAINT grade CHECK (grade IN (1, 2, 3, 4)),
tumour_weight DECIMAL (3,3) -- in grams
tumour_circumference DECIMAL (2,2) -- in cms
tumour_stage ENUM('t0','t1', 't2', 't3', 't4'),
node_stage ENUM('n0', 'n1', 'n2', 'n3'),
met_stage ENUM('m0', 'm1')
);
@TimRoberts Поскольку вторая таблица содержит все возможные комбинации, нет смысла использовать LEFT JOIN, это должно быть INNER JOIN.
«Итак, t1,n2,m0 — это Stage3a» — ПОЧЕМУ? как из этой комбинации получается 3а?
@P.Salmon, это способ сортировки стадий рака. Никаких математических рассуждений. Просто накапливаются разные баллы в зависимости от результатов патологии. Таким образом, если m равно 1, это всегда будет стадия 4, потому что 1 означает, что есть метастазы.
@TimRoberts Мне приходится хранить отдельные оценки t,n,m для каждой записи, сохраненные в схеме, для возможного запроса позже.
@P.Salmon, эта таблица, которую он использовал Researchgate.net/figure/…
@DonnaCannon Вам НЕ нужны результаты, хранящиеся в записи. На самом деле это считается плохой практикой, потому что это излишне. Это не Нормальная Форма. Где бы вы ни запросили запись, вы просто используете JOIN. Фактически, MySQL позволяет вам создать CREATE VIEW, который ВЫГЛЯДИТ как таблица, но выполняет JOIN за вас, оставляя структуру базы данных нетронутой.
Ответы 3
У вас должна быть таблица всех возможных комбинаций, записанная в вашей БД. Затем вы можете соединить таблицу случаев с соответствующими строками в таблице комбинаций, используя поля T, N и M, что должно дать вам искомый результат.
Если имя вашей таблицы случаев было X, а таблица комбинаций — Y, предикат в вашем SQL должен читать что-то похожее на это:
SELECT (...), Y.STAGE
FROM X, Y
WHERE X.T=Y.T AND X.N=Y.N AND X.M=Y.M
Согласно опубликованной вами таблице, в некоторых случаях допускается любое T или любое N. Для таких случаев вам следует создать дополнительные группы ИЛИ. Дайте мне знать, если вам нужна дополнительная информация по этому поводу.
Теперь я добавил код таблицы к своему первоначальному вопросу. Я не уверен, ясно ли я это объяснил изначально, но отдельные данные t, n и m должны быть сохранены для каждой записи для возможного отдельного запроса в следующий раз. Так будет ли работать таблица со всеми комбинациями?
Да, они бы это сделали. Теперь, имея описание таблицы, считайте T как tumour_stage, N как node_stage и M как met_stage. Ваша таблица «патологий» (X) сохранит эти значения. Вам понадобится дополнительная таблица «стадий» (Y), к которой вы соедините патологию с критериями, которые я объяснил выше.
Итак, я выполнил этот запрос после создания и заполнения таблицы комбинаций (tnm_staging), и он дал мне заголовок столбца, но не дал данных. Извините, если я упускаю что-то очень очевидное. Я попробовал другую комбинацию, где в этом примере указан pathology_id. ВЫБРАТЬ (pathology_id) общий_этап ИЗ патологии, tnm_staging ГДЕ pathology.tumour_stage=tnm_staging.t_category И pathology.node_stage=tnm_staging.n_category И pathology.met_stage=tnm_staging.n_category;
@DonnaCannon при условии, что ваша таблица tnm_staging содержит правильную стадию для каждой комбинации t, n и m в поле common_stage, а в вашей таблице патологии есть хотя бы одна запись с полными данными, это должно работать. Точный SQL-запрос должен быть следующим: SELECT pathology_id, tnm_staging.overall_stage FROM pathology, tnm_staging WHERE pathology.tumour_stage=tnm_staging.t_category AND pathology.node_stage=tnm_staging.n_category AND pathology.met_stage=tnm_staging.n_category; - Можете ли вы предоставить примеры данных из вашей таблицы патологий, просто для проверки?
Если у вас есть таблица, подобная этой:
-- S a m p l e D a t a :
Create Table your_table ( id Int, T_stage Text, N_stage Text, M_stage Text );
Insert Into your_table
Select 1, 'T0', 'N0', 'M0' Union All
Select 2, 'T0', 'N1', 'M0' Union All
Select 3, 'T0', 'N2', 'M0' Union All
Select 4, 'T0', 'N2', 'M1' Union All
Select 5, 'T1', 'N0', 'M0' Union All
Select 6, 'T1', 'N1', 'M0' Union All
Select 7, 'T1', 'N2', 'M0' Union All
Select 8, 'T3', 'N1', 'M0' Union All
Select 9, 'T3', 'N1', 'M1' Union All
Select 10, 'T3', 'N3', 'M0' Union All
Select 11, 'T4', 'N2', 'M0' Union All
Select 12, 'T4', 'N3', 'M0';
... вы можете создать cte (или таблицу), которая определяет различные комбинации этапов T, N и M, давая им общий этап (как в вашей ссылке выше). Я закомментировал последние 3 строки, потому что они различаются и будут обрабатываться выражением Case в основном коде SQL ниже...
WITH
stages ( overall_stage, T_category, N_category, M_category ) AS
( Select 'Stage 0', 'T0', 'N0', 'M0' Union All
Select 'Stage 1', 'T1', 'N0', 'M0' Union All
Select 'Stage 2A', 'T0', 'N1', 'M0' Union All
Select 'Stage 2A', 'T1', 'N1', 'M0' Union All
Select 'Stage 2A', 'T2', 'N0', 'M0' Union All
Select 'Stage 2B', 'T2', ' N1', 'M0' Union All
Select 'Stage 2B', 'T3', 'N0', 'M0' Union All
Select 'Stage 3A', 'T0', 'N2', 'M0' Union All
Select 'Stage 3A', 'T1', 'N2', 'M0' Union All
Select 'Stage 3A', 'T2', 'N2', 'M0' Union All
Select 'Stage 3A', 'T3', 'N1', 'M0' Union All
Select 'Stage 3A', 'T3', 'N2', 'M0'
-- Union All
-- Select 'Stage 3B', 'T4', 'Any N', 'M0' Union All
-- Select 'Stage 3C', 'Any T', 'N3', 'M0' Union All
-- Select 'Stage 4', 'Any T', 'Any N', 'M1'
)
... этот cte, если его оставить присоединенным к your_table, должен дать вам ожидаемый общий_этап для каждой строки, оставляя ваши отдельные этапы нетронутыми...
-- M a i n S Q L :
Select t.id, t.T_stage, t.N_stage, t.M_stage,
Case When t.M_stage = 'M1' Then 'Stage 4'
When t.N_stage = 'N3' And t.M_stage = 'M0' Then 'Stage 3C'
When t.T_stage = 'T4' And t.M_stage = 'M0' Then 'Stage 3B'
When s.overall_stage Is Not Null Then s.overall_stage
Else 'N/A'
End as overall_stage
From your_table t
Left Join stages s ON( s.T_category = t.T_stage And
s.N_category = t.N_stage And
s.M_category = t.M_stage )
Order By t.id
/* R e s u l t :
id T_stage N_stage M_stage overall_stage
-- --------- ---------- ---------- -------------
1 T0 N0 M0 Stage 0
2 T0 N1 M0 Stage 2A
3 T0 N2 M0 Stage 3A
4 T0 N2 M1 Stage 4
5 T1 N0 M0 Stage 1
6 T1 N1 M0 Stage 2A
7 T1 N2 M0 Stage 3A
8 T3 N1 M0 Stage 3A
9 T3 N1 M1 Stage 4
10 T3 N3 M0 Stage 3C
11 T4 N2 M0 Stage 3B
12 T4 N3 M0 Stage 3C */
См. скрипку здесь.
ПРИМЕЧАНИЕ: Выражение Case является последовательным, то есть первым. Когда условие удовлетворено, оно возвращает значение «Тогда» и выходит из Case. Единственная проблема может заключаться в том, какой общий_этап должен быть выбран, если у вас есть комбинация T4-N3-M0, это этап 3C или 3B - я поставил 3C, если это противоположно, просто поменяйте местами эти две строки в выражении Case....
P.S. Лучшим решением было бы создать постоянную таблицу со всеми явными комбинациями трех этапов с соответствующим значением common_stage для каждого. Тогда вам вообще не понадобится выражение Case — просто соединение, выбирающее общий_этап из этой новой таблицы для каждой объединенной строки в вашей_таблице.
Я создал отдельную таблицу под названием tnm_staging. он содержит все комбинации из эталонного изображения, которое я использовал. Я создал внешний ключ для подключения таблицы патологии (которая содержит отдельные оценки t,n,m) к tnm_staging (таблице сочетаний). Я использовал запрос, указанный пользователем выше, но мне не удалось заполнить возвращаемый столбец. Возможно, я использовал неправильный тип соединения.
Справочное изображение @DonnaCannon не содержит всех явных комбинаций из трех. Есть такие вещи, как ЛЮБОЙ T или Tis, которых нет в данных (T0, T1, T2, T3, T4). Попробуйте сначала адаптировать приведенный выше код к вашему контексту (имя таблицы (your_table) и имена столбцов, начинающиеся с t. в основном SQL выше). В зависимости от чувствительности к регистру вашей базы данных и фактических данных - она может работать как есть. Если ваша база данных чувствительна к регистру и у вас есть «t0» вместо «T0», тогда обработайте это тоже, используя функцию Upper() или Lower() в предложении ON соединения и в условиях Case WHEN...
Я создал каждую перестановку, кроме Тиса. Это настолько незначительно, что теоретически не могло бы произойти в тех целях, для которых эта база данных будет использоваться. Я убедился, что случаи одинаковы. Я бы прикрепил код, но в это поле для комментариев он не влезает. Возможно, загружу удаленно и пришлю ссылку. Если у вас есть ссылка на скрипку, возможно, я мог бы сделать это таким образом?
@DonnaCannon Посмотрите скрипку на dbfiddle.uk/PCC6tT_e Я сгенерировал все комбинации, используя рекурсивные cte (всего 40 комбинаций). Ниже этих комбинаций приведены примеры данных и рабочий основной код sql, извлекающий common_stages.... (Используется Oracle из-за множественных рекурсий и из-за того, что я привык к Oracle)
ВТОРОЙ ОТВЕТ (после множества комментариев):
Сначала создайте таблицу tnm_staging со всеми 40 возможными явными комбинациями трех значений столбца, определяющими столбец common_stage для каждого:
-- Combination generator for T 0-4 / N 0-3 / M 0-1
CREATE TABLE tnm_staging
WITH
t_stages ( T_cat ) AS
( Select 't0' Union All
Select 't1' Union All
Select 't2' Union All
Select 't3' Union All
Select 't4'
),
n_stages ( N_cat ) AS
( Select 'n0' Union All
Select 'n1' Union All
Select 'n2' Union All
Select 'n3'
),
m_stages ( M_cat ) AS
( Select 'm0' Union All
Select 'm1'
),
stages as
( Select Row_Number() Over(Order By tn.T_cat, tn.N_cat, m.M_cat) as id,
'Stage x' as overall_stage, tn.T_cat, tn.N_cat, m.M_cat
From ( Select t.T_cat, n.N_cat
From t_stages t
CROSS JOIN n_stages n
) tn
CROSS JOIN m_stages m
),
stage_names ( overall_stage, T_category, N_category, M_category ) AS
( Select 'Stage 0', 't0', 'n0', 'm0' Union All
Select 'Stage 1', 't1', 'n0', 'm0' Union All
Select 'Stage 2A', 't0', 'n1', 'm0' Union All
Select 'Stage 2A', 't1', 'n1', 'm0' Union All
Select 'Stage 2A', 't2', 'n0', 'm0' Union All
Select 'Stage 2B', 't2', 'n1', 'm0' Union All
Select 'Stage 2B', 't3', 'n0', 'm0' Union All
Select 'Stage 3A', 't0', 'n2', 'm0' Union All
Select 'Stage 3A', 't1', 'n2', 'm0' Union All
Select 'Stage 3A', 't2', 'n2', 'm0' Union All
Select 'Stage 3A', 't3', 'n1', 'm0' Union All
Select 'Stage 3A', 't3', 'n2', 'm0'
)
Select t.id, t.T_cat as tumour_stage, t.N_cat as node_stage, t.M_cat as met_stage,
Case When t.M_cat = 'm1' Then 'Stage 4'
When t.N_cat = 'n3' And t.M_cat = 'm0' Then 'Stage 3C'
When t.T_cat = 't4' And t.M_cat = 'm0' Then 'Stage 3B'
When s.overall_stage Is Not Null Then s.overall_stage
Else 'N/A'
End as overall_stage
From stages t
Left Join stage_names s ON( s.T_category = t.T_cat And
s.N_category = t.N_cat And
s.M_category = t.M_cat )
Order By t.id;
SELECT * FROM tnm_staging; -- this should resut with 40 different combinations each with it's own overall_stage following your rules
... и с вашей таблицей патологий и данными как:
CREATE TABLE pathology ( pathology_id VARCHAR(100) NOT NULL PRIMARY KEY,
tumour_stage ENUM('t0','t1', 't2', 't3', 't4'),
node_stage ENUM('n0', 'n1', 'n2', 'n3'),
met_stage ENUM('m0', 'm1')
);
-- S a m p l e D a t a :
pathology_id tumour_stage node_stage met_stage
------------ ------------ ---------- ---------
01 t0 n0 m0
02 t0 n1 m0
03 t0 n2 m0
04 t0 n2 m1
05 t1 n0 m0
06 t1 n1 m0
07 t1 n2 m0
08 t3 n1 m0
09 t3 n1 m1
10 t3 n3 m0
11 t4 n2 m0
12 t4 n3 m0
13 t2 n1 m0
... объедините свои таблицы патологии и tnm_staging, чтобы получить столбец overal_stage для каждой строки в таблице патологии:
-- S Q L :
Select t.pathology_id, t.tumour_stage, t.node_stage, t.met_stage,
s.overall_stage
From pathology t
Left Join tnm_staging s ON( s.tumour_stage = t.tumour_stage And
s.node_stage = t.node_stage And
s.met_stage = t.met_stage )
Order By t.pathology_id
Посмотрите скрипт здесь (с результатами).
Фантастическое спасибо вам огромное. Теперь я могу этому научиться на основе реверс-инжиниринга. Я только что добавил дополнительные поля и данные в таблицу патологии вместе с уже имеющимися данными tnm. Огромное вам спасибо за вашу помощь и за то, что вы поддерживаете новичка на этом сайте, а также за MySQL.
@DonnaCannon Пожалуйста. Рад помочь.
Существует 40 различных комбинаций. У всех 40 есть сцена? Вы наверняка могли бы создать вторую таблицу, содержащую все 40 возможных комбинаций и полученный идентификатор этапа, и выполнить LEFT JOIN, чтобы включить ее в свой запрос.