这会拉回昨天和今天的两个int值。我想从第三列的语句中减去两个结果,即差异:
SELECT (
SELECT COUNT(*)
FROM collectors_users
WHERE DATE(dateadded) = CURDATE() - INTERVAL 1 DAY
) AS yesterday, COUNT(*) AS today
FROM collectors_users
WHERE DATE(dateadded) = CURDATE()
最佳答案
您需要重复这些表达式。 SQL(通常)不允许您在同一SELECT
中重复使用列别名。您可以将逻辑简化为:
SELECT SUM(DATE(dateadded) = CURDATE() - INTERVAL 1 DAY) AS yesterday,
SUM(DATE(dateadded) = CURDATE()) as today,
(SUM(DATE(dateadded) = CURDATE()) -
SUM(DATE(dateadded) = CURDATE() - INTERVAL 1 DAY)
) as diff
FROM collectors_users
WHERE dateadded >= CURDATE() - INTERVAL 1 DAY AND
dateadded < CURDATE() + INTERVAL 1 DAY;
请注意,
WHERE
子句的逻辑为期两天。另外,它不使用DATE()
。这将允许查询使用索引(如果有)。关于mysql - 如何从该语句中的SELECT语句中减去两个结果?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39606687/