SQLite subquery syntax/error/difference from MySQL
I was under the impression this is valid SQLite syntax:
SELECT
*,
(SELECT amount AS target
FROM target_money
WHERE start_year <= p.bill_year
AND start_month <= p.bill_month
ORDER BY start_year ASC, start_month ASC
LIMIT 1) AS target
FROM payments AS p;
But I guess it's not, because SQLite returns this error:
no such column: p.bill_year
What's wrong with how I refer to p.bill_year?
Yes, I am positive tablepayments
hosts a co开发者_运维问答lumn bill_year
. Am I crazy or is this just valid SQL syntax? It would work in MySQL wouldn't it?? I don't have any other SQL present so I can't test others, but I thought SQLite was quite standardlike.Thanks, Mark.
Your query works fine in SQLite:
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('CREATE TABLE payments (bill_year INT, bill_month INT);')
<sqlite3.Cursor object at 0x00C62CE0>
>>> conn.commit()
>>> c.execute("""CREATE TABLE target_money
(amount INT, start_year INT, start_month INT);""")
<sqlite3.Cursor object at 0x00C62CE0>
>>> conn.commit()
>>> c.execute("""
... SELECT
... *,
... (SELECT amount AS target
... FROM target_money
... WHERE start_year <= p.bill_year AND start_month <= p.bill_month
... ORDER BY start_year ASC, start_month ASC
... LIMIT 1) AS target
... FROM
... payments AS p;
... """)
<sqlite3.Cursor object at 0x00C62CE0>
>>> c.fetchall()
[]
It works in MySQL:
CREATE TABLE payments (bill_year INT, bill_month INT);
CREATE TABLE target_money (amount INT, start_year INT, start_month INT);
SELECT
*,
(SELECT amount AS target
FROM target_money
WHERE start_year <= p.bill_year AND start_month <= p.bill_month
ORDER BY start_year ASC, start_month ASC
LIMIT 1) AS target
FROM
payments AS p;
I'd imagine that it would work in SQLite too. I'm sure someone here can copy & paste the above to verify it...
I make some tests to confirm that correlated subqueries dosn't work on sqlite2, but works on sqlite3, and that seems to be a case. The problem is that official documentation says nothing about it. There is still a small possibility that correlated subqueries are supported in sqlite2 under some odd syntax. Thats all I can tell without getting into sqlite2 source code.
精彩评论