在sql server中交叉应用

发布于 2024-11-14 19:00:52 字数 704 浏览 8 评论 0原文

最近我的存储过程遇到了问题，它运行得太慢，所以我的一位同事建议交叉应用，他说交叉应用作为内连接工作，但不需要表之间的comman key。

我的情况是我有三个表 crossarticle_article 和 crossarticle_articletocategory 和 crossarticle_article 现在我想检索 categoryid 为 4 的所有记录，categoryid 存储在 crossarticle_articletocategory 中，

据我所知，我创建了下面的查询：

SELECT *
FROM   crossarticle_article c
       CROSS APPLY
crossarticle_articletocategory cc1
CROSS APPLY
crossarticle_category cc2
WHERE  cc2.id = 1

此查询应仅返回来自 crossarticle_article 且类别 id 与 1 匹配的记录但它返回所有 3 个表中的所有记录。

我哪里出错了，如果这不是交叉应用的使用方式那么如何使用，它的优点是什么。

交叉应用是否适用于相同的表...如果是的话，那么可能的情况是什么...

原文

recently i was having a problem in my stored procedure, it was working too slow, so one of my colleague suggested cross apply, he said that cross apply work as inner join but without the need of comman key between the table.

my senario is i have three tables crossarticle_article and crossarticle_articletocategory, and crossarticle_article
now i want to retreive all the records whose categoryid is 4, categoryid are stored in crossarticle_articletocategory

as much i understood, i created below query for it:

SELECT *
FROM   crossarticle_article c
       CROSS APPLY
crossarticle_articletocategory cc1
CROSS APPLY
crossarticle_category cc2
WHERE  cc2.id = 1

this query should return only records from crossarticle_article where category id matches 1
but it returns all the records from all 3 tables.

where i am going wrong,
if this is not the way of using cross apply then how to use, and whats the advantage of it.

is it that cross apply works with same tables... if so than what could be the scenario...

分享到QQ

分享到微博

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

发布评论

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

兔小萌 2024-11-21 19:00:52

假设 id 是所有表中的关键字段，查询应该是：

SELECT c.*,
       c1.colXXX,
       c2.colYYY --ALL columns  u need
FROM   crossarticle_article c
       CROSS APPLY(
    SELECT cc1.colXXX
    FROM   crossarticle_articletocategory cc1
    WHERE  cc1.id = c.id
) c1
CROSS APPLY(
    SELECT cc2.colYYY
    FROM   crossarticle_category cc2
    WHERE  cc2.id = c.id
) c2
WHERE  c.id = 4

Assuming id is the key field in all tables, The Query should be:

SELECT c.*,
       c1.colXXX,
       c2.colYYY --ALL columns  u need
FROM   crossarticle_article c
       CROSS APPLY(
    SELECT cc1.colXXX
    FROM   crossarticle_articletocategory cc1
    WHERE  cc1.id = c.id
) c1
CROSS APPLY(
    SELECT cc2.colYYY
    FROM   crossarticle_category cc2
    WHERE  cc2.id = c.id
) c2
WHERE  c.id = 4

回复收藏 0 原文

~没有更多了~