Game-Set-Match/puzzle+solution_numbers.sql at main · patch72/Game-Set-Match · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- tab = puzzle+solution
with symbols as
  (          select 0     symbol   from dual
   union all select 1          from dual
   union all select 2           from dual
  )
,    colors   as
 (           select 0    color    from dual
   union all select 1            from dual
   union all select 2               from dual
  )
,    shadings as
 (           select 0   shading  from dual
   union all select 1           from dual
   union all select 2              from dual
  )
,    numbers  as
 (           select 0          quantity from dual
   union all select 1                   from dual
   union all select 2                   from dual
  )
,    deck     as
  (select symbol, color, shading, quantity
   from       symbols
   cross join colors
   cross join shadings
   cross join numbers
  )
,    puzzle   as
  (select rownum as cardno, symbol, color
        , shading         , quantity
   from   (select *
           from   deck
           order  by dbms_random.value
          )
   where  rownum <= 12
  )
,    solution as
  (select cards1.symbol   as symbol1
        , cards1.color    as color1
        , cards1.shading  as shading1
        , cards1.quantity as quantity1
        , cards2.symbol   as symbol2
        , cards2.color    as color2
        , cards2.shading  as shading2
        , cards2.quantity as quantity2
        , cards3.symbol   as symbol3
        , cards3.color    as color3
        , cards3.shading  as shading3
        , cards3.quantity as quantity3
   from   puzzle cards1
   join   puzzle cards2
     on   cards1.cardno < cards2.cardno
   join   puzzle cards3
     on   cards2.cardno < cards3.cardno
   where  1=1
   and    mod( ( cards1.symbol + cards2.symbol + cards3.symbol ), 3 ) = 0
   and    mod( ( cards1.color + cards2.color + cards3.color ), 3 ) = 0
   and    mod( ( cards1.shading + cards2.shading + cards3.shading ), 3 ) = 0
   and    mod( ( cards1.quantity + cards2.quantity + cards3.quantity ), 3 ) = 0

  --  and    (   (    cards1.symbol  =  cards2.symbol
  --              and cards1.symbol  =  cards3.symbol
  --             )
  --          or (    cards1.symbol  <> cards2.symbol
  --              and cards2.symbol  <> cards3.symbol
  --              and cards1.symbol  <> cards3.symbol
  --             )
  --         )
  --  and    (   (    cards1.color  =  cards2.color
  --              and cards1.color  =  cards3.color
  --             )
  --          or (    cards1.color  <> cards2.color
  --              and cards2.color  <> cards3.color
  --              and cards1.color  <> cards3.color
  --             )
  --         )
  --  and    (   (    cards1.shading  =  cards2.shading
  --              and cards1.shading  =  cards3.shading
  --             )
  --          or (    cards1.shading  <> cards2.shading
  --              and cards2.shading  <> cards3.shading
  --              and cards1.shading  <> cards3.shading
  --             )
  --         )
  --  and    (   (    cards1.quantity  =  cards2.quantity
  --              and cards1.quantity  =  cards3.quantity
  --             )
  --          or (    cards1.quantity  <> cards2.quantity
  --              and cards2.quantity  <> cards3.quantity
  --              and cards1.quantity  <> cards3.quantity
  --             )
  --         )
  )
select 'puzzle'   as what
     ,               symbol,          color
     ,               shading,         quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
from   puzzle
union  all
select 'solution' as what
     ,               symbol1,         color1
     ,               shading1,        quantity1
     ,               symbol2,         color2
     ,               shading2,        quantity2
     ,               symbol3,         color3
     ,               shading3,        quantity3
from   solution
/