Chcem odstrániť potrebu vnorené dotaz, či môžem z môjho dotazu nižšie, ale ja som snaží zistiť ako.
Toto je 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]
Je to údaje:
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')
A toto je môj dotaz:
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
To produkuje tento výsledok:
fruit_number | expirationDate |
---|---|
1 | 1 |
2 | 1 |
3 | 0 |
4 | 0 |
5 | 0 |
Na resultset je to, čo som po, ale je to škaredé so vnorené dotaz. Je to možné urobiť bez vnorených dotaz? Online query analyzer (https://www.eversql.com/sql-query-optimizer/) povedal, aby move sub-dopyt do temp tabuľka dotazu proti tomu, ale nie je len robiť rovnakú vec na ďalšie kroky?