代码之家 › 专栏 › 技术社区 › Booji Boy

sql交叉连接-有人发现它有什么用吗?[关闭]

cross-join tsql database sql-server

Booji Boy · 技术社区 · 17 年前

今天,在sql server开发的10年里,我第一次在生产查询中使用了交叉连接。我需要将结果集添加到报告中,发现在两个表之间使用创造性的where子句进行交叉连接是一个很好的解决方案。我想知道有人发现交叉连接在生产代码中有什么用?

更新:Tony Andrews发布的代码非常接近我使用交叉连接的目的。相信我,我理解使用交叉连接的含义,不会轻易这样做。我很高兴终于使用了它(我是个书呆子)——有点像我第一次使用完整的外部连接。

感谢大家的回答!以下是我如何使用交叉连接:

SELECT  CLASS, [Trans-Date] as Trans_Date,
SUM(CASE TRANS
     WHEN 'SCR' THEN [Std-Labor-Value]
     WHEN 'S+' THEN [Std-Labor-Value]
     WHEN 'S-' THEN [Std-Labor-Value]
     WHEN 'SAL' THEN [Std-Labor-Value]
     WHEN 'OUT' THEN [Std-Labor-Value]
     ELSE 0
END) AS [LABOR SCRAP],
SUM(CASE TRANS
     WHEN 'SCR' THEN  [Std-Material-Value]
     WHEN 'S+' THEN [Std-Material-Value]
     WHEN 'S-' THEN  [Std-Material-Value]
     WHEN 'SAL' THEN [Std-Material-Value]
     ELSE 0
END) AS [MATERIAL SCRAP], 
SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR REWORK],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Act-Labor-Value]
     WHEN 'TRN' THEN  [Act-Labor-Value]
     WHEN 'RWK' THEN  [Act-Labor-Value]
     ELSE 0 
END) AS [ACTUAL LABOR],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Std-Labor-Value]
     WHEN 'TRN' THEN   [Std-Labor-Value]
     ELSE 0 
END) AS [STANDARD LABOR],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Act-Labor-Value] - [Std-Labor-Value]
     WHEN 'TRN' THEN  [Act-Labor-Value] - [Std-Labor-Value]
     --WHEN 'RWK' THEN  [Act-Labor-Value]
     ELSE 0 END) -- - SUM([Std-Labor-Value]) -- - SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) 
AS [LABOR VARIANCE] 
FROM         v_Labor_Dist_Detail
where [Trans-Date] between @startdate and @enddate
    --and CLASS = (CASE @class WHEN '~ALL' THEN CLASS ELSE @class END)
GROUP BY  [Trans-Date], CLASS
UNION  --REL 2/6/09 Pad result set with any missing dates for each class. 
select distinct [Description] as class,  cast([Date] as datetime) as [Trans-Date], 0,0,0,0,0,0 
FROM Calendar_To_Fiscal cross join PRMS.Product_Class
where cast([Date] as datetime) between @startdate and @enddate and
not exists (select class FROM v_Labor_Dist_Detail vl where [Trans-Date] between @startdate and @enddate
                    and vl.[Trans-Date] = cast(Calendar_To_Fiscal.[Date] as datetime)
                    and vl.class= PRMS.Product_Class.[Description]
                GROUP BY [Trans-Date], CLASS)
order by [Trans-Date], CLASS

8 回复 | 直到 15 年前

MatBailie 17 年前

交叉连接的典型合法用途是显示按产品和地区划分的总销售额的报告。如果产品P在R区域没有销售,那么我们希望看到一行有零,而不仅仅是不显示一行。

select r.region_name, p.product_name, sum(s.sales_amount)
from regions r
cross join products p
left outer join sales s on  s.region_id = r.region_id
                        and s.product_id = p.product_id
group by r.region_name, p.product_name
order by r.region_name, p.product_name;

Tony Andrews 17 年前

我经常遇到的一个用途是将记录拆分为几个记录,主要是为了报告目的。

