重寫查詢，將多個select陳述句轉換為一個select-有解無憂

我有以下程式。它有 4 個 select 陳述句，其中一個是根據傳入的引數選擇的。每次我必須添加一個新列時，我必須確保將它添加到所有 select 陳述句中。如果我錯過向其中一個 select 陳述句添加一列，則應用程式會遇到錯誤。我試圖找到一種方法，只需要一個 select 陳述句并根據傳入的引數獲得適當的結果。我不想使用動態 SQL 來實作它。有沒有其他方法可以做到？謝謝！

Create Procedure usp_region_order
@regionId varchar(100),
@custId   varchar(100)
As
Begin

If @regionId = 'North'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionNorth rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'South'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionSouth rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'East'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionEast rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'West'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionWest rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
End /* procedure ends */

uj5u.com熱心網友回復：

您可以形成所有區域的行內表，然后使用單次查詢：

BEGIN
    SELECT o.orderId, o.ordername, c.custName, c.custPhone
    FROM dbo.customer c
    INNER JOIN dbo.order o ON c.custId = o.custId
    INNER JOIN dbo.region r ON o.regionId = r.regionId
    INNER JOIN
    (
        SELECT regionId, 'North' AS region FROM dbo.regionNorth UNION ALL
        SELECT regionId, 'South' FROM dbo.regionSouth           UNION ALL
        SELECT regionId, 'East' FROM dbo.regionEast             UNION ALL
        SELECT regionId, 'West' FROM dbo.regionWest
    ) rn
        ON rn.regionId = r.regionId
    WHERE
        c.custId = @custId AND
        r.regionId = @regionId AND
        rn.region = @regionId
END

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/382453.html

標籤：sql sql-server 查询语句存储过程

上一篇：為同一ID選擇ALLMAX個日期

下一篇：如何在SQL中獲取一個月的每一天？