|
ЖКК
1 - 08.07.2009 - 11:14
|
set nocount on
declare @sig table(sig varchar(10)) -- сигареты
declare @color table(col varchar(10)) -- цвета домов
declare @drink table(dri varchar(10)) -- напитки
declare @anim table(ani varchar(10)) -- животные
declare @nats table(nat varchar(10)) -- национальности
insert @sig
select 'Pall Mall' union select 'Dunhill' union select 'Marlboro' union select 'Winfield' union select 'Rothmans'
insert @color
select 'red' union select 'green' union select 'white' union select 'blue' union select 'yellow'
insert @drink
select 'tea' union select 'beer' union select 'cofe' union select 'milk' union select 'water'
insert @anim
select 'fish' union select 'dog' union select 'cat' union select 'horse' union select 'bird'
insert @nats
select 'eng' union select 'norv' union select 'shved' union select 'dat' union select 'nem'
--собственно сам запрос
select * from @sig s1 ,@color c1 , @drink d1 , @anim a1, @nats n1,
@sig s2 ,@color c2 , @drink d2 , @anim a2, @nats n2,
@sig s3 ,@color c3 , @drink d3 , @anim a3, @nats n3,
@sig s4 ,@color c4 , @drink d4 , @anim a4, @nats n4,
@sig s5 ,@color c5 , @drink d5 , @anim a5, @nats n5
where
s1.sig<>s2.sig and s1.sig<>s3.sig and s1.sig<>s4.sig and s1.sig<>s5.sig
and s2.sig<>s3.sig and s2.sig<>s4.sig and s2.sig<>s5.sig
and s3.sig<>s4.sig and s3.sig<>s5.sig
and s4.sig<>s5.sig
and
c1.col<>c2.col and c1.col<>c3.col and c1.col<>c4.col and c1.col<>c5.col
and c2.col<>c3.col and c2.col<>c4.col and c2.col<>c5.col
and c3.col<>c4.col and c3.col<>c5.col
and c4.col<>c5.col
and
d1.dri<>d2.dri and d1.dri<>d3.dri and d1.dri<>d4.dri and d1.dri<>d5.dri
and d2.dri<>d3.dri and d2.dri<>d4.dri and d2.dri<>d5.dri
and d3.dri<>d4.dri and d3.dri<>d5.dri
and d4.dri<>d5.dri
and
a1.ani<>a2.ani and a1.ani<>a3.ani and a1.ani<>a4.ani and a1.ani<>a5.ani
and a2.ani<>a3.ani and a2.ani<>a4.ani and a2.ani<>a5.ani
and a3.ani<>a4.ani and a3.ani<>a5.ani
and a4.ani<>a5.ani
and
n1.nat<>n2.nat and n1.nat<>n3.nat and n1.nat<>n4.nat and n1.nat<>n5.nat
and n2.nat<>n3.nat and n2.nat<>n4.nat and n2.nat<>n5.nat
and n3.nat<>n4.nat and n3.nat<>n5.nat
and n4.nat<>n5.nat
and
( (n1.nat='eng' and c1.col='red')
or (n2.nat='eng' and c2.col='red')
or (n3.nat='eng' and c3.col='red')
or (n4.nat='eng' and c4.col='red')
or (n5.nat='eng' and c5.col='red') ) -- 1
and ( (n1.nat='shved' and a1.ani='dog')
or (n2.nat='shved' and a2.ani='dog')
or (n3.nat='shved' and a3.ani='dog')
or (n4.nat='shved' and a4.ani='dog')
or (n5.nat='shved' and a5.ani='dog') ) --2
and ( (n1.nat='dat' and d1.dri='tea')
or (n2.nat='dat' and d2.dri='tea')
or (n3.nat='dat' and d3.dri='tea')
or (n4.nat='dat' and d4.dri='tea')
or (n5.nat='dat' and d5.dri='tea') ) -- 3
and ( (c1.col='green' and c2.col='white')
or (c2.col='green' and c3.col='white')
or (c3.col='green' and c4.col='white')
or (c4.col='green' and c5.col='white') ) -- 4
and ( (c1.col='green' and d1.dri='cofe')
or (c2.col='green' and d2.dri='cofe')
or (c3.col='green' and d3.dri='cofe')
or (c4.col='green' and d4.dri='cofe')
or (c5.col='green' and d5.dri='cofe') ) -- 5
and ( (s1.sig='Pall Mall' and a1.ani='bird')
or (s2.sig='Pall Mall' and a2.ani='bird')
or (s3.sig='Pall Mall' and a3.ani='bird')
or (s4.sig='Pall Mall' and a4.ani='bird')
or (s5.sig='Pall Mall' and a5.ani='bird') ) -- 6
and (d3.dri='milk') -- 7
and ( (s1.sig='Dunhill' and c1.col='yellow')
or (s2.sig='Dunhill' and c2.col='yellow')
or (s3.sig='Dunhill' and c3.col='yellow')
or (s4.sig='Dunhill' and c4.col='yellow')
or (s5.sig='Dunhill' and c5.col='yellow') ) -- 8
and (n1.nat='norv') -- 9
and ( (s1.sig='Marlboro' and 'cat'=a2.ani)
or (s2.sig='Marlboro' and 'cat' in (a1.ani, a3.ani))
or (s3.sig='Marlboro' and 'cat' in (a2.ani, a4.ani))
or (s4.sig='Marlboro' and 'cat' in (a3.ani, a5.ani))
or (s5.sig='Marlboro' and 'cat'=a5.ani)) -- 10
and ( (s1.sig='Dunhill' and 'horse'=a2.ani)
or (s2.sig='Dunhill' and 'horse' in (a1.ani, a3.ani))
or (s3.sig='Dunhill' and 'horse' in (a2.ani, a4.ani))
or (s4.sig='Dunhill' and 'horse' in (a3.ani, a5.ani))
or (s5.sig='Dunhill' and 'horse'=a5.ani)) -- 11
and ( (s1.sig='Winfield' and d1.dri='beer')
or (s2.sig='Winfield' and d2.dri='beer')
or (s3.sig='Winfield' and d3.dri='beer')
or (s4.sig='Winfield' and d4.dri='beer')
or (s5.sig='Winfield' and d5.dri='beer') ) -- 12
and ( (n1.nat='norv' and 'blue'=c2.col)
or (n2.nat='norv' and 'blue' in (c1.col, c3.col))
or (n3.nat='norv' and 'blue' in (c2.col, c4.col))
or (n4.nat='norv' and 'blue' in (c3.col, c5.col))
or (n5.nat='norv' and 'blue'=c5.col)) -- 13
and ( (s1.sig='Rothmans' and n1.nat='nem')
or (s2.sig='Rothmans' and n2.nat='nem')
or (s3.sig='Rothmans' and n3.nat='nem')
or (s4.sig='Rothmans' and n4.nat='nem')
or (s5.sig='Rothmans' and n5.nat='nem')) -- 14
and ( (s1.sig='Marlboro' and 'water'=d2.dri)
or (s2.sig='Marlboro' and 'water' in (d1.dri, d3.dri))
or (s3.sig='Marlboro' and 'water' in (d2.dri, d4.dri))
or (s4.sig='Marlboro' and 'water' in (d3.dri, d5.dri))
or (s5.sig='Marlboro' and 'water'=d5.dri)) -- 15 |