MS SQL Server для логического завершения

Знаете ли вы, что методы оптимизации MS SQL Server настолько хороши, что вы даже можете использовать их, чтобы просмотреть миллионы, миллиарды и триллионы возможных решений, чтобы найти правильное?

Покажу, как это работает, на очень простом (для компьютера) образце. Это один из тех умопомрачительных самореферентных тестов, которые можно найти на BrainBashers сайт:

1. The first question with B as the correct answer is: 
A. 1   B. 4   C. 3   D. 2   

2. The answer to Question 4 is: 
A. D   B. A   C. B   D. C

3. The answer to Question 1 is: 
A. D   B. C   C. B   D. A

4. The number of questions which have D as the correct answer is: 
A. 3   B. 2   C. 1   D. 0

5. The number of questions which have B as the correct answer is: 
A. 0   B. 2   C. 3  D. 1

Мы будем использовать Common Table Expressions для запроса, который является действительно отличным инструментом для сложных запросов. Нам даже не нужны настоящие таблицы для работы, все необходимые данные мы создадим в самом запросе на выборку.

Для начала найдем все возможные решения этой головоломки. Вот запрос:

with t as
(select 'A' as a
union
select 'B'
union
select 'C'
union
select 'D')
select  *
from t as t1, t as t2, t as t3, t as t4, t as t5 /* Cross join */

Здесь мы создаем список букв «A», «B», «C» и «D», которые являются возможными ответами на один вопрос, и соединяем эти буквы сами с собой пять раз, чтобы получить все возможные комбинации:
all_combinations.jpg

Это хорошо, но недостаточно, так как нам нужен только правильный ответ, а не все. Итак, давайте добавим выражения фильтра в WHERE пункт.

И у нас есть в основном выражения фильтра, по одному на каждый вопрос головоломки. Давайте возьмем первый вопрос и переведем его на SQL.

1. The first question with B as the correct answer is: 
A. 1   B. 4   C. 3   D. 2  

В SQL это будет очень просто. Нам нужно проверить все возможные ответы на первый вопрос и убедиться, что ответ «В» не стоит перед данным вариантом:

(t1.a="A" and t1.a="B" --false)
or (t1.a="B" and t4.a="B" --false)
or (t1.a="C" and t3.a="B" and t1.a!='B' and t2.a!='B')
or (t1.a="D" and t2.a="B" and t1.a!='B' )

Два первых варианта очевидны. false но давайте все равно их сохраним. SQL Server должен понять это сам.

Второй вопрос еще проще. Нам нужно связать 2-й и 4-й вопросы:

 (t2.a="A" and t4.a="D")
or (t2.a="B" and t4.a="A")
or (t2.a="C" and t4.a="B")
or (t2.a="D" and t4.a="C")

Вы поняли идею. Позвольте мне показать окончательный SQL для этой головоломки:

with t as
(select 'A' as a
union
select 'B'
union
select 'C'
union
select 'D')
select  *
  from t as t1, t as t2, t as t3, t as t4, t as t5 /* Cross join */
where
( -- 1
   (t1.a="A" and t1.a="B")
or (t1.a="B" and t4.a="B" --false)
or (t1.a="C" and t3.a="B" and t1.a!='B' and t2.a!='B')
or (t1.a="D" and t2.a="B" and t1.a!='B' )
)
and
( -- 2
   (t2.a="A" and t4.a="D")
or (t2.a="B" and t4.a="A")
or (t2.a="C" and t4.a="B")
or (t2.a="D" and t4.a="C")
)
and
( -- 3
   (t3.a="A" and t1.a="D")
or (t3.a="B" and t1.a="C")
or (t3.a="C" and t1.a="B")
or (t3.a="D" and t1.a="A")
)
and
( -- 4
   (t4.a="A" and	3=(case when t1.a="D" then 1 else 0 end
          +case when t2.a="D" then 1 else 0 end
          +case when t3.a="D" then 1 else 0 end
          +case when t4.a="D" then 1 else 0 end
          +case when t5.a="D" then 1 else 0 end))
or (t4.a="B" and 2=(case when t1.a="D" then 1 else 0 end
          +case when t2.a="D" then 1 else 0 end
          +case when t3.a="D" then 1 else 0 end
          +case when t4.a="D" then 1 else 0 end
          +case when t5.a="D" then 1 else 0 end))
or (t4.a="C" and 1=(case when t1.a="D" then 1 else 0 end
          +case when t2.a="D" then 1 else 0 end
          +case when t3.a="D" then 1 else 0 end
          +case when t4.a="D" then 1 else 0 end
          +case when t5.a="D" then 1 else 0 end))
or (t4.a="D" and 0=(case when t1.a="D" then 1 else 0 end
          +case when t2.a="D" then 1 else 0 end
          +case when t3.a="D" then 1 else 0 end
          +case when t4.a="D" then 1 else 0 end
          +case when t5.a="D" then 1 else 0 end))
)
and
( -- 5
(t5.a="A" and	0=(case when t1.a="B" then 1 else 0 end
          +case when t2.a="B" then 1 else 0 end
          +case when t3.a="B" then 1 else 0 end
          +case when t4.a="B" then 1 else 0 end
          +case when t5.a="B" then 1 else 0 end))
or (t5.a="B" and 2=(case when t1.a="B" then 1 else 0 end
          +case when t2.a="B" then 1 else 0 end
          +case when t3.a="B" then 1 else 0 end
          +case when t4.a="B" then 1 else 0 end
          +case when t5.a="B" then 1 else 0 end))
or (t5.a="C" and 3=(case when t1.a="B" then 1 else 0 end
          +case when t2.a="B" then 1 else 0 end
          +case when t3.a="B" then 1 else 0 end
          +case when t4.a="B" then 1 else 0 end
          +case when t5.a="B" then 1 else 0 end))
or (t5.a="D" and 1=(case when t1.a="B" then 1 else 0 end
          +case when t2.a="B" then 1 else 0 end
          +case when t3.a="B" then 1 else 0 end
          +case when t4.a="B" then 1 else 0 end
          +case when t5.a="B" then 1 else 0 end))
)

И выполнение этого запроса мгновенно дает нам ответ: «CDBC B».

Но это всего 1024 варианта. Давайте попробуем что-нибудь посложнее!
Как насчет САМОРЕФЕРЕНЦИАЛЬНЫЙ ТЕСТ НА ПРИГОДНОСТИ, Джеймс Пропп? Есть 5^19 знак равно 1,90734863 × 10^13 вариантов, что довольно много даже для современных компьютеров. Но MS SQL Server дает решение («DADBEDDEDABADBADBABE») менее чем за секунду.

Запрос имеет длину 20 КБ, но его действительно легко построить, поскольку это те же вопросы в форме 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 /* Cross 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 */

Логические головоломки не часто являются частью корпоративного приложения, но я действительно убедился, что механизм SQL может работать на моем компьютере. WHERE оговорки с такой эффективностью.

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *