使用嵌套查詢簡化查詢

如果可以的話，我想從下面的查詢中消除對嵌套查詢的需要，但我正在努力弄清楚如何做。

這是架構：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[expiration]
(
    [batch_number] [int] NOT NULL,
    [fruit_number] [int] NOT NULL,
    [store_number] [int] NOT NULL,
    [expiration_date] [date] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[fruits]
(
    [fruit_number] [int] NOT NULL,
    [fruit_name] [nvarchar](50) NOT NULL
) ON [PRIMARY]

這是資料：

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (1, 3, 4, CAST(N'2021-11-25' AS Date))

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (1, 2, 2, CAST(N'2021-11-22' AS Date))

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (1, 5, 3, CAST(N'2021-11-30' AS Date))

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (2, 2, 7, NULL)

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (2, 3, 2, CAST(N'2021-12-12' AS Date))

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (1, 1, 5, NULL)

INSERT INTO [dbo].[expiration] ([batch_number], [fruit_number], [store_number], [expiration_date]) 
VALUES (2, 1, 6, CAST(N'2021-11-28' AS Date))

INSERT INTO [dbo].[fruits] ([fruit_number], [fruit_name]) 
VALUES (1, N'banana')

INSERT INTO [dbo].[fruits] ([fruit_number], [fruit_name]) 
VALUES (2, N'apple')

INSERT INTO [dbo].[fruits] ([fruit_number], [fruit_name]) 
VALUES (3, N'pear')

INSERT INTO [dbo].[fruits] ([fruit_number], [fruit_name]) 
VALUES (4, N'peach')

INSERT INTO [dbo].[fruits] ([fruit_number], [fruit_name]) 
VALUES (5, N'strawberry')

這是我的查詢：

SELECT
    fruit_number, 
    MAX(expirationDate) as expirationDate
FROM
    (SELECT
        f.fruit_number,
        CASE
            WHEN e.expiration_date is NULL AND e.fruit_number IS NOT NULL THEN 1
            ELSE 0
        END AS expirationDate
    FROM
        expiration AS e
    FULL OUTER JOIN 
        fruits AS f ON f.fruit_number = e.fruit_number
    WHERE
        f.fruit_number IS NOT NULL) t
GROUP BY
    fruit_number
ORDER BY
    fruit_number

它產生這個結果集：

結果集是我所追求的，但嵌套查詢很難看。如果沒有嵌套查詢，是否可以做到這一點？在線查詢分析器 ( https://www.eversql.com/sql-query-optimizer/ ) 說將子查詢移動到臨時表中并對其進行查詢，但不僅僅是在更多步驟中做同樣的事情?

uj5u.com熱心網友回復：

我要做的第一個改變是你的加入。FULL OUTER JOIN在 where 子句中使用then put是沒有意義的f.fruit_number IS NOT NULL。這意味著每一行都必須有一個記錄fruits，因此您的查詢將更有意義SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number。

您還可以通過將 case 運算式直接放在MAX函式中來洗掉子查詢：

SELECT  f.fruit_number, 
        f.fruit_name,
        expirationDate = MAX(CASE WHEN e.expiration_date IS NULL 
                                    AND e.fruit_number IS NOT NULL THEN 1 ELSE 0 END)
FROM    dbo.fruits AS f
        LEFT JOIN dbo.expiration AS e
            ON e.fruit_number = f.fruit_number
GROUP BY f.fruit_number, f.fruit_name
ORDER BY f.fruit_number;

db<>fiddle 示例

uj5u.com熱心網友回復：

試試這個，我相信它可以讓你得到你想要的：

SELECT t1.fruit_number
, CASE WHEN MIN(ISNULL(expiration_date, '1/1/1900')) = CAST('1/1/1900' as date) and t2.fruit_number IS NOT NULL THEN 1 ELSE 0 END expirationDate
FROM fruits t1 
LEFT JOIN expiration t2 on t1.fruit_number = t2.fruit_number
GROUP BY t1.fruit_number, t2.fruit_number
ORDER BY t1.fruit_number

uj5u.com熱心網友回復：

看來你把這個復雜化了。假設fruit_number是唯一的fruits，則不需要 a group by，而是使用exists

SELECT
  f.fruit_number, 
  f.fruit_name,
  expirationDate = CASE WHEN EXISTS (SELECT 1
                       FROM dbo.expiration AS e
                       WHERE e.fruit_number = f.fruit_number
                         AND e.expiration_date IS NULL
                     ) THEN 1 ELSE 0 END
FROM dbo.fruits AS f
ORDER BY f.fruit_number;

資料庫<>小提琴

標籤：sql-server 查询语句

上一篇：SQLServer::MASKEDWITH(FUNCTION='default()');不作業

下一篇：如何從5串列中選擇唯一記錄，其中值可以相同但順序不同