Chcę wyeliminować potrzebę w dodatkowym wezwaniu, jeśli będę w stanie, z poniższej prośby, ale ja ze wszystkich sił staram się zrozumieć, jak to zrobić.
To schemat:
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]
To dane:
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 to moje pytanie:
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
On tworzy ten zestaw wyników:
фрукт_номер | Okres trwałości |
---|---|
1 | 1 |
2 | 1 |
3 | 0 |
4 | 0 |
5 | 0 |
Zestaw wyników-to jest to, czego potrzebuję, ale nieładnie mieć załączony wniosek. Czy można to zrobić bez załączonego zapytania? Online-analizator zapytań (https://www.eversql.com/sql-query-optimizer/) jest napisane, aby przenieść podzapytanie w tabeli tymczasowej i uruchomić kwerendę przeciwko temu, ale czy to nie to samo, co w innych kroków?