在T-SQL中，如何在子查询中引用表变量？

发布于 2024-10-03 12:48:00 字数 493 浏览 6 评论 0原文

我已经声明了一个表变量“@t”，并正确执行了“INSERT-INTO-SELECT”。当我尝试对每组行编号进行一些额外计算来查询表变量时，出现错误“直接使用“@t”时必须声明变量”或“使用“@t”别名时“无效的对象名称” '。请各位指教。

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

帅气尐潴 2024-10-10 12:48:00

使用：

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

Use:

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

回复收藏 0 原文

~没有更多了~