想象一个字符串,其中每个字符代表相应小时内的某个事件。

ID | Hourly Event Data
1  | -----X-------X-------X--
2  | ---X-----X------X-------
3  | -----X---X--X-----------
4  | ----------------X--X-X--
5  | ---X--------X-------X---
6  | -------X-------X-----X--

现在,您需要一份报告,显示在哪一天发生了多少事件。将表与ID为1到24的表交叉连接,然后施展魔法。..

SELECT
   [hour].id,
   SUM(CASE WHEN SUBSTRING([data].string, [hour].id, 1) = 'X' THEN 1 ELSE 0 END)
FROM
   [data]
CROSS JOIN
   [hours]
GROUP BY
   [hours].id

JeffO 17 年前

我有不同的报告对记录集进行预过滤(按公司内的不同业务线),但有些计算需要公司范围内的收入百分比。记录来源必须包含公司总额,而不是依赖于在报告本身中计算总金额。

示例:记录集包含每个客户的余额以及客户收入来源的业务线。该报告可能只显示“零售”客户。无法获得整个公司的余额总和,但报告显示了公司收入的百分比。

由于有不同的余额字段,我觉得与具有多个余额的视图进行完全连接(我也可以重用此公司总计视图)而不是由子查询组成的多个字段不那么复杂。

另一个是更新语句,其中需要创建多条记录(预设工作流过程中的每个步骤一条记录)。

Paul M Simpson 12 年前

这里有一个,其中CROSS JOIN替代了INNER JOIN。当要连接的两个表之间没有相同的值时,这是有用和合法的。例如,假设您有一个表,其中包含某个语句或公司文档的版本1、版本2和版本3,所有这些都保存在SQL Server表中,这样您就可以在订单发出后很长一段时间内,以及在文档被重写为新版本后很长时间内,动态地重新创建与订单关联的文档。但是,您需要连接的两个表中只有一个(Documents表)有VersionID列。以下是一种方法:

SELECT DocumentText, VersionID = 
    (
    SELECT d.VersionID                               
    FROM Documents d 
    CROSS JOIN Orders o
    WHERE o.DateOrdered BETWEEN d.EffectiveStart AND d.EffectiveEnd
    )
FROM Documents

joshperry 17 年前

我最近在一份用于销售预测的报告中使用了CROSS JOIN,该报告需要列出销售人员在每个总账账户中完成的销售额。

因此,在报告中,我做了一些这样的事情:

SELECT gla.AccountN, s.SalespersonN
FROM
    GLAccounts gla
    CROSS JOIN Salesperson s
WHERE (gla.SalesAnalysis = 1 OR gla.AccountN = 47500)

这给了我每个销售人员的每个总账账户,比如:

SalesPsn    AccountN
1000    40100
1000    40200
1000    40300
1000    48150
1000    49980
1000    49990
1005    40100
1005    40200
1005    40300
1054    48150
1054    49980
1054    49990
1078    40100
1078    40200
1078    40300
1078    48150
1078    49980
1078    49990
1081    40100
1081    40200
1081    40300
1081    48150
1081    49980
1081    49990
1188    40100
1188    40200
1188    40300
1188    48150
1188    49980
1188    49990

pro 14 年前

用于图表(报告),其中每个分组都必须有一条记录,即使它为零。 (例如RadCharts)

Haider Ali 11 年前

我从源数据中获得了破产字段的组合。有5种不同的类型,但数据中有2种类型的组合。因此,我创建了5个不同值的查找表,然后对插入语句使用交叉连接来填写其余的值

insert into LK_Insolvency (code,value)

select a.code+b.code, a.value+' '+b.value 
from LK_Insolvency a
cross join LK_Insolvency b
where a.code <> b.code <--this makes sure the x product of the value with itself is not used as this does not appear in the source data.

-3

Brettski 17 年前

我个人在查询中尽量避免使用笛卡尔积。我想有一个你的连接的每个组合的结果集可能是有用的,但通常如果我最终得到一个,我知道我出了什么问题。