Simplificarea o interogare cu o interogare imbricate

Question 1

Vreau pentru a elimina nevoia pentru o interogare imbricate dacă pot la întrebarea mea de mai jos, dar eu sunt luptă pentru a lucra cum.

Aceasta este schema:

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]

Aceasta este cea de date:

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

Și acest lucru este meu de interogare:

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 acest set de rezultate:

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

Resultset este ceea ce caut, dar e urât cu interogare imbricate. Este posibil să facă acest lucru fără interogare imbricate? O interogare on-line analizor (https://www.eversql.com/sql-query-optimizer/) a spus să se mute sub-interogare într-un tabel temp și interogare împotriva, dar nu este doar de a face același lucru în mai multe etape?

Question 2

Prima schimbare ar face, este cu alătură. Nu are sens de a utiliza un FULL OUTER JOIN apoi a pus într-o clauză where care spune f.fruit_number IS NOT NULL. Acest lucru înseamnă că fiecare rând trebuie să aibă un record în fruits, atât de interogare ar face mai mult sens ca SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

Puteți elimina, de asemenea, subinterogare prin plasarea caz expresia direct în MAX funcția:

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;

Exemplu pe db<>vioara

Question 3

Dă-o încercare, cred că devine din ce ai vrut:

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

Se pare ca se complica acest lucru. Presupunând că fruit_number este unic în fruits, nu este nevoie de o group by, în loc de a folosi 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<>vioara

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

Prima schimbare ar face, este cu alătură. Nu are sens de a utiliza un FULL OUTER JOIN apoi a pus într-o clauză where care spune f.fruit_number IS NOT NULL. Acest lucru înseamnă că fiecare rând trebuie să aibă un record în fruits, atât de interogare ar face mai mult sens ca SELECT .. FROM fruits AS f LEFT JOIN expiration AS e ON e.fruit_number = f.fruit_number.

Puteți elimina, de asemenea, subinterogare prin plasarea caz expresia direct în MAX funcția:

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;

Exemplu pe db<>vioara

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

Dă-o încercare, cred că devine din ce ai vrut:

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

Se pare ca se complica acest lucru. Presupunând că fruit_number este unic în fruits, nu este nevoie de o group by, în loc de a folosi 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<>vioara

Simplificarea o interogare cu o interogare imbricate

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie