使查询在SQLite和MySQL上工作

Question

我在主查询where子句中有一个带有条件的子查询。

下面是示例模式

CREATE TABLE `payments` (  
  `id` int(11) NOT NULL,
  `discount_id` int(11),
  `amount` int(11) 
);

CREATE TABLE `discounts` (
  `id` int(11) NOT NULL,
  `rate` int(11) NOT NULL
);

和查询

SELECT 
  discounts.*, 
  (SELECT COUNT(id) 
   FROM payments 
   WHERE payments.discount_id = discounts.id 
   GROUP BY payments.discount_id) AS usage_count
FROM discounts   
WHERE
  rate > 10
  AND usage_count > 1

• 工作于SQLite：http://sqlfiddle.com/#!5/9f159/2
• 不工作于MySQL：http://sqlfiddle.com/#!9/9f1593/1

MySQL显示错误Unknown column 'usage_count' in 'where clause'

我可以让它在MySQL上使用HAVING子句，但是在SQLite和TypeError: e.STATEMENT is undefined上，它会失败。

• 不工作于SQLite：http://sqlfiddle.com/#!5/9f159
• 工作于MySQL：http://sqlfiddle.com/#!9/9f1593/2

有没有办法让两者都有一种单一的工作方式呢？

为什么？我正在使用Laravel框架进行开发，我们的单元测试运行在SQLite上，应用服务器运行MySQL。

Answer 1

或者作为一个连接，因为您的子查询只是一个连接，而其中只基于折扣。

SELECT 
  discounts.*, count(*) as usage_count
FROM discounts   
JOIN payments
  ON payments.discount_id = discounts.id 
WHERE
  rate > 10
GROUP BY payments.discount_id
HAVING usage_count > 1

Answer 2

SELECT * FROM 
  (SELECT 
  discounts.*, 
  (SELECT COUNT(id) 
   FROM payments 
   WHERE payments.discount_id = discounts.id 
   GROUP BY payments.discount_id) AS usage_count
FROM discounts   
WHERE rate > 10) table1 
WHERE usage_count > 1