派生表多次使用

发布于 2024-12-09 17:54:38 字数 505 浏览 2 评论 0原文

我需要使用相同的派生表运行 3 个连续查询...我正在使用 MS SQL Server 2008

Select a,b,c from categories inner join (select x,y,z from derivedTable where…) …
Select a,b,c from users inner join (select x,y,z from derivedTable where…) …
Select a,b,c from orders inner join (select x,y,z from derivedTable where…) …

的方式同时运行 3 个 sql 查询

(select x,y,z from derivedTable where ...)

有没有办法以派生表仅执行一次

？我正在使用 .net 所以...我想知道是否可以返回 3 个表并加载包含 3 个表的数据集。

谢谢

原文

I need to run 3 consecutive queries using the same derived table... I'm using MS SQL Server 2008

Select a,b,c from categories inner join (select x,y,z from derivedTable where…) …
Select a,b,c from users inner join (select x,y,z from derivedTable where…) …
Select a,b,c from orders inner join (select x,y,z from derivedTable where…) …

Is there a way to run the 3 sql queries at the same time in a way the derived table

(select x,y,z from derivedTable where ...)

is executed only once?

I’m using .net so... I’m wondering if I could return the 3 tables and load a dataset with the 3 tables.

Thanks

分享到QQ

分享到微博

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

发布评论

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

小梨窩很甜 2024-12-16 17:54:38

您可以使用 WITH：

注意： 正如 @Martin 所指出的，WITH 会被评估多次，因此正确的解决方案是临时表。

WITH derivedTable (x,y,z)
as
(
    select x,y,z
      from derivedTable
      ...
)
SELECT a, b, c
  FROM users
  join derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join derivedtable on ...
 WHERE ... 
union all
 ...

或临时表：

select x,y,z
  into #derivedTable
  from derivedTable
  ...

SELECT a, b, c
  FROM users
  join #derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join #derivedtable on ...
 WHERE ... 
union all
 ...

You could use WITH:

Note: As pointed by @Martin WITH is evaluated several times so the correct solution is the temporary table.

WITH derivedTable (x,y,z)
as
(
    select x,y,z
      from derivedTable
      ...
)
SELECT a, b, c
  FROM users
  join derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join derivedtable on ...
 WHERE ... 
union all
 ...

or a temporary table:

select x,y,z
  into #derivedTable
  from derivedTable
  ...

SELECT a, b, c
  FROM users
  join #derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join #derivedtable on ...
 WHERE ... 
union all
 ...

回复收藏 0 原文

~没有更多了~