我有以下查询：
SELECT u.*
(SELECT sum(trs.amount)
FROM transactions trs
WHERE u.id = trs.user AND trs.type = 'Recycle' AND
trs.TIME >= UNIX_TIMESTAMP(CURDATE())
) as amt
FROM (SELECT DISTINCT user_by
FROM xeon_users_rented
) AS xur JOIN
users u
ON xur.user_by = u.username
LIMIT 50
从我的数据库中选择一些数据。上面的查询工作正常。不过，我想也选择count(*)从xeon_users_rented哪里user_by = u.username这是我曾尝试：
SELECT u.*
(SELECT sum(trs.amount)
FROM transactions trs
WHERE u.id = trs.user AND trs.type = 'Recycle' AND
trs.TIME >= UNIX_TIMESTAMP(CURDATE())
) as amt,
(SELECT DISTINCT count(*)
FROM xeon_users_rented
WHERE xur.user_by = u.username
) AS ttl
FROM (SELECT DISTINCT user_by
FROM xeon_users_rented
) AS xur JOIN
users u
ON xur.user_by = u.username
LIMIT 50
但是，这使我的行总数xeon_users_rented为ttl-而不是其中的不同行总数username = user_by
我认为您只需修改一下子查询就可以完成您想做的事情。也就是说，将更select distinct改为group by：
SELECT u.*, xur.cnt,
(SELECT sum(trs.amount)
FROM transactions trs
WHERE u.id = trs.user AND trs.type = 'Recycle' AND
trs.TIME >= UNIX_TIMESTAMP(CURDATE())
) as amt
FROM (SELECT user_by, COUNT(*) as cnt
FROM xeon_users_rented
GROUP BY user_by
) xur JOIN
users u
ON xur.user_by = u.username
LIMIT 50;
一些注意事项：
SELECT DISTINCT并不是真正必要的，因为您可以使用进行相同的逻辑GROUP BY。因此，了解更为重要GROUP BY。LIMITno ORDER BY。这意味着，每次运行查询时，您可以获得一组不同的行。不好的做法。本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句