Simplification d'une requête avec une requête imbriquée

Question 1

Je veux supprimer la nécessité d'une requête imbriquée si je peux de ma requête ci-dessous, mais j'ai du mal à voir comment.

Voici le schéma:

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]

Voici les données:

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')

Et c'est ma requête:

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

Il produit cet ensemble de résultats:

fruit_number	expirationDate
1	1
2	1
3	0
4	0
5	0

Le resultset est ce que je suis après, mais c'est moche d'avoir la requête imbriquée. Est-il possible de le faire sans que la requête imbriquée? Une requête en ligne analyseur (https://www.eversql.com/sql-query-optimizer/) dit pour déplacer le sous-requête dans une table temporaire de requête et contre qui, mais ce n'est pas juste faire la même chose en plusieurs étapes?

Question 2

Le premier changement que je ferais est avec vos jointures. Il n'a pas de sens d'utiliser un FULL OUTER JOIN puis mettez-les dans une clause where qui dit f.fruit_number IS NOT NULL. Cela signifie que chaque ligne doit avoir un enregistrement dans fruitsafin que votre requête aurait plus de sens que SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

Vous pouvez également supprimer la sous-requête en plaçant votre cas l'expression directement dans le MAX fonction:

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;

Exemple sur db<>violon

Question 3

Donner à ceci un essai, je crois que ça fait ce que tu voulais:

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

Il semble que vous êtes de compliquer à l'excès cette. En supposant que fruit_number est unique dans fruits, il n'est pas nécessaire pour un group byau lieu d'utiliser un 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<>violon

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

Le premier changement que je ferais est avec vos jointures. Il n'a pas de sens d'utiliser un FULL OUTER JOIN puis mettez-les dans une clause where qui dit f.fruit_number IS NOT NULL. Cela signifie que chaque ligne doit avoir un enregistrement dans fruitsafin que votre requête aurait plus de sens que SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

Vous pouvez également supprimer la sous-requête en plaçant votre cas l'expression directement dans le MAX fonction:

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;

Exemple sur db<>violon

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

Donner à ceci un essai, je crois que ça fait ce que tu voulais:

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

Il semble que vous êtes de compliquer à l'excès cette. En supposant que fruit_number est unique dans fruits, il n'est pas nécessaire pour un group byau lieu d'utiliser un 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<>violon

Simplification d'une requête avec une requête imbriquée

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie