Chci, aby odstranit potřebu pro vnořený dotaz, jestli můžu z mého dotazu níže, ale snažím se vymyslet, jak.
Tady 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]
Jedná se o ú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 to 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ýsledek:
fruit_number | expirationDate |
---|---|
1 | 1 |
2 | 1 |
3 | 0 |
4 | 0 |
5 | 0 |
Výsledný soubor je to, co jsem po tom, ale je to ošklivé, že vnořený dotaz. Je možné to udělat, aniž vnořený dotaz? On-line dotazu analyzátoru (https://www.eversql.com/sql-query-optimizer/), řekl, přesunout sub-dotaz do dočasné tabulky a dotaz na to, ale není jen dělat stejnou věc ve více krocích?