Geek asks and answers

SQL SELECT expression expression value expression for another expression

I have a table with a huge number of rows in mysql (although I am looking for a universal SQL solution)

very_big_table(INT a, INT b, INT c, ...)

I need a SELECT statement

SELECT a, 
    (b + c) as expression1,
    (b + c + a) AS expression2 -- basically (expression1 + a)
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

It looks good and easy to read, while expression1 is simple.
But when CASE-WHEN / IFNULL () / SUM () / MIN () / STRCAT () or some operator comes into play in these expressions, it is difficult to read and debug it.

I examined some of the questions already asked
assigning a mysql value to an inline variable.
Use a column value for another column (SQL Server)? How to use conditional column values ​​in the same select clause?

But if I use the approaches described approximately as

SELECT a, 
    expression1,
    (expression1 + a) AS expression2
FROM 
    (SELECT a,
        (b + c) AS expression1
    FROM very_big_table
    WHERE ...
    GROUP BY a) as inner_table
ORDER BY a DESC

, 70 . , , .
, ?

- , ?

, select SQL? (, SELECT , )

+6
4

. SQL :

SELECT a, 
    @expr1 := (b + c) as expression1,
    (@expr1 + a) AS expression2
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

.

+4

2 .

 select a,exp1,exp1+a as exp2
 from (SELECT a 
             ,(b + c) as exp1
       FROM very_big_table
       WHERE ...
       GROUP BY a
 )V
 ORDER BY a DESC

...

SELECT a 
      ,OA.exp1 as expression1
      ,(OA.exp1 + a) AS expression2 -- basically (expression1 + a)
FROM very_big_table
outer apply (select (b + c) as exp1) OA
WHERE ...
GROUP BY a
ORDER BY a DESC

, , , ...

, Outer apply

, exp1 .

, , , .

, ?

+2

, sql select, /, , " ", - "look" ":

select a
, myfunction( b, c)
, myfunction( b, c) + a
...

- " ", ,

- " ", , , ,

0

Postgresql

/ ,

create table x
(
    a int not null,
    b int not null,
    c int not null
);

create table y
(
    a int not null,
    z int not null
);


select * from x;

insert into x VALUES
(1,2,3), (4,5,6);

insert into y values
(1, 100);

/ :

create or replace function computer
(
    rx x, -- this emphasizes that the source of data come from table x
    ry y, -- this emphasizes that the source of data come from table x

    out expression1 int, out expression2 int, out expression3 int
)
as $$
begin
    expression1 := rx.b + rx.c;
    expression2 := expression1 + rx.a;
    expression3 := expression2 + ry.z;
end;
$$ language 'plpgsql';

:

select x.a, x.b, x.c, y.z, answer.*
from x
join y on x.a = y.a
cross join lateral computer(x,y) answer

:

enter image description here

, :

select x.a, x.b, x.c, y.z, (computer(x, y)).*
from x
join y on x.a = y.a

, , 50 , 50 . cross join lateral . : "" PostgreSQL ?

, , record

create or replace function computer_b
(
    anon record,
    out expression1 int, out expression2 int, out expression3 int
)
as $$
begin
    expression1 := anon.b + anon.c;
    expression2 := expression1 + anon.a;
    expression3 := expression2 + anon.z;
end;
$$ language 'plpgsql';

:

with data_source as
(
    select x.*, y.z
    from x
    join y on x.a = y.a
)
select ds.*, answer.*
from data_source ds
cross join lateral computer_b(ds) answer
0

More articles:


All Articles