为什么这个查询在 SQLite 中不起作用，但在 MySQL 和 MSAccess 中工作正常?

问题描述：

我有一个非常复杂的 SQL 查询，如在这个小提琴>

I have a pretty complex SQL query as shown in this fiddle

SELECT payer_payment.payer_id, 
       Sum(payer_payment.amount)                             AS total_paid, 
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share 
FROM   payers 
       INNER JOIN (payer_payment 
                   INNER JOIN (SELECT payment_id, 
                                      Sum(amount) / Sum(pays) AS single_share 
                               FROM   payer_payment 
                               GROUP  BY payment_id) AS payments_share 
                           ON payer_payment.payment_id = 
                              payments_share.payment_id) 
               ON payers.id = payer_payment.payer_id 
WHERE  payers.user_id  = 1 
GROUP  BY payer_payment.payer_id;

在小提琴中它在 MySQL 上运行良好，但是当我在 SQLite 数据库上运行它时，它要么引发错误引用:

In the fiddle it runs fine on MySQL but when I run it on a SQLite database it either throws an error citing:

(1 没有这样的列:payer_payment.payer_id)

(1 no such column: payer_payment.payer_id)

当该列明确存在时.

或者简单地返回 0 个结果，具体取决于 SQLite 实现(WebSQL 与 SQLite.js)

or simply returns 0 results, depending on the SQLite implementation (WebSQL vs SQLite.js)

这是什么原因，是否可以使我的查询与数据库无关?

What is the reason for this and is it possible to make my query more database agnostic?

答

在 SQLite 中，您可以使用整数主键.(如果您依赖查询中 payers.id 的实际值，您应该明确给出它们.)

In SQLite, you get an autoincrementing column by using INTEGER PRIMARY KEY. (And if you rely on the actual values of payers.id in the query, you should give them explicitly.)

不要在不需要时尝试嵌套连接:

You should not try to nest joins when it is not needed:

SELECT payer_payment.payer_id,
       Sum(payer_payment.amount)                             AS total_paid,
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share
FROM       payers
INNER JOIN payer_payment
        ON payers.id = payer_payment.payer_id
INNER JOIN (SELECT payment_id,
                   Sum(amount) / Sum(pays) AS single_share
            FROM   payer_payment
            GROUP  BY payment_id) AS payments_share
        ON payer_payment.payment_id = payments_share.payment_id
WHERE payers.user_id = 1
GROUP BY payer_payment.payer_id;

SQLFiddle

为什么这个查询在 SQLite 中不起作用，但在 MySQL 和 MSAccess 中工作正常?

相关推荐