在T-SQL中，如何在子查询中引用表变量? [英] In T-SQL, how to reference to table variable in the subquery?

问题描述

我已经声明了一个表变量'@t'，并正确执行了'INSERT-INTO-SELECT'. 当我尝试使用每组行编号的一些额外计算查询表变量时，我收到错误消息，或者直接使用'@t'时出现必须声明变量"，而使用'@t的别名时出现无效的对象名" '.请指教.

I've declared a table variable '@t', and have correctly performed the 'INSERT-INTO-SELECT'. When I was trying to query the table variable with some additional computation for per-group row numbering, I got error either "Must declare the variable" when using '@t' directly or "invalid object name" while using alias of '@t'. Please kindly advise.

SELECT 
    *,
    (SELECT COUNT(*) FROM "LTV" "COUNTER"
     WHERE 
        "COUNTER"."Collateral_ID" = "LTV"."Collateral_ID"
        AND
        "COUNTER"."m_il_no" = "LTV"."m_il_no"
        AND
        "COUNTER"."Ref_Key" <= "LTV"."Ref_Key"
     GROUP BY "COUNTER"."Collateral_ID", "COUNTER"."m_il_no"
    ) "MIL_IDX"

FROM @t AS LTV

推荐答案

使用:

SELECT x.*,
       y.num
  FROM @t x
  JOIN (SELECT t.collateral_id,
               t.m_il_no,
               COUNT(*) AS num
          FROM @t t
      GROUP BY t.collateral_id, t.m_il_no) y ON y.collateral_id = x.collateral_id
                                            AND y.m_il_no = x.m_il_no

这篇关于在T-SQL中，如何在子查询中引用表变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！