mysql - 将两个 SQL 查询的结果相减

Question

这个问题已经有答案了:

MySQL: difference of two result sets (3 个回答)

已关闭 8 年前。

我试图减去以下两个 SQL 查询的结果，但无法计算出来。我已经尝试过NOT IN和EXCEPT但我认为我这样做是错误的。以下是我的疑问:

SELECT DISTINCT first_name, last_name, rating, email, phone
FROM users
WHERE rating !='6';


SELECT DISTINCT first_name, last_name, rating, email, phone
FROM users
JOIN user_transactions
ON users.id=user_transactions.user_id
WHERE rating != '6' AND created_on >= '2014-09-01' AND created_on <= CURDATE();

Answer 1

您可以使用聚合和 having 子句执行您想要的操作:

SELECT first_name, last_name, rating, email, phone
FROM users u JOIN
     user_transactions ut
     ON u.id = ut.user_id
WHERE rating <> '6';
GROUP BY first_name, last_name, rating, email, phone
HAVING SUM(created_on >= '2014-09-01' AND created_on <= CURDATE()) = 0;