mysql - 为什么这个查询在 SQLite 中不起作用，但在 MySQL 和 MSAccess 中却能正常工作？

Question

我有一个非常复杂的 SQL 查询，如图所示 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; 

在 fiddle 中，它在 MySQL 上运行良好，但是当我在 SQLite 数据库上运行它时，它要么抛出错误，引用:

(1 no such column: payer_payment.payer_id)

当列明显存在时。

或者简单地返回 0 个结果，这取决于 SQLite 的实现(WebSQL vs SQLite.js)

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

Answer 1

在 SQLite 中，您可以使用 INTEGER PRIMARY KEY 获得一个自动递增的列.(如果您在查询中依赖于 payers.id 的实际值，则应明确给出它们。)

你不应该在不需要的时候尝试嵌套连接:

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