简化查询有一个嵌套的查询

Question 1

我想除去需要一种嵌套的查询，如果我可以从我的查询以下，但我在努力工作出如何。

这是一架构：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE IF NOT EXISTS [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 IF NOT EXISTS [dbo].[fruits]
(
    [fruit_number] [int] NOT NULL,
    [fruit_name] [nvarchar](50) NOT NULL
) ON [PRIMARY]

这是数据：

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

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

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

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

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

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

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

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

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

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

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

INSERT IGNORE 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

它产生这一结果设置：

fruit_number	expirationdate将
1	1
2	1
3	0
4	0
5	0

该结果是什么我以后，但它具有丑陋的嵌套的查询。它是可能做到这一点没有嵌套的查询？一个在线查询分析仪(https://www.eversql.com/sql-query-optimizer/)所述的移动子查询到一个临时表格和查询反对，但并不只是做同样的事情，在更多步骤？

Question 2

第一变化我会让你加入。这是没有意义的使用 FULL OUTER JOIN 然后放在那里的条款，说： f.fruit_number IS NOT NULL. 这意味着每个行必须有一个记录 fruits，因此您的查询将使更多的意义 SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

你也可以删除的查询通过把你的情况下表达直接在 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;

例如关数据库<>的小提琴

Question 3

试试这个，我认为它获得你所想要的东西：

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

Question 4

看来你是过分复杂化这一点。假设 fruit_number 是独一无二 fruits有没有需要一个 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;

db<>的小提琴

GarethD · Answer 1 · 2021-11-23T12:35:52

第一变化我会让你加入。这是没有意义的使用 FULL OUTER JOIN 然后放在那里的条款，说： f.fruit_number IS NOT NULL. 这意味着每个行必须有一个记录 fruits，因此您的查询将使更多的意义 SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

你也可以删除的查询通过把你的情况下表达直接在 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;

例如关数据库<>的小提琴

JMabee · Answer 2 · 2021-11-23T12:24:55

试试这个，我认为它获得你所想要的东西：

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

Charlieface · Answer 3 · 2021-11-23T16:39:29

看来你是过分复杂化这一点。假设 fruit_number 是独一无二 fruits有没有需要一个 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;

db<>的小提琴

简化查询有一个嵌套的查询

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别