Oracle sucks — различия между версиями
Материал из YourcmcWiki
м |
м |
||
| (не показана одна промежуточная версия этого же участника) | |||
| Строка 1: | Строка 1: | ||
| + | Oracle, бывает, очень прикольно исполняет некоторые запросы. | ||
| + | |||
| + | Например, здесь, если раскомментировать /* ORDER BY "alias1".id_employee */, результат запроса меняется! | ||
| + | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT | SELECT | ||
| − | "alias6".birth_date AS "col4", | + | "alias6".birth_date AS "col4", |
| − | "alias6".e_category AS "col5", | + | "alias6".e_category AS "col5", |
| − | "alias6".id_chief AS "col6", | + | "alias6".id_chief AS "col6", |
| − | "alias6".id_department AS "col7", | + | "alias6".id_department AS "col7", |
| − | "alias6".fire_date AS "col8", | + | "alias6".fire_date AS "col8", |
| − | "alias6".first_name AS "col9", | + | "alias6".first_name AS "col9", |
| − | "alias6".hire_date AS "col10", | + | "alias6".hire_date AS "col10", |
| − | "alias6".id_employee AS "col11", | + | "alias6".id_employee AS "col11", |
| − | "alias6".last_name AS "col12", | + | "alias6".last_name AS "col12", |
| − | "alias6".dtm_modified AS "col13", | + | "alias6".dtm_modified AS "col13", |
| − | "alias6".salary AS "col14", | + | "alias6".salary AS "col14", |
| − | "alias6".e_sex AS "col15", | + | "alias6".e_sex AS "col15", |
| − | "alias6".id_product AS "col16" | + | "alias6".id_product AS "col16" |
| − | FROM | + | FROM ( |
| − | ( | + | SELECT "alias1".id_employee as "col123", |
| − | SELECT | + | CASE WHEN (EXISTS ( |
| − | "alias1".id_employee as "col123", | + | SELECT '1' AS "col1" |
| − | CASE WHEN (EXISTS | + | FROM t_employee "alias2" |
| − | ( | + | WHERE "alias2".id_chief = "alias1".id_employee |
| − | SELECT | + | )) |
| − | '1' AS "col1" | + | THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2" |
| − | FROM | + | FROM t_employee "alias1" |
| − | t_employee "alias2" | + | /* ORDER BY "alias1".id_employee - если раскомментировать, результат запроса меняется! */ |
| − | WHERE | + | |
| − | "alias2".id_chief = "alias1".id_employee | + | |
| − | ) | + | |
| − | ) THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2" | + | |
| − | FROM | + | |
| − | t_employee "alias1" | + | |
| − | /* ORDER BY | + | |
| − | "alias1".id_employee */ | + | |
) "alias4" | ) "alias4" | ||
| − | LEFT JOIN | + | LEFT JOIN (t_employee "alias3" |
| − | ( | + | LEFT JOIN (t_employee "alias5" |
| − | t_employee "alias3" | + | LEFT JOIN t_employee "alias6" ON "alias5".id_chief = "alias6".id_employee |
| − | LEFT JOIN | + | ) ON "alias3".id_chief = "alias5".id_employee |
| − | ( | + | ) ON "alias4"."col2" = "alias3".id_employee |
| − | t_employee "alias5" | + | ORDER BY "alias4"."col123" |
| − | LEFT JOIN | + | |
| − | t_employee "alias6" | + | |
| − | ON "alias5".id_chief = "alias6".id_employee | + | |
| − | ) | + | |
| − | ON "alias3".id_chief = "alias5".id_employee | + | |
| − | ) | + | |
| − | ON "alias4"."col2" = "alias3".id_employee | + | |
| − | + | ||
</source> | </source> | ||
Текущая версия на 14:11, 23 марта 2011
Oracle, бывает, очень прикольно исполняет некоторые запросы.
Например, здесь, если раскомментировать /* ORDER BY "alias1".id_employee */, результат запроса меняется!
SELECT "alias6".birth_date AS "col4", "alias6".e_category AS "col5", "alias6".id_chief AS "col6", "alias6".id_department AS "col7", "alias6".fire_date AS "col8", "alias6".first_name AS "col9", "alias6".hire_date AS "col10", "alias6".id_employee AS "col11", "alias6".last_name AS "col12", "alias6".dtm_modified AS "col13", "alias6".salary AS "col14", "alias6".e_sex AS "col15", "alias6".id_product AS "col16" FROM ( SELECT "alias1".id_employee AS "col123", CASE WHEN (EXISTS ( SELECT '1' AS "col1" FROM t_employee "alias2" WHERE "alias2".id_chief = "alias1".id_employee )) THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2" FROM t_employee "alias1" /* ORDER BY "alias1".id_employee - если раскомментировать, результат запроса меняется! */ ) "alias4" LEFT JOIN (t_employee "alias3" LEFT JOIN (t_employee "alias5" LEFT JOIN t_employee "alias6" ON "alias5".id_chief = "alias6".id_employee ) ON "alias3".id_chief = "alias5".id_employee ) ON "alias4"."col2" = "alias3".id_employee ORDER BY "alias4"."col123"
