如何在存储过程中使用JOIN？

Question

我有一个不需要参数并返回两个字段的存储过程。这个存储过程会汇总所有应用于租户的交易，并返回租户的余额和id。

我想使用它返回的记录集进行查询，并需要根据租户的id将其结果连接。

这是我当前的查询：

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,
        u.UnitNumber,
        p.PropertyName
FROM tblTenant t
    LEFT JOIN tblRentalUnit u
    ON t.UnitID = u.ID

    LEFT JOIN tblProperty p
    ON u.PropertyID = p.ID

ORDER BY p.PropertyName, t.CarPlateNumber

存储过程如下所示：

SELECT tenant.ID AS TenantID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance FROM tblTenant tenant
    LEFT JOIN tblTransaction trans
    ON tenant.ID = trans.TenantID
    GROUP BY tenant.ID

我希望也将储存过程中的余额添加到其中。

我该怎么做？

- Malfist

---

租户=用户。我想在查询中添加余额，因为这是一份报告，它需要有关租户居住物业的所有信息。 - Malfist

是的，它返回一个记录集（这就是问题所指定的），每个租户一行。 - Malfist

@Malfist - 我的答案为您的问题提供了最佳解决方案（希望有一个单独的SQL片段来计算租户余额并将其加入到其他查询中）。 - cjk

顺带一提，你应该使用“性别”而不是“性别”。 - vy32

大家好！那么真正的答案是什么呢？下面所有的“答案”都是通过避免直接使用所需的存储过程来解决问题的方法。你不能在存储过程上使用CROSS APPLY，但是存储过程可以返回表格，为什么不呢？ - Fandango68

Answer 1

将存储过程的结果插入到临时表中，然后进行连接：

CREATE TABLE #Temp (
    TenantID int, 
    TenantBalance int
)

INSERT INTO #Temp
EXEC TheStoredProc

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,
    u.UnitNumber, p.PropertyName
FROM tblTenant t
INNER JOIN #Temp ON t.TenantID = #Temp.TenantID
...

- devio

Answer 2

我实际上喜欢之前的答案（不要使用SP），但是如果出于某些原因你与SP本身有关联，那么您可以使用它来填充一个临时表，然后在临时表上进行连接。请注意，这将导致额外的开销，但这是我能想到的唯一使用实际存储过程的方法。

同样，您可能最好将SP中的查询内联到原始查询中。

- AllenG

---

我宁愿不将其内联，因为如果我改变了 SP，就必须寻找到我内联的每个地方并进行更改。 - Malfist

@Malfist，如果你坚持要它是一个存储过程而不是函数或内联函数，那么你唯一的选择就是采用AllenG建议的方法，填充一个临时表并在其上进行连接。 - Lieven Keersmaekers

尝试使用表变量代替临时表。 - David

@AllenG，很抱歉但那不是一个答案。“不要使用SP”是避免使用CROSS APPLY的一种方式，但如果这样做不起作用，那么答案应该简单地是“不，你不能这样做。”。说要避免SP并将逻辑改为函数也不是答案。 - Fandango68

Answer 3

简短的回答是“你不能”。你需要做的是使用子查询或将现有的存储过程转换成表函数。将其创建为函数取决于你需要它的可重用性。

- CAbbott

---

一个返回表格的UDF（用户自定义函数）会非常有用。 - TheTXI

你说要使用“子查询”。那不是一个解决方案吗？请用一些代码解释一下你的意思。谢谢。 - Fandango68

Answer 4

您的存储过程可以很容易地作为视图来使用。然后，您可以将其与其他需要的内容连接起来。

SQL:

CREATE VIEW vwTenantBalance
AS

 SELECT tenant.ID AS TenantID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance 
 FROM tblTenant tenant
 LEFT JOIN tblTransaction trans
 ON tenant.ID = trans.TenantID
 GROUP BY tenant.ID

你可以执行任何语句，例如：

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, 
    t.Memo, u.UnitNumber, p.PropertyName, TenantBalance
FROM tblTenant t
LEFT JOIN tblRentalUnit u
 ON t.UnitID = u.ID
LEFT JOIN tblProperty p
 ON u.PropertyID = p.ID
LEFT JOIN vwTenantBalance v 
 ON t.ID = v.tenantID
ORDER BY p.PropertyName, t.CarPlateNumber

- cjk

---

我认为您会发现这个答案比使用临时表要好得多，因为它仍然是一段集中的代码，只需要在一个地方更新，并且没有创建临时表、插入数据和再次查询的开销。 - cjk

