Просьба помочь в решении задачи с сайта SQL-ex, SELECT(обучающий этап). Пробовал несколько разных вариантов написания решения, ни один по итогу не проходит проверку на проверочной базе, выходит ошибка: "Ваш запрос вернул правильные данные на основной базе, но не прошел тест на проверочной базе.
Неверное число записей (меньше на 1).
При этом пробовал в том числе те варианты, которые ранее в сети выкладывались как ответы к этой задаче, и, по видимому, проходили все необходимые проверки. Просьба посмотреть мои решения и подсказать, что именно я мог не учесть. Подозреваю, что сложность задачи возросла после того, как недавно один из создателей сайта добавил дополнительные проверки (об этом в конце марта была новость), но что именно в них я не могу даже предположить. Сами программисты на связь выходить не хотят, поэтому вышел уже на форум.
Схема базы данных состоит из трех отношений:
CREATE TABLE utQ ( -- Таблица utQ содержит идентификатор и название квадрата, цвет которого первоначально черный.
Q_ID int, -- идентификатор квадрата
Q_NAME varchar(35)); -- название квадрата
CREATE TABLE utV ( -- Таблица utV содержит идентификатор, название и цвет баллончика с краской.
V_ID int, -- идентификатор баллончика с краской
V_NAME varchar(35), -- название баллончика с краской
V_COLOR char(1)); -- цвет баллончика с краской
CREATE TABLE utB ( -- Таблица utB содержит информацию об окраске квадрата баллончиком: время окраски, идентификатор квадрата, идентификатор баллончика, количество краски.
B_DATETIME datetime, -- время окраски
B_Q_ID int, -- идентификатор квадрата
B_V_ID int, -- идентификатор баллончика
B_VOL tinyint); -- количество краски
При этом следует иметь в виду, что:
- - баллончики с краской могут быть трех цветов - красный V_COLOR='R', зеленый V_COLOR='G', голубой V_COLOR='B' (латинские буквы).
- - объем баллончика равен 255 и первоначально он полный;
- - цвет квадрата определяется по правилу RGB, т.е. R=0,G=0,B=0 - черный, R=255, G=255, B=255 - белый;
- - запись в таблице закрасок utB уменьшает количество краски в баллончике на величину B_VOL и соответственно увеличивает количество краски в квадрате на эту же величину;
- - значение 0 < B_VOL <= 255;
- - количество краски одного цвета в квадрате не превышает 255, а количество краски в баллончике не может быть меньше нуля;
- - время окраски B_DATETIME дано с точностью до секунды, т.е. не содержит миллисекунд.
Текст задачи:
При условии, что баллончики с красной краской использовались более одного раза, выбрать из них такие, которыми окрашены квадраты, имеющие голубую компоненту.
Вывести название баллончика
Мои варианты ответа к задаче:
Вариант 1:
with r as (
select v.v_name,
v.v_id,
count(case when v_color = 'R' then 1 end) over(partition by v_id) cnt_r,
count(case when v_color = 'B' then 1 end) over(partition by b_q_id) cnt_b
from utV v join utB b on v.v_id = b.b_v_id
)
select v_name
from r
where cnt_r > 1
and cnt_b > 0
group by v_name
Вариант 2:
WITH RED_V AS (
SELECT V_name, B_V_ID
FROM utV v
JOIN utB b ON v.V_ID = b.B_V_ID
WHERE V_COLOR = 'R'
GROUP BY V_name, B_V_ID
HAVING COUNT(v.V_ID) > 1
),
BLUE_V AS (
SELECT V_name, B_V_ID
FROM utV v
JOIN utB b ON v.V_ID = b.B_V_ID
WHERE B_Q_ID IN (
SELECT B_Q_ID
FROM utB b
JOIN utV v ON v.V_ID = b.B_V_ID
WHERE V_COLOR = 'B')
)
SELECT DISTINCT R.V_name
FROM RED_V R
JOIN BLUE_V B ON R.B_V_ID = B.B_V_ID
Вариант 3:
select distinct v_name
from (Select *, Sum(iif( V_color='r', 1, 0)) over (partition by V_name) red, Sum(iif( V_color='b', 1, 0)) over (partition by B_Q_ID) blue
From utV JOIN utB ON V_id=B_V_id) t
where red>1 and blue>0
Простой
