可以存储一个选择列的值并将其用于下一个吗? [英] Possible to store value of one select column and use it for the next one?

问题描述

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

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 <= 50 那么一行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

这篇关于可以存储一个选择列的值并将其用于下一个吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！