Answer 5

这个问题已经有答案了，最好的解决方法是将存储过程转换为SQL函数或视图。

简短的回答是，除非您将存储过程的输出放入临时表并加入临时表，否则无法直接在SQL中连接存储过程。

我将通过将您的存储过程转换为SQL函数，并向您展示如何在所选查询中使用它来回答此问题。

CREATE FUNCTION fnMyFunc()
RETURNS TABLE AS
RETURN 
(
  SELECT tenant.ID AS TenantID, 
       SUM(ISNULL(trans.Amount,0)) AS TenantBalance 
  FROM tblTenant tenant
    LEFT JOIN tblTransaction trans ON tenant.ID = trans.TenantID
  GROUP BY tenant.ID
)

现在要使用该函数，在您的SQL中...

SELECT t.TenantName, 
       t.CarPlateNumber, 
       t.CarColor, 
       t.Sex, 
       t.SSNO, 
       t.Phone, 
       t.Memo,
       u.UnitNumber,
       p.PropertyName
FROM tblTenant t
    LEFT JOIN tblRentalUnit u ON t.UnitID = u.ID
    LEFT JOIN tblProperty p ON u.PropertyID = p.ID
    LEFT JOIN dbo.fnMyFunc() AS a
         ON a.TenantID = t.TenantID
ORDER BY p.PropertyName, t.CarPlateNumber

如果您希望从上述SQL中向函数传递参数，则建议使用CROSS APPLY或CROSS OUTER APPLY。请参阅此处了解更多信息。祝好！

- Fandango68

Answer 6

我解决了这个问题，写了一个函数代替存储过程，并在SQL语句中使用了CROSS APPLY。这个解决方案适用于SQL 2005及更高版本。

- Gediminas Bukauskas

Answer 7

以下是需要翻译的内容：

给你提供一个糟糕的想法。

使用别名，从您的服务器创建一个新的链接服务器到自己的别名。

现在您可以执行以下操作：

select a.SomeColumns, b.OtherColumns
from LocalDb.dbo.LocalTable a
inner join (select * from openquery([AliasToThisServer],'
exec LocalDb.dbo.LocalStoredProcedure
') ) b
on a.Id = b.Id

- Devin Lamothe

---

好可怕的想法，我从未想过。 - Costa

通过使用这个解决方案，我们会面临哪些风险？ - Daniel L. VanDenBosch

Answer 8

我希望你的存储过程没有使用游标循环！

如果没有，将存储过程中的查询与您在此处发布的查询整合起来。

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,
        u.UnitNumber,
        p.PropertyName
        ,dt.TenantBalance
FROM tblTenant t
    LEFT JOIN tblRentalUnit u ON t.UnitID = u.ID
    LEFT JOIN tblProperty   p ON u.PropertyID = p.ID
    LEFT JOIN (SELECT ID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance
                   FROM tblTransaction
                   GROUP BY tenant.ID
              ) dt ON t.ID=dt.ID
ORDER BY p.PropertyName, t.CarPlateNumber

如果你在存储过程中执行的不仅仅是查询操作，那么可以创建一个临时表，并将存储过程执行结果插入到这个临时表中，然后在查询中连接该临时表。

create procedure test_proc
as
  select 1 as x, 2 as y
  union select 3,4 
  union select 5,6 
  union select 7,8 
  union select 9,10
  return 0
go 

create table #testing
(
  value1   int
  ,value2  int
)

INSERT INTO #testing
exec test_proc


select
  *
  FROM #testing

- KM.

---

我正在问如何集成SP，因为我不知道该怎么做。 - Malfist

我相信SP正在执行游标循环。 - Malfist

Answer 9

为什么不直接在你的SQL中进行计算呢？

SELECT 
  t.TenantName
  , t.CarPlateNumber
  , t.CarColor
  , t.Sex
  , t.SSNO
  , t.Phone
  , t.Memo
  , u.UnitNumber
  , p.PropertyName
  , trans.TenantBalance
FROM tblTenant t
     LEFT JOIN tblRentalUnit u ON t.UnitID = u.ID
     LEFT JOIN tblProperty p ON u.PropertyID = p.ID
     INNER JOIN (
       SELECT tenant.ID AS TenantID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance 
       FROM tblTenant tenant
            LEFT JOIN tblTransaction trans ON tenant.ID = trans.TenantID
       GROUP BY tenant.ID
     ) trans ON trans.ID = t.ID
ORDER BY 
  p.PropertyName
  , t.CarPlateNumber

- Lieven Keersmaekers