drop table if exists #country
select 'datch' as country
	into #country
	union select 'norv' as country
	union select 'sweed' as country
	union select 'brit' as country
	union select 'germ' as country

drop table if exists #animal
select 'fish' as animal
	into #animal
	union select 'dog' as animal
	union select 'cat' as animal
	union select 'bird' as animal
	union select 'horse' as animal


drop table if exists #cigar
select 'dunhi' as cigar
	into #cigar
	union select 'marlbo' as cigar
	union select 'rothma' as cigar
	union select 'PalMal' as cigar
	union select 'PhilMor' as cigar

drop table if exists #color
select 'yel' as color
	into #color
	union select 'red' as color
	union select 'blue' as color
	union select 'gree' as color
	union select 'whit' as color

drop table if exists #addr
select 1 as addr
	into #addr
	union select 2 as addr
	union select 3 as addr
	union select 4 as addr
	union select 5 as addr

drop table if exists #drink
select 'tea' as drink
	into #drink
	union select 'milk' as drink
	union select 'water' as drink
	union select 'beer' as drink
	union select 'cafe' as drink

-- Варианты записей удовлетворяющие фильтрам на одну запись 
drop table if exists #step1
select * 
	into #step1
	from #addr, #country, #animal, #cigar, #color, #drink
	where ((country = 'norv' and addr = 1) or (country != 'norv' and addr != 1)) --1 Норвежец живёт в первом доме.
		and ((country = 'brit' and color = 'red') or (color != 'red' and country != 'brit')) --2 Англичанин живёт в красном доме.
		and ((country = 'datch' and drink = 'tea') or (country != 'datch' and drink != 'tea'))--4 Датчанин пьёт чай.
		and ((color = 'yel' and cigar = 'dunhi') or (color != 'yel' and cigar != 'dunhi'))--6 Тот, кто живёт в жёлтом доме, курит Dunhill.
		and ((country = 'germ' and cigar = 'rothma') or (country != 'germ' and cigar != 'rothma'))--7 Немец курит Rothmans.
		and ((addr = 3 and drink = 'milk') or (addr != 3 and drink != 'milk'))--8 Тот, кто живёт в центре, пьёт молоко.
		and ((cigar = 'PalMal' and animal = 'bird') or (cigar != 'PalMal' and animal != 'bird'))--10 Тот, кто курит Pall Mall, выращивает птиц.
		and ((country = 'sweed' and animal = 'dog') or (country != 'sweed' and animal != 'dog'))--11 Швед выращивает собак.
		and ((animal = 'horse' and color = 'blue') or (animal != 'horse' and color != 'blue'))--13 Тот, кто выращивает лошадей, живёт в синем доме.
		and ((cigar = 'PhilMor' and drink = 'beer') or (cigar != 'PhilMor' and drink != 'beer'))--14 Тот, кто курит Phillip Morris, пьет пиво.
		and ((color = 'gree' and drink = 'cafe') or (color != 'gree' and drink != 'cafe'))--15 В зелёном доме пьют кофе.

-- Нумерация
drop table if exists #step2
select ROW_NUMBER() OVER(ORDER BY addr, country, animal, cigar, color, drink ASC) AS line, * 
	into #step2
	from #step1 

-- Результат 80 штук
select * from #step2 order by line

-- Комбинации по 5 с неповторяющимся одним атрибутом (4376064 шт)
drop table if exists #comb
select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	into #comb
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.addr != B.addr and A.addr != C.addr and A.addr != D.addr and A.addr != E.addr
			and B.addr != C.addr and B.addr != D.addr and B.addr != E.addr
			and C.addr != D.addr and C.addr != E.addr
			and D.addr != E.addr
union all select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.country != B.country and A.country != C.country and A.country != D.country and A.country != E.country
			and B.country != C.country and B.country != D.country and B.country != E.country
			and C.country != D.country and C.country != E.country
			and D.country != E.country
union all select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.animal != B.animal and A.animal != C.animal and A.animal != D.animal and A.animal != E.animal
			and B.animal != C.animal and B.animal != D.animal and B.animal != E.animal
			and C.animal != D.animal and C.animal != E.animal
			and D.animal != E.animal
union all select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.cigar != B.cigar and A.cigar != C.cigar and A.cigar != D.cigar and A.cigar != E.cigar
			and B.cigar != C.cigar and B.cigar != D.cigar and B.cigar != E.cigar
			and C.cigar != D.cigar and C.cigar != E.cigar
			and D.cigar != E.cigar
union all select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.color != B.color and A.color != C.color and A.color != D.color and A.color != E.color
			and B.color != C.color and B.color != D.color and B.color != E.color
			and C.color != D.color and C.color != E.color
			and D.color != E.color
union all select A.line as l1, B.line as l2, C.line as l3, D.line as l4, E.line as l5
	from #step2 A, #step2 B, #step2 C, #step2 D, #step2 E
	where A.line < B.line and B.line < C.line and C.line < D.line and D.line < E.line
			and A.drink != B.drink and A.drink != C.drink and A.drink != D.drink and A.drink != E.drink
			and B.drink != C.drink and B.drink != D.drink and B.drink != E.drink
			and C.drink != D.drink and C.drink != E.drink
			and D.drink != E.drink

-- Комбинации по 5 с неповторяющимися всеми атрибутами (360 штук)
drop table if exists #step3
select l1,l2,l3,l4,l5 
	into #step3
	from #comb group by l1,l2,l3,l4,l5 having count(*) = 6 

select * from #step3 order by l1,l2,l3,l4,l5

-- Тут надо допроверку каждой комбинации на условия:
--3 Зелёный дом находится слева от белого, рядом с ним.
--5 Тот, кто курит Marlboro, живёт рядом с тем, кто выращивает кошек.
--9 Сосед того, кто курит Marlboro, пьёт воду.
--12 Норвежец живёт рядом с синим домом.