Portál AbcLinuxu, 19. července 2025 12:45
Ahoj,
je možné udělat v SELECTu, že výsledek prvního výrazu se použije ve druhém výrazu? Například něco jako toto:
SELECT 1+2 INTO @x, 4*@x;
To samozřejmě nefunguje, ale třeba to nějak jde. Zatím to dělám takto:
SELECT 1+2 INTO @x; SELECT 4*@x;
Jde to udělat jedním SELECTem, nebo je nutno použít dva?
Řešení dotazu:
SELECT 4*(1+2);
Ono je to moc zjednodušené, v tom prvním výrazu je prostě komplikovanější výpočet. A ve druhém je výraz, kde použiji výsledek prvního výrazu. Pokud to chci mít v jednom selectu, tak musim ten komplikovaný výpočet provést ještě jednou, to samozřejmě lze. Jen mě napadlo, zda-li na to neexistuje finta, neopakovat ještě jednou ten komplikovaný výpočet.
SELECT 1+2 AS x, 4*x. Ale nevím, zda to projde, asi to bude záviset i na konkrétní databázi.
Toto a podobné jsem zkoušel na MySQL a bohužel nic.
12.9.2009 17:44
xkucf03 | skóre: 49
| blog: xkucf03
Např. v PostgreSQL to nefunguje. Ale obecně funguje vnořený dotaz:
SELECT x, 4*x FROM (SELECT 1+2 AS x) AS z
select cx, cx from (select code as cx FROM base.centers) as scxkde cx je předvipočítáno a může se zůčastnit dalších výpočtů.
Zjednodusil jsi to mozna az trochu moc brutalne;), ale jinak jestli chapu dobre co chces, tak v Oracle a PostgreSQL (nevim, jak je na tom Firebird a vim, ze MySQL to neumi) existuje WITH statement, neco jako (pgsql):
WITH
AS ( SELECT 1+2 AS kouzelnenumero )
SELECT
kouzelnenumero, 4*kouzelnenumero FROM tri
err... WITH tri AS [...] ... je brzo rano 
Pointa WITH (factoring klauzule) je hlavně v tom, že materializuje. Bez indexů. A z toho vyplývá i využití. 
Třeba Oracle umí udělat in-line view, ale někdy ne, což je zákeřné.
Následující příklad ukazuje in-line view:
8.9.2009 20:45
CREATE TABLE my_all_tables (
table_id NUMBER(10) NOT NULL,
table_name VARCHAR2(30) NOT NULL)
/
ALTER TABLE my_all_tables ADD CONSTRAINT pk_mat_tid PRIMARY KEY (table_id)
/
CREATE TABLE my_all_schemas (
schema_name VARCHAR2(30) NOT NULL,
table_id NUMBER(10) NOT NULL)
/
INSERT ALL
INTO my_all_tables (table_id, table_name)
VALUES (table_id1, table_name1)
INTO my_all_schemas (schema_name, table_id)
VALUES (table_schema1, table_id1)
SELECT
ROW_NUMBER() OVER (ORDER BY att.table_name ASC) AS table_id1,
att.owner AS table_schema1,
att.table_name AS table_name1
FROM
all_tables att
/
COMMIT
/
ALTER TABLE my_all_schemas ADD CONSTRAINT fk_mas_mat FOREIGN KEY (table_id) REFERENCING my_all_tables (table_id)
/
CREATE INDEX idx_mat_tn ON my_all_tables (table_name)
/
EXPLAIN PLAN FOR SELECT
'"' || mas.schema_name || '"."' || mat.table_name || '"' AS full_name
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)
WHERE
mat.table_name LIKE 'C%'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/
EXPLAIN PLAN FOR SELECT
'"' || dta.schema_name || '"."' || dta.table_name || '"' AS full_name
FROM
(SELECT
mas.schema_name,
mat.table_name
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta
WHERE
dta.table_name LIKE 'C%'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/
EXPLAIN PLAN FOR WITH dta AS (SELECT
mas.schema_name,
mat.table_name
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id))
SELECT
'"' || dta.schema_name || '"."' || dta.table_name || '"' AS full_name
FROM
dta
WHERE
dta.table_name LIKE 'C%'
/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/
DROP TABLE my_all_schemas PURGE
/
DROP TABLE my_all_tables PURGE
/
A výstup:
CREATE TABLE succeeded.
ALTER TABLE my_all_tables succeeded.
CREATE TABLE succeeded.
5254 rows inserted
COMMIT succeeded.
ALTER TABLE my_all_schemas succeeded.
CREATE TABLE succeeded.
ALTER TABLE my_all_tables succeeded.
CREATE TABLE succeeded.
5254 rows inserted
COMMIT succeeded.
ALTER TABLE my_all_schemas succeeded.
CREATE INDEX succeeded.
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 171 | 10260 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 171 | 10260 | 11 (10)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES | 171 | 5130 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_MAT_TN | 171 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 78810 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
3 - access("MAT"."TABLE_NAME" LIKE 'C%')
filter("MAT"."TABLE_NAME" LIKE 'C%')
Note
-----
- dynamic sampling used for this statement
22 rows selected
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 171 | 10260 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 171 | 10260 | 11 (10)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES | 171 | 5130 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_MAT_TN | 171 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 78810 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
3 - access("MAT"."TABLE_NAME" LIKE 'C%')
filter("MAT"."TABLE_NAME" LIKE 'C%')
Note
-----
- dynamic sampling used for this statement
22 rows selected
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 171 | 10260 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 171 | 10260 | 11 (10)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES | 171 | 5130 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_MAT_TN | 171 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 78810 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
3 - access("MAT"."TABLE_NAME" LIKE 'C%')
filter("MAT"."TABLE_NAME" LIKE 'C%')
Note
-----
- dynamic sampling used for this statement
22 rows selected
DROP TABLE my_all_schemas succeeded.
DROP TABLE my_all_tables succeeded.
Ale jakmile uděláš něco složitějšího:
WITH dta AS (SELECT
mas.schema_name,
mat.table_name,
LENGTH(mat.table_name) AS table_name_len
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id))
SELECT
'"' || dta.schema_name || '"."' || dta.table_name || '" (' || gen.val || ')' AS full_name
FROM
dta INNER JOIN (SELECT
gen.val
FROM
(SELECT
LEVEL AS val
FROM
dual
CONNECT BY
LEVEL <= (SELECT MAX(dta.table_name_len) FROM dta)) gen) gen
ON ((dta.table_name_len <= 1 AND gen.val = 1) OR (dta.table_name_len > 1 AND gen.val <= dta.table_name_len))
WHERE
dta.table_name LIKE 'C%'
/
pak LOAD AS SELECT a čau:
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 330330509
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 480 | 21 (5)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | HASH JOIN | | 2627 | 153K| 11 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 78810 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | MY_ALL_TABLES | 2627 | 78810 | 5 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 8 | 480 | 10 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 13 | | |
| 11 | VIEW | | 2627 | 34151 | 8 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_51DED0 | 2627 | 120K| 8 (0)| 00:00:01 |
|* 13 | VIEW | | 8 | 376 | 8 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_51DED0 | 2627 | 120K| 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
8 - filter(LEVEL<= (SELECT MAX("DTA"."TABLE_NAME_LEN") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1")
*/ "C0" "SCHEMA_NAME","C1" "TABLE_NAME","C2" "TABLE_NAME_LEN" FROM "SYS"."SYS_TEMP_0FD9D6608_51DED0"
"T1") "DTA"))
13 - filter("DTA"."TABLE_NAME" LIKE 'C%' AND ("DTA"."TABLE_NAME_LEN"<=1 AND "GEN"."VAL"=1 OR
"DTA"."TABLE_NAME_LEN">1 AND "GEN"."VAL"<="DTA"."TABLE_NAME_LEN"))
Note
-----
- dynamic sampling used for this statement
35 rows selected
Zatímco, když si ty dotazy rozkopíruješ:
SELECT
'"' || dta.schema_name || '"."' || dta.table_name || '" (' || gen.val || ')' AS full_name
FROM
(SELECT
mas.schema_name,
mat.table_name,
LENGTH(mat.table_name) AS table_name_len
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta
INNER JOIN (SELECT
gen.val
FROM
(SELECT
LEVEL AS val
FROM
dual
CONNECT BY
LEVEL <= (SELECT
MAX(dta.table_name_len)
FROM
(SELECT
mas.schema_name,
mat.table_name,
LENGTH(mat.table_name) AS table_name_len
FROM
my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta)) gen) gen
ON ((dta.table_name_len <= 1 AND gen.val = 1) OR (dta.table_name_len > 1 AND gen.val <= dta.table_name_len))
WHERE
dta.table_name LIKE 'C%'
/
dostaneš:
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2113348083
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 12 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 12 (9)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 43 | 6 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 43 | | |
|* 8 | HASH JOIN | | 2627 | 110K| 11 (10)| 00:00:01 |
| 9 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 34151 | 5 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | MY_ALL_TABLES | 2627 | 78810 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_MAT_TN | 171 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | MY_ALL_TABLES | 1 | 30 | 4 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | MY_ALL_SCHEMAS | 2627 | 78810 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
5 - filter(LEVEL<= (SELECT MAX(LENGTH("MAT"."TABLE_NAME")) FROM "MY_ALL_TABLES"
"MAT","MY_ALL_SCHEMAS" "MAS" WHERE "MAS"."TABLE_ID"="MAT"."TABLE_ID"))
8 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
11 - access("MAT"."TABLE_NAME" LIKE 'C%')
filter("MAT"."TABLE_NAME" LIKE 'C%')
12 - filter("GEN"."VAL"=1 AND LENGTH("MAT"."TABLE_NAME")<=1 OR
LENGTH("MAT"."TABLE_NAME")>1 AND "GEN"."VAL"<=LENGTH("MAT"."TABLE_NAME"))
Note
-----
- dynamic sampling used for this statement
36 rows selected
Ano, příklad není ideální, ale nic lepšího mě teď narychlo nenapadá.
Oracle 11gR1
SELECT @x:=1+2, 4*@x; +---------+------+ | @x:=1+2 | 4*@x | +---------+------+ | 3 | 12 | +---------+------+Je to tedy vyřešeno, jen jsem už zapoměl, proč jsem to vlastně chtěl...
Tiskni
Sdílej:
ISSN 1214-1267, (c) 1999-2007 Stickfish s.r.o.