вернуться в раздел

Вычисление логических выражений на SQL

Жена подкинула тут чудесную логическую задачку:

Дело это было в середине рабочего дня, поэтому фраза "Стандартизированный тест относится к интеллигентности как барометр" сразу похоронила остатки интеллектуальных мощностей. Начал распутывать условия, но очень быстро где-то ошибся и потерял нить рассуждений. После этого стал думать, как можно написать программку, которая бы думала за меня. Память тихо подсказывала использовать для решения LISP, Prolog или на худой конец F#, но этих слов я не знал. Зато подумалось, что эту задачку можно решить на SQL.


SQL-выражение получилось нехилое. Причём, с первого раза, естественно, оно не заработало и выяснилось, что эта портянка относительно неплохо отлаживается. Искать ошибки, встречающиеся в рельных SQL-запросах часто труднее, чем в этом:

with t as
(select 1 as a
union
select 2
union
select 3
union
select 4
union
select 5)
select *
from t as t1, t as t2, t as t3, t as t4, t as t5,
t as t6, t as t7, t as t8, t as t9, t as t10,
t as t11, t as t12, t as t13, t as t14, t as t15,
t as t16, t as t17, t as t18, t as t19, (select 5 as a) as t20 /* Full cartesian join */
where 
(
(t1.a = 2 and t2.a = 2)
or (t1.a = 3 and t3.a = 2)
or (t1.a = 4 and t4.a = 2)
or (t1.a = 5 and t5.a = 2)
) /* 1 */
and
(
(t2.a = 1 and (t6.a = t7.a)
and (t1.a != t2.a) and (t2.a != t3.a) and (t4.a != t5.a) and (t5.a != t6.a)
/*and (t6.a != t7.a)*/ and (t7.a != t8.a) and (t8.a != t9.a) and (t9.a != t10.a)
and (t10.a != t11.a) and (t11.a != t12.a) and (t12.a != t13.a) and (t13.a != t14.a)
and (t14.a != t15.a) and (t15.a != t16.a) and (t16.a != t17.a) and (t17.a != t18.a) 
and (t18.a != t19.a) and (t19.a != t20.a))
or (t2.a = 2 and (t7.a = t8.a)
and (t1.a != t2.a) and (t2.a != t3.a) and (t4.a != t5.a) and (t5.a != t6.a)
and (t6.a != t7.a)/* and (t7.a != t8.a)*/ and (t8.a != t9.a) and (t9.a != t10.a)
and (t10.a != t11.a) and (t11.a != t12.a) and (t12.a != t13.a) and (t13.a != t14.a)
and (t14.a != t15.a) and (t15.a != t16.a) and (t16.a != t17.a) and (t17.a != t18.a) 
and (t18.a != t19.a) and (t19.a != t20.a)
)
or (t2.a = 3 and (t8.a = t9.a)
and (t1.a != t2.a) and (t2.a != t3.a) and (t4.a != t5.a) and (t5.a != t6.a)
and (t6.a != t7.a) and (t7.a != t8.a)/* and (t8.a != t9.a)*/ and (t9.a != t10.a)
and (t10.a != t11.a) and (t11.a != t12.a) and (t12.a != t13.a) and (t13.a != t14.a)
and (t14.a != t15.a) and (t15.a != t16.a) and (t16.a != t17.a) and (t17.a != t18.a) 
and (t18.a != t19.a) and (t19.a != t20.a))
or (t2.a = 4 and (t9.a = t10.a)
and (t1.a != t2.a) and (t2.a != t3.a) and (t4.a != t5.a) and (t5.a != t6.a)
and (t6.a != t7.a) and (t7.a != t8.a) and (t8.a != t9.a) /*and (t9.a != t10.a)*/
and (t10.a != t11.a) and (t11.a != t12.a) and (t12.a != t13.a) and (t13.a != t14.a)
and (t14.a != t15.a) and (t15.a != t16.a) and (t16.a != t17.a) and (t17.a != t18.a) 
and (t18.a != t19.a) and (t19.a != t20.a)
)
or (t2.a = 5 and (T10.a = t11.a)
and (t1.a != t2.a) and (t2.a != t3.a) and (t4.a != t5.a) and (t5.a != t6.a)
and (t6.a != t7.a) and (t7.a != t8.a) and (t8.a != t9.a) and (t9.a != t10.a)
/*and (t10.a != t11.a)*/ and (t11.a != t12.a) and (t12.a != t13.a) and (t13.a != t14.a)
and (t14.a != t15.a) and (t15.a != t16.a) and (t16.a != t17.a) and (t17.a != t18.a) 
and (t18.a != t19.a) and (t19.a != t20.a)
)
) /* 2 */ 
and
(
t3.a = 1 + (case when t1.a = 5 then 1 else 0 end
+ case when t2.a = 5 then 1 else 0 end
+ case when t3.a = 5 then 1 else 0 end
+ case when t4.a = 5 then 1 else 0 end
+ case when t5.a = 5 then 1 else 0 end
+ case when t6.a = 5 then 1 else 0 end
+ case when t7.a = 5 then 1 else 0 end
+ case when t8.a = 5 then 1 else 0 end
+ case when t9.a = 5 then 1 else 0 end
+ case when t10.a = 5 then 1 else 0 end
+ case when t11.a = 5 then 1 else 0 end
+ case when t12.a = 5 then 1 else 0 end
+ case when t13.a = 5 then 1 else 0 end
+ case when t14.a = 5 then 1 else 0 end
+ case when t15.a = 5 then 1 else 0 end
+ case when t16.a = 5 then 1 else 0 end
+ case when t17.a = 5 then 1 else 0 end
+ case when t18.a = 5 then 1 else 0 end
+ case when t19.a = 5 then 1 else 0 end
+ case when t20.a = 5 then 1 else 0 end) 
) /* 3 */
and
(
t4.a + 3 = (case when t1.a = 1 then 1 else 0 end
+ case when t2.a = 1 then 1 else 0 end
+ case when t3.a = 1 then 1 else 0 end
+ case when t4.a = 1 then 1 else 0 end
+ case when t5.a = 1 then 1 else 0 end
+ case when t6.a = 1 then 1 else 0 end
+ case when t7.a = 1 then 1 else 0 end
+ case when t8.a = 1 then 1 else 0 end
+ case when t9.a = 1 then 1 else 0 end
+ case when t10.a = 1 then 1 else 0 end
+ case when t11.a = 1 then 1 else 0 end
+ case when t12.a = 1 then 1 else 0 end
+ case when t13.a = 1 then 1 else 0 end
+ case when t14.a = 1 then 1 else 0 end
+ case when t15.a = 1 then 1 else 0 end
+ case when t16.a = 1 then 1 else 0 end
+ case when t17.a = 1 then 1 else 0 end
+ case when t18.a = 1 then 1 else 0 end
+ case when t19.a = 1 then 1 else 0 end
+ case when t20.a = 1 then 1 else 0 end)
) /* 4 */
and
(
(t5.a = t1.a and t1.a = 1)
or (t5.a = t2.a and t2.a = 2)
or (t5.a = t3.a and t3.a = 3)
or (t5.a = t4.a and t4.a = 4)
or (t5.a = t5.a and t5.a = 5) 
) /* 5 */
and
(
(t6.a = 1 and t17.a = 3)
or (t6.a = 2 and t17.a = 4)
or (t6.a = 3 and t17.a = 5)
or (t6.a = 4 and not t17.a in (3, 4, 5))
) /* 6 */
and
(
(5 - t7.a) = abs(t7.a - t8.a)
) /* 7 */
and
(
t8.a + 3 = 
(case when t1.a in (1, 5) then 1 else 0 end
+ case when t2.a in (1, 5) then 1 else 0 end
+ case when t3.a in (1, 5) then 1 else 0 end
+ case when t4.a in (1, 5) then 1 else 0 end
+ case when t5.a in (1, 5) then 1 else 0 end
+ case when t6.a in (1, 5) then 1 else 0 end
+ case when t7.a in (1, 5) then 1 else 0 end
+ case when t8.a in (1, 5) then 1 else 0 end
+ case when t9.a in (1, 5) then 1 else 0 end
+ case when t10.a in (1, 5) then 1 else 0 end
+ case when t11.a in (1, 5) then 1 else 0 end
+ case when t12.a in (1, 5) then 1 else 0 end
+ case when t13.a in (1, 5) then 1 else 0 end
+ case when t14.a in (1, 5) then 1 else 0 end
+ case when t15.a in (1, 5) then 1 else 0 end
+ case when t16.a in (1, 5) then 1 else 0 end
+ case when t17.a in (1, 5) then 1 else 0 end
+ case when t18.a in (1, 5) then 1 else 0 end
+ case when t19.a in (1, 5) then 1 else 0 end
+ case when t20.a in (1, 5) then 1 else 0 end)
) /* 8 */
and
(
(t9.a = 1 and t10.a = 1)
or (t9.a = 2 and t11.a = 2)
or (t9.a = 3 and t12.a = 3)
or (t9.a = 4 and t13.a = 4)
or (t9.a = 5 and t14.a = 5)
) /* 9 */
and
(
(t10.a = 1 and t16.a = 4)
or (t10.a = 2 and t16.a = 1)
or (t10.a = 3 and t16.a = 5)
or (t10.a = 4 and t16.a = 2)
or (t10.a = 5 and t16.a = 3)
) /* 10 */
and
(
t11.a = 1 +
(case when t1.a = 2 then 1 else 0 end
+ case when t2.a = 2 then 1 else 0 end
+ case when t3.a = 2 then 1 else 0 end
+ case when t4.a = 2 then 1 else 0 end
+ case when t5.a = 2 then 1 else 0 end
+ case when t6.a = 2 then 1 else 0 end
+ case when t7.a = 2 then 1 else 0 end
+ case when t8.a = 2 then 1 else 0 end
+ case when t9.a = 2 then 1 else 0 end
+ case when t10.a = 2 then 1 else 0 end)
) /* 11 */
and
(
(t12.a = 1 and (case when t1.a in (2, 3, 4) then 1 else 0 end
+ case when t2.a in (2, 3, 4) then 1 else 0 end
+ case when t3.a in (2, 3, 4) then 1 else 0 end
+ case when t4.a in (2, 3, 4) then 1 else 0 end
+ case when t5.a in (2, 3, 4) then 1 else 0 end
+ case when t6.a in (2, 3, 4) then 1 else 0 end
+ case when t7.a in (2, 3, 4) then 1 else 0 end
+ case when t8.a in (2, 3, 4) then 1 else 0 end
+ case when t9.a in (2, 3, 4) then 1 else 0 end
+ case when t10.a in (2, 3, 4) then 1 else 0 end
+ case when t11.a in (2, 3, 4) then 1 else 0 end
+ case when t12.a in (2, 3, 4) then 1 else 0 end
+ case when t13.a in (2, 3, 4) then 1 else 0 end
+ case when t14.a in (2, 3, 4) then 1 else 0 end
+ case when t15.a in (2, 3, 4) then 1 else 0 end
+ case when t16.a in (2, 3, 4) then 1 else 0 end
+ case when t17.a in (2, 3, 4) then 1 else 0 end
+ case when t18.a in (2, 3, 4) then 1 else 0 end
+ case when t19.a in (2, 3, 4) then 1 else 0 end
+ case when t20.a in (2, 3, 4) then 1 else 0 end) % 2 = 0
) 
or (t1.a = 2 and (case when t1.a in (2, 3, 4) then 1 else 0 end
+ case when t2.a in (2, 3, 4) then 1 else 0 end
+ case when t3.a in (2, 3, 4) then 1 else 0 end
+ case when t4.a in (2, 3, 4) then 1 else 0 end
+ case when t5.a in (2, 3, 4) then 1 else 0 end
+ case when t6.a in (2, 3, 4) then 1 else 0 end
+ case when t7.a in (2, 3, 4) then 1 else 0 end
+ case when t8.a in (2, 3, 4) then 1 else 0 end
+ case when t9.a in (2, 3, 4) then 1 else 0 end
+ case when t10.a in (2, 3, 4) then 1 else 0 end
+ case when t11.a in (2, 3, 4) then 1 else 0 end
+ case when t12.a in (2, 3, 4) then 1 else 0 end
+ case when t13.a in (2, 3, 4) then 1 else 0 end
+ case when t14.a in (2, 3, 4) then 1 else 0 end
+ case when t15.a in (2, 3, 4) then 1 else 0 end
+ case when t16.a in (2, 3, 4) then 1 else 0 end
+ case when t17.a in (2, 3, 4) then 1 else 0 end
+ case when t18.a in (2, 3, 4) then 1 else 0 end
+ case when t19.a in (2, 3, 4) then 1 else 0 end
+ case when t20.a in (2, 3, 4) then 1 else 0 end) % 2 = 1
) 
or (t1.a = 3 and (case when t1.a in (2, 3, 4) then 1 else 0 end
+ case when t2.a in (2, 3, 4) then 1 else 0 end
+ case when t3.a in (2, 3, 4) then 1 else 0 end
+ case when t4.a in (2, 3, 4) then 1 else 0 end
+ case when t5.a in (2, 3, 4) then 1 else 0 end
+ case when t6.a in (2, 3, 4) then 1 else 0 end
+ case when t7.a in (2, 3, 4) then 1 else 0 end
+ case when t8.a in (2, 3, 4) then 1 else 0 end
+ case when t9.a in (2, 3, 4) then 1 else 0 end
+ case when t10.a in (2, 3, 4) then 1 else 0 end
+ case when t11.a in (2, 3, 4) then 1 else 0 end
+ case when t12.a in (2, 3, 4) then 1 else 0 end
+ case when t13.a in (2, 3, 4) then 1 else 0 end
+ case when t14.a in (2, 3, 4) then 1 else 0 end
+ case when t15.a in (2, 3, 4) then 1 else 0 end
+ case when t16.a in (2, 3, 4) then 1 else 0 end
+ case when t17.a in (2, 3, 4) then 1 else 0 end
+ case when t18.a in (2, 3, 4) then 1 else 0 end
+ case when t19.a in (2, 3, 4) then 1 else 0 end
+ case when t20.a in (2, 3, 4) then 1 else 0 end) in (1, 4)
) 
or (t1.a = 4 and (case when t1.a in (2, 3, 4) then 1 else 0 end
+ case when t2.a in (2, 3, 4) then 1 else 0 end
+ case when t3.a in (2, 3, 4) then 1 else 0 end
+ case when t4.a in (2, 3, 4) then 1 else 0 end
+ case when t5.a in (2, 3, 4) then 1 else 0 end
+ case when t6.a in (2, 3, 4) then 1 else 0 end
+ case when t7.a in (2, 3, 4) then 1 else 0 end
+ case when t8.a in (2, 3, 4) then 1 else 0 end
+ case when t9.a in (2, 3, 4) then 1 else 0 end
+ case when t10.a in (2, 3, 4) then 1 else 0 end
+ case when t11.a in (2, 3, 4) then 1 else 0 end
+ case when t12.a in (2, 3, 4) then 1 else 0 end
+ case when t13.a in (2, 3, 4) then 1 else 0 end
+ case when t14.a in (2, 3, 4) then 1 else 0 end
+ case when t15.a in (2, 3, 4) then 1 else 0 end
+ case when t16.a in (2, 3, 4) then 1 else 0 end
+ case when t17.a in (2, 3, 4) then 1 else 0 end
+ case when t18.a in (2, 3, 4) then 1 else 0 end
+ case when t19.a in (2, 3, 4) then 1 else 0 end
+ case when t20.a in (2, 3, 4) then 1 else 0 end) in (2, 3, 5)
)
or (t1.a = 5 and (case when t1.a in (2, 3, 4) then 1 else 0 end
+ case when t2.a in (2, 3, 4) then 1 else 0 end
+ case when t3.a in (2, 3, 4) then 1 else 0 end
+ case when t4.a in (2, 3, 4) then 1 else 0 end
+ case when t5.a in (2, 3, 4) then 1 else 0 end
+ case when t6.a in (2, 3, 4) then 1 else 0 end
+ case when t7.a in (2, 3, 4) then 1 else 0 end
+ case when t8.a in (2, 3, 4) then 1 else 0 end
+ case when t9.a in (2, 3, 4) then 1 else 0 end
+ case when t10.a in (2, 3, 4) then 1 else 0 end
+ case when t11.a in (2, 3, 4) then 1 else 0 end
+ case when t12.a in (2, 3, 4) then 1 else 0 end
+ case when t13.a in (2, 3, 4) then 1 else 0 end
+ case when t14.a in (2, 3, 4) then 1 else 0 end
+ case when t15.a in (2, 3, 4) then 1 else 0 end
+ case when t16.a in (2, 3, 4) then 1 else 0 end
+ case when t17.a in (2, 3, 4) then 1 else 0 end
+ case when t18.a in (2, 3, 4) then 1 else 0 end
+ case when t19.a in (2, 3, 4) then 1 else 0 end
+ case when t20.a in (2, 3, 4) then 1 else 0 end) = 5
) 
) /* 12 */
and
(
t1.a != 1 and t3.a != 1 and t5.a != 1 and t7.a != 1 and t19.a != 1 and 
(
t13.a = 1 and (t9.a = 1 and t11.a != 1 and t13.a != 1 and t15.a != 1 and t17.a != 1 and t19.a != 1)
or t13.a = 2 and (t9.a != 1 and t11.a = 1 and t13.a != 1 and t15.a != 1 and t17.a != 1 and t19.a != 1)
or t13.a = 3 and (t9.a != 1 and t11.a != 1 and t13.a = 1 and t15.a != 1 and t17.a != 1 and t19.a != 1)
or t13.a = 4 and (t9.a != 1 and t11.a != 1 and t13.a != 1 and t15.a = 1 and t17.a != 1 and t19.a != 1)
or t13.a = 5 and (t9.a != 1 and t11.a != 1 and t13.a != 1 and t15.a != 1 and t17.a = 1 )
)
) /* 13 */
and
(
t14.a = (case when t1.a = 4 then 1 else 0 end
+ case when t2.a = 4 then 1 else 0 end
+ case when t3.a = 4 then 1 else 0 end
+ case when t4.a = 4 then 1 else 0 end
+ case when t5.a = 4 then 1 else 0 end
+ case when t6.a = 4 then 1 else 0 end
+ case when t7.a = 4 then 1 else 0 end
+ case when t8.a = 4 then 1 else 0 end
+ case when t9.a = 4 then 1 else 0 end
+ case when t10.a = 4 then 1 else 0 end
+ case when t11.a = 4 then 1 else 0 end
+ case when t12.a = 4 then 1 else 0 end
+ case when t13.a = 4 then 1 else 0 end
+ case when t14.a = 4 then 1 else 0 end
+ case when t15.a = 4 then 1 else 0 end
+ case when t16.a = 4 then 1 else 0 end
+ case when t17.a = 4 then 1 else 0 end
+ case when t18.a = 4 then 1 else 0 end
+ case when t19.a = 4 then 1 else 0 end
+ case when t20.a = 4 then 1 else 0 end) - 5
) /* 14 */
and
(
t15.a = t12.a
) /* 15 */
and
(
(t16.a = 1 and t10.a = 4)
or (t16.a = 2 and t10.a = 3)
or (t16.a = 3 and t10.a = 2)
or (t16.a = 4 and t10.a = 1)
or (t16.a = 5 and t10.a = 5)
) /* 16 */
and
(
(t17.a = 1 and t6.a = 3)
or (t17.a = 2 and t6.a = 4)
or (t17.a = 3 and t6.a = 5)
or (t17.a = 4 and not t6.a in (3, 4, 5)) 
) /* 17 */
and
(
(t18.a = 1 and
((case when t1.a = 1 then 1 else 0 end
+ case when t2.a = 1 then 1 else 0 end
+ case when t3.a = 1 then 1 else 0 end
+ case when t4.a = 1 then 1 else 0 end
+ case when t5.a = 1 then 1 else 0 end
+ case when t6.a = 1 then 1 else 0 end
+ case when t7.a = 1 then 1 else 0 end
+ case when t8.a = 1 then 1 else 0 end
+ case when t9.a = 1 then 1 else 0 end
+ case when t10.a = 1 then 1 else 0 end
+ case when t11.a = 1 then 1 else 0 end
+ case when t12.a = 1 then 1 else 0 end
+ case when t13.a = 1 then 1 else 0 end
+ case when t14.a = 1 then 1 else 0 end
+ case when t15.a = 1 then 1 else 0 end
+ case when t16.a = 1 then 1 else 0 end
+ case when t17.a = 1 then 1 else 0 end
+ case when t18.a = 1 then 1 else 0 end
+ case when t19.a = 1 then 1 else 0 end
+ case when t20.a = 1 then 1 else 0 end) = 
(case when t1.a = 2 then 1 else 0 end
+ case when t2.a = 2 then 1 else 0 end
+ case when t3.a = 2 then 1 else 0 end
+ case when t4.a = 2 then 1 else 0 end
+ case when t5.a = 2 then 1 else 0 end
+ case when t6.a = 2 then 1 else 0 end
+ case when t7.a = 2 then 1 else 0 end
+ case when t8.a = 2 then 1 else 0 end
+ case when t9.a = 2 then 1 else 0 end
+ case when t10.a = 2 then 1 else 0 end
+ case when t11.a = 2 then 1 else 0 end
+ case when t12.a = 2 then 1 else 0 end
+ case when t13.a = 2 then 1 else 0 end
+ case when t14.a = 2 then 1 else 0 end
+ case when t15.a = 2 then 1 else 0 end
+ case when t16.a = 2 then 1 else 0 end
+ case when t17.a = 2 then 1 else 0 end
+ case when t18.a = 2 then 1 else 0 end
+ case when t19.a = 2 then 1 else 0 end
+ case when t20.a = 2 then 1 else 0 end)))
or (t18.a = 2 and
((case when t1.a = 1 then 1 else 0 end
+ case when t2.a = 1 then 1 else 0 end
+ case when t3.a = 1 then 1 else 0 end
+ case when t4.a = 1 then 1 else 0 end
+ case when t5.a = 1 then 1 else 0 end
+ case when t6.a = 1 then 1 else 0 end
+ case when t7.a = 1 then 1 else 0 end
+ case when t8.a = 1 then 1 else 0 end
+ case when t9.a = 1 then 1 else 0 end
+ case when t10.a = 1 then 1 else 0 end
+ case when t11.a = 1 then 1 else 0 end
+ case when t12.a = 1 then 1 else 0 end
+ case when t13.a = 1 then 1 else 0 end
+ case when t14.a = 1 then 1 else 0 end
+ case when t15.a = 1 then 1 else 0 end
+ case when t16.a = 1 then 1 else 0 end
+ case when t17.a = 1 then 1 else 0 end
+ case when t18.a = 1 then 1 else 0 end
+ case when t19.a = 1 then 1 else 0 end
+ case when t20.a = 1 then 1 else 0 end) = 
(case when t1.a = 3 then 1 else 0 end
+ case when t2.a = 3 then 1 else 0 end
+ case when t3.a = 3 then 1 else 0 end
+ case when t4.a = 3 then 1 else 0 end
+ case when t5.a = 3 then 1 else 0 end
+ case when t6.a = 3 then 1 else 0 end
+ case when t7.a = 3 then 1 else 0 end
+ case when t8.a = 3 then 1 else 0 end
+ case when t9.a = 3 then 1 else 0 end
+ case when t10.a = 3 then 1 else 0 end
+ case when t11.a = 3 then 1 else 0 end
+ case when t12.a = 3 then 1 else 0 end
+ case when t13.a = 3 then 1 else 0 end
+ case when t14.a = 3 then 1 else 0 end
+ case when t15.a = 3 then 1 else 0 end
+ case when t16.a = 3 then 1 else 0 end
+ case when t17.a = 3 then 1 else 0 end
+ case when t18.a = 3 then 1 else 0 end
+ case when t19.a = 3 then 1 else 0 end
+ case when t20.a = 3 then 1 else 0 end)))
or (t18.a = 3 and
((case when t1.a = 1 then 1 else 0 end
+ case when t2.a = 1 then 1 else 0 end
+ case when t3.a = 1 then 1 else 0 end
+ case when t4.a = 1 then 1 else 0 end
+ case when t5.a = 1 then 1 else 0 end
+ case when t6.a = 1 then 1 else 0 end
+ case when t7.a = 1 then 1 else 0 end
+ case when t8.a = 1 then 1 else 0 end
+ case when t9.a = 1 then 1 else 0 end
+ case when t10.a = 1 then 1 else 0 end
+ case when t11.a = 1 then 1 else 0 end
+ case when t12.a = 1 then 1 else 0 end
+ case when t13.a = 1 then 1 else 0 end
+ case when t14.a = 1 then 1 else 0 end
+ case when t15.a = 1 then 1 else 0 end
+ case when t16.a = 1 then 1 else 0 end
+ case when t17.a = 1 then 1 else 0 end
+ case when t18.a = 1 then 1 else 0 end
+ case when t19.a = 1 then 1 else 0 end
+ case when t20.a = 1 then 1 else 0 end) = 
(case when t1.a = 4 then 1 else 0 end
+ case when t2.a = 4 then 1 else 0 end
+ case when t3.a = 4 then 1 else 0 end
+ case when t4.a = 4 then 1 else 0 end
+ case when t5.a = 4 then 1 else 0 end
+ case when t6.a = 4 then 1 else 0 end
+ case when t7.a = 4 then 1 else 0 end
+ case when t8.a = 4 then 1 else 0 end
+ case when t9.a = 4 then 1 else 0 end
+ case when t10.a = 4 then 1 else 0 end
+ case when t11.a = 4 then 1 else 0 end
+ case when t12.a = 4 then 1 else 0 end
+ case when t13.a = 4 then 1 else 0 end
+ case when t14.a = 4 then 1 else 0 end
+ case when t15.a = 4 then 1 else 0 end
+ case when t16.a = 4 then 1 else 0 end
+ case when t17.a = 4 then 1 else 0 end
+ case when t18.a = 4 then 1 else 0 end
+ case when t19.a = 4 then 1 else 0 end
+ case when t20.a = 4 then 1 else 0 end)))
or (t18.a = 4 and
((case when t1.a = 1 then 1 else 0 end
+ case when t2.a = 1 then 1 else 0 end
+ case when t3.a = 1 then 1 else 0 end
+ case when t4.a = 1 then 1 else 0 end
+ case when t5.a = 1 then 1 else 0 end
+ case when t6.a = 1 then 1 else 0 end
+ case when t7.a = 1 then 1 else 0 end
+ case when t8.a = 1 then 1 else 0 end
+ case when t9.a = 1 then 1 else 0 end
+ case when t10.a = 1 then 1 else 0 end
+ case when t11.a = 1 then 1 else 0 end
+ case when t12.a = 1 then 1 else 0 end
+ case when t13.a = 1 then 1 else 0 end
+ case when t14.a = 1 then 1 else 0 end
+ case when t15.a = 1 then 1 else 0 end
+ case when t16.a = 1 then 1 else 0 end
+ case when t17.a = 1 then 1 else 0 end
+ case when t18.a = 1 then 1 else 0 end
+ case when t19.a = 1 then 1 else 0 end
+ case when t20.a = 1 then 1 else 0 end) = 
(case when t1.a = 5 then 1 else 0 end
+ case when t2.a = 5 then 1 else 0 end
+ case when t3.a = 5 then 1 else 0 end
+ case when t4.a = 5 then 1 else 0 end
+ case when t5.a = 5 then 1 else 0 end
+ case when t6.a = 5 then 1 else 0 end
+ case when t7.a = 5 then 1 else 0 end
+ case when t8.a = 5 then 1 else 0 end
+ case when t9.a = 5 then 1 else 0 end
+ case when t10.a = 5 then 1 else 0 end
+ case when t11.a = 5 then 1 else 0 end
+ case when t12.a = 5 then 1 else 0 end
+ case when t13.a = 5 then 1 else 0 end
+ case when t14.a = 5 then 1 else 0 end
+ case when t15.a = 5 then 1 else 0 end
+ case when t16.a = 5 then 1 else 0 end
+ case when t17.a = 5 then 1 else 0 end
+ case when t18.a = 5 then 1 else 0 end
+ case when t19.a = 5 then 1 else 0 end
+ case when t20.a = 5 then 1 else 0 end)))
) /* 18 */ /* Нет проверки на ответ E (ничего из перечисленного) */

Часть проверок я убрал, так как их совсем уж неудобно было записывать. И так ответ получается один и подходит по условиям.

Как ни странно это всё довольно быстро написалось, довольно быстро отладилось и в результате дало ответ. Причём, запрос на моей машине отрабатывал за 4 секунды(!!!), что меня очень удивило и заставило уважать оптимизатор MS SQL Server'а. Всё-таки в блоке FROM стоит декартово произведение, дающее 5^19 = 1.90734863 1013 элементов, полный перебор выполнялся бы нереально долго.

Вверх Домой