<p>找到了我自己问题的答案。我在下面用sqlalchemy核心来表示,因为这就是我最近使用的。在</p>
<p>诀窍是使用WITH RECURSIVE来计算乘积:</p>
<p>Python代码如下所示:</p>
<pre><code>from sqlalchemy import Table, Column, String, Integer, MetaData, \
select, func, ForeignKey, text
import sys
from functools import reduce
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
linked_list = Table('linked_list', metadata,
Column('id', Integer, primary_key = True),
Column('at', Integer, nullable=False),
Column('val', Integer, nullable=False),
Column('next', Integer, ForeignKey('linked_list.at'))
)
refs = Table('refs', metadata,
Column('id', Integer, primary_key = True),
Column('ref', Integer, ForeignKey('linked_list.at')),
)
placeholder = Table('placeholder', metadata,
Column('id', Integer, primary_key = True),
Column('ref', Integer, ForeignKey('linked_list.at')),
Column('val', Integer, nullable=False),
)
metadata.create_all(engine)
conn = engine.connect()
refs_al = refs.alias()
linked_list_m = select([
linked_list.c.at,
linked_list.c.val,
linked_list.c.next]).\
where(linked_list.c.at==refs_al.c.ref).\
cte(recursive=True)
llm_alias = linked_list_m.alias()
ll_alias = linked_list.alias()
linked_list_m = linked_list_m.union_all(
select([
llm_alias.c.at,
ll_alias.c.val * llm_alias.c.val,
ll_alias.c.next
]).
where(ll_alias.c.at==llm_alias.c.next)
)
llm_alias_2 = linked_list_m.alias()
sub_statement = select([
llm_alias_2.c.at,
llm_alias_2.c.val]).\
order_by(llm_alias_2.c.val.desc()).\
limit(1)
def gen_statement(v) :
return select([refs_al.c.ref, func.max(llm_alias_2.c.val)]).\
select_from(
refs_al.\
join(llm_alias_2, onclause=refs_al.c.ref == llm_alias_2.c.at)).\
group_by(refs_al.c.ref).where(llm_alias_2.c.val > v)
LISTS = [[2,4,4,11],[3,4,5,6]]
idx = 0
for LIST in LISTS :
start = idx
for x in range(len(LIST)) :
ELT = LIST[x]
conn.execute(linked_list.insert().\
values(at=idx, val = ELT, next=idx+1 if x != len(LIST) - 1 else None))
idx += 1
conn.execute(refs.insert().values(ref=start))
def gen_insert(v) :
return placeholder.insert().from_select(['ref', 'val'], gen_statement(v))
print "LISTS:"
for LIST in LISTS :
print " ", LIST
def PRODUCT(L) : return reduce(lambda x,y : x*y, L, 1)
print "PRODUCTS OF LISTS:"
for LIST in LISTS :
print " ", PRODUCT(LIST)
for x in (345,355,365) :
statement_ = gen_insert(x)
print "########"
print "Lists that are greater than:", x
conn.execute(statement_)
allresults = conn.execute(select([placeholder.c.val])).fetchall()
if len(allresults) == 0 :
print " /no results found/"
else :
for res in allresults :
print res
conn.execute(placeholder.delete())
print "########"
</code></pre>
<p>结果是:</p>
^{pr2}$
<p>python函数gen_语句生成的要插入占位符表中的SQL(w/indentation被我改为更具可读性)是:</p>
<pre><code>WITH RECURSIVE anon_2(at, val, next) AS
(SELECT linked_list.at AS at, linked_list.val AS val, linked_list.next AS next
FROM linked_list, refs AS refs_1
WHERE linked_list.at = refs_1.ref
UNION ALL
SELECT anon_3.at AS at, linked_list_1.val * anon_3.val AS anon_4, linked_list_1.next AS next
FROM anon_2 AS anon_3, linked_list AS linked_list_1
WHERE linked_list_1.at = anon_3.next)
SELECT refs_1.ref, max(anon_1.val) AS max_1
FROM refs AS refs_1 JOIN anon_2 AS anon_1 ON refs_1.ref = anon_1.at
WHERE anon_1.val > :val_1 GROUP BY refs_1.ref
</code></pre>
<p>奇怪的是,我写入<code>placeholder</code>表然后从中读取的原因是,如果我只是迭代select语句返回的行,sqlalchemy 1.0会为产生0行的<code>> 365</code>请求抛出一个错误。理论上,它应该只产生0行。但是,当语句的结果刚刚插入表<code>placeholder</code>时,它会按预期插入0行。在</p>