Мне было скучно и я решил занялся лёгким некропостингом...
| Код |
WITH recursive R0 AS (
SELECT 42 AS ID, 'John' AS Name FROM RDB$DATABASE
UNION ALL
SELECT 43 AS ID, 'Jane' AS Name FROM RDB$DATABASE
UNION ALL
SELECT 44 AS ID, 'Jak' AS Name FROM RDB$DATABASE
UNION ALL
SELECT 45 AS ID, 'Jun' AS Name FROM RDB$DATABASE
), R1 AS (
SELECT 1 AS ID, 42 AS R0_ID, CAST(4.5 AS DECIMAL(2,1)) AS Hours, CAST('12/01/2020' AS DATE) AS dt FROM RDB$DATABASE
UNION all
SELECT 2 AS ID, 42 AS R0_ID, CAST(7.0 AS DECIMAL(2,1)) AS Hours, CAST('12/02/2020' AS DATE) AS dt FROM RDB$DATABASE
UNION all
SELECT 3 AS ID, 43 AS R0_ID, CAST(5.5 AS DECIMAL(2,1)) AS Hours, CAST('12/01/2020' AS DATE) AS dt FROM RDB$DATABASE
UNION all
SELECT 4 AS ID, 43 AS R0_ID, CAST(6.5 AS DECIMAL(2,1)) AS Hours, CAST('12/02/2020' AS DATE) AS dt FROM RDB$DATABASE
UNION all
SELECT 5 AS ID, 44 AS R0_ID, CAST(4.3 AS DECIMAL(2,1)) AS Hours, CAST('12/01/2020' AS DATE) AS dt FROM RDB$DATABASE
UNION all
SELECT 6 AS ID, 45 AS R0_ID, CAST(3.5 AS DECIMAL(2,1)) AS Hours, CAST('12/01/2020' AS DATE) AS dt FROM RDB$DATABASE
), R2 AS (
SELECT
ROW_NUMBER() OVER(PARTITION BY R1.dt ORDER BY R1.Hours DESC) AS RN,
R0.Name,
R1.Hours,
R1.dt
FROM R0
INNER JOIN R1
ON R1.R0_ID = R0.ID
), R3 AS (
select
ROW_NUMBER() OVER(PARTITION BY EXTRACT(WEEKDAY FROM dt) ORDER BY Hours DESC) AS RN,
MAX(RN) OVER(PARTITION BY EXTRACT(WEEKDAY FROM dt)) AS RN_M,
EXTRACT(WEEKDAY FROM dt) AS WD,
CAST(Name || '(' || Hours || ' hours)' AS VARCHAR(1000)) AS rs
FROM R2
WHERE RN < 4
), R4 AS (
SELECT WD, rs, RN, RN_M
FROM R3
WHERE RN = 1
UNION ALL
SELECT R4.WD, R4.rs || ', ' || R3.rs AS rs, R3.RN, R4.RN_M
FROM R4
INNER join R3
ON R3.WD = R4.WD
and R4.RN + 1 = R3.RN
)
SELECT
WD,
RS
FROM R4
WHERE RN = RN_M
|
|