La simplificación de una consulta con una consulta anidada

Question 1

Quiero eliminar la necesidad de una consulta anidada si puedo de mi consulta, pero estoy luchando para averiguar cómo.

Este es el esquema:

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]

Estos son los datos:

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

Y esta es mi consulta:

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

Se produce este conjunto de resultados:

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

El conjunto de resultados es lo que yo busco, pero es feo tener la consulta anidada. Es posible hacer esto sin la consulta anidada? Una consulta en línea analizador (https://www.eversql.com/sql-query-optimizer/) le dijo a mover la sub-consulta en una tabla temporal y la consulta en contra de eso, pero no es sólo hacer lo mismo en más pasos?

Question 2

El primer cambio que yo haría es con sus combinaciones. No tiene sentido utilizar un FULL OUTER JOIN a continuación, poner en una cláusula where que dice f.fruit_number IS NOT NULL. Esto significa que cada fila debe tener un registro en fruits, por lo que la consulta tendría más sentido como SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

También se puede quitar la subconsulta colocando el caso de la expresión directamente en el MAX función:

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;

Ejemplo en db<>violín

Question 3

Darle una oportunidad, yo creo que usted obtiene lo que quería:

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

Parece que complicar este. Suponiendo que fruit_number es único en fruitsno hay necesidad para una group by, en lugar de utilizar una 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<>violín

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

El primer cambio que yo haría es con sus combinaciones. No tiene sentido utilizar un FULL OUTER JOIN a continuación, poner en una cláusula where que dice f.fruit_number IS NOT NULL. Esto significa que cada fila debe tener un registro en fruits, por lo que la consulta tendría más sentido como SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

También se puede quitar la subconsulta colocando el caso de la expresión directamente en el MAX función:

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;

Ejemplo en db<>violín

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

Darle una oportunidad, yo creo que usted obtiene lo que quería:

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

Parece que complicar este. Suponiendo que fruit_number es único en fruitsno hay necesidad para una group by, en lugar de utilizar una 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<>violín

La simplificación de una consulta con una consulta anidada

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría