sqlite> select 38 as latitude, 122 as longitude, "Berkeley" as name union
...> select 42, 71, "Cambridge" union
...> select 45, 93, "Minneapolis";
38|122|Berkeley
42|71|Cambridge
45|93|Minneapolis
sqlite> create table cities as
...> select 38 as latitude, 122 as longitude, "Berkeley" as name union
...> select 42, 71, "Cambridge" union
...> select 45, 93, "Minneapolis";
sqlite> create table distances as
...> select name, 60*abs(latitude-38) as distance from cities;
sqlite> select distance/5, name from distances;
0|Berkeley
48|Cambridge
84|Minneapolis
sqlite> select a.city, b.city, a.temp - b.temp
...> from temps as a, temps as b where a.city < b.city;
Berkeley|Chicago|10
Berkeley|Minneapolis|15
Chicago|Minneapolis|5
sqlite> create table cities as
...> select 38 as latitude, 122 as longitude, "Berkeley" as name union
...> select 42, 71, "Cambridge" union
...> select 45, 93, "Minneapolis";
sqlite> create table distances as
...> select name, 60*abs(latitude-38) as distance from cities;
sqlite> select distance/5, name from distances;
0|Berkeley
48|Cambridge
84|Minneapolis
>>> def create_make_row(description):
"""Return a function from an input environment (dict) to an output row.
description -- a comma-separated list of [expression] as [column name]
"""
columns = description.split(", ")
expressions, names = [], []
for column in columns:
if " as " in column:
expression, name = column.split(" as ")
else:
expression, name = column, column
expressions.append(expression)
names.append(name)
row = namedtuple("Row", names)
return lambda env: row(*[eval(e, env) for e in expressions])
>>> from itertools import product
>>> def join(tables, env):
"""Return an iterator over dictionaries from names to values in a row.
tables -- a comma-separate sequences of table names
env -- a dictionary from global names to tables
"""
names = tables.split(", ")
joined_rows = product(*[env[name] for name in names])
return map(lambda rows: make_env(rows, names), joined_rows)
>>> def make_env(rows, names):
"""Create an environment of names bound to values."""
env = dict(zip(names, rows))
for row in rows:
for name in row._fields:
env[name] = getattr(row, name)
return env
sqlite> with
...> states(city, state) as (
...> select "Berkeley", "California" union
...> select "Boston", "Massachusetts" union
...> select "Cambridge", "Massachusetts" union
...> select "Chicago", "Illinois" union
...> select "Pasadena", "California"
...> )
...> select a.city, b.city, a.state from states as a, states as b
...> where a.state = b.state and a.city < b.city;
Berkeley|Pasadena|California
Boston|Cambridge|Massachusetts
sqlite> with
...> ints(n) as (
...> select 5 union
...> select n+1 from ints where n < 15
...> )
...> select n, n*n from ints where n % 2 = 1;
5|25
7|49
9|81
11|121
13|169
15|225
sqlite> with
...> ints(n) as (
...> select 1 union select n+1 from ints where n < 20
...> ),
...> squares(x, xx) as (
...> select n, n*n from ints
...> ),
...> sum_of_squares(a, b, sum) as (
...> select a.x, b.x, a.xx + b.xx
...> from squares as a, squares as b where a.x < b.x
...> )
...> select a, b, x from squares, sum_of_squares where sum = xx;
3|4|5
6|8|10
5|12|13
9|12|15
8|15|17
12|16|20
sqlite> with
...> fib(previous, current) as (
...> select 0, 1 union
...> select current, previous+current from fib
...> where current <= 100
...> )
...> select previous from fib;
0
1
1
2
3
5
8
13
21
34
55
89
这些例子说明递归是一种强大的组合方法，即使在声明性语言中也是如此。
构建字符串。在SQL中，可以使用||操作符将两个字符串连接成一个更长的字符串。
sqlite> with wall(n) as (
....> select 99 union select 98 union select 97
....> )
....> select n || " bottles" from wall;
99 bottles
98 bottles
97 bottles
sqlite> create table nouns as
....> select "the dog" as phrase union
....> select "the cat" union
....> select "the bird";
sqlite> select subject.phrase || " chased " || object.phrase
....> from nouns as subject, nouns as object
....> where subject.phrase != object.phrase;
the bird chased the cat
the bird chased the dog
the cat chased the bird
the cat chased the dog
the dog chased the bird
the dog chased the cat
作为练习，使用递归本地表来生成这样的句子：“the dog that chased the cat that chased the bird also chased the bird”
sqlite> select max(name) from animals group by legs, weight order by name;
bird
dog
ferret
penguin
t-rex
sqlite> select max(name), legs, weight from animals group by legs, weight
....> having max(weight) < 100;
bird|2|6
penguin|2|10
ferret|4|10
dog|4|20
sqlite> select count(*), weight/legs from animals group by weight/legs;
2|2
1|3
2|5
1|6000
