可以存储一个选择列的值并将其用于下一个吗?

问题描述：

是否可以存储或缓存属于一个选择列的值，然后将其用于下一个?例如，

Is it possible to store or cache values that are part of one select column and then be used for the next one? For example,

select 
       FirstColumn = (complex query returns a value based on ThirdColumn),
       SecondColumn = (uses the same value returned from above + does some additional calculations.)
from SomeTable

是否可以执行类似的操作，这样我就不必两次编写相同的复杂查询?

Is it possible to do something like that so I don't have to write the same complex query twice?

答

这里需要CROSS APPLY，它可以引用外部引用，不需要烦人的子查询或CTE:

You need CROSS APPLY here, it can refer to outer references, no annoying subqueries or CTEs needed:

select col1, col2
from table1 as outer_table

-- can also have multi-row values
cross apply (values (complex_expression_1) ) as v1 (col1)
cross apply (values (expression_referring_to_col1) ) as v2 (col2)

-- alternate syntax, select without from returns a single row
cross apply (select complex_expression_1 as col1 ) AS v1
cross apply (select expression_referring_to_col1 as col2 ) as v2

-- you can also do anything you like in there, can be one or multiple rows
cross apply (
    select complex_expression_1 as col1 
    from othercomplexjoin as o
    where o.join_column = outer_table.join_column
) AS v1

您可以使用 `APPLY` 做的更多技巧:

1.每组子表前 1:

每组前1名"的经典解决方案；是使用 row_number().这通常会导致大量的扫描，特别是当不同的外部值的数量相对于子表来说很小时.

A classic solution to the "top 1 per group" is to use row_number(). This can often result in huge scans, especially when the number of distinct outer values is small relative to the child table.

select
    o.id,
    lastPayment.Date
from order_header as o
join
( select *, row_number() over (partition by order_id order by date desc) as rn
 from payments
) as lastPayment on ...
where lastPayment.rn = 1

相反，我们可以这样做:

Instead we can do:

select
    o.id,
    lastPayment.Date
from order_header as o
cross apply
( select top (1) *
 from payments as p
 where p.order_id = o.id
 order by date desc
) as lastPayment

注意:OUTER APPLY 在概念上替换了左连接，即返回空值而不是无行.

Note: OUTER APPLY conceptually replaces a left join, i.e. returns nulls instead of no rows.

2.反转

select
    o.id,
    customer.*
from order_header as o
cross apply ( values    -- This returns two rows for every order_header
    ( 'DeliveryCustomer', o.deliveryCustomer ),
    ( 'billingCustomer', o.billingCustomer )
) as customer (type, name)

3.以可变次数展开一行:

假设我们要取一个金额，并将其分成不同的行.如果amount 那么一行amount，如果>50 然后是两行，50 之一，其余之一:

Say we want to take an amount, and split it into different rows. If the amount <= 50 then one row of amount, if > 50 then two rows, one of 50 and one of the rest:

select t.id, v.amount
from table as t
cross apply (
    select case when amount > 50 then 50 else amount end as amount
    union all
    select amount - 50   -- note this row will not appear if amount < 50
    where amount > 50
) v

可以存储一个选择列的值并将其用于下一个吗?

您可以使用 APPLY 做的更多技巧:

相关推荐

您可以使用 `APPLY` 做的更多技巧: