给定以下查询,如何返回包含大多数事务的p_名称?同样,我如何返回大多数交易的t U金额。当然,我想在这一个查询中完成这一切。

SELECT t.*, p.*
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id

它可以返回如下内容:
t_amount     t_platform      t_user     p_id      p_name

100.00       windows         122         20       simmons
200.00       windows         211         20       simmons
100.00       mac             200         18       smith
100.00       linux           190         20       simmons
100.00       mac             100         18       smith

所以在给定结果集的情况下,我将得到best_partner=simmons和best_amount=100.00
谢谢!

最佳答案

我假设“最佳合作伙伴”=交易次数最多的合作伙伴,“最佳金额”=最频繁发生的交易金额。
要对事务进行计数,可以使用count()函数并按分组。像这样的:

SELECT p.name,count(t.id) as transactionCount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p.name
ORDER BY 2 DESC
LIMIT 1

类似于“最佳用量”:
SELECT t.amount, Count(t.id) as transactionAmountCount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY t.amount
ORDER BY 2 DESC
LIMIT 1

编辑:合并为两个子查询:
SELECT
(SELECT p.name
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY p.name
ORDER BY count(t.id) DESC
LIMIT 1) as best_partner
,
(SELECT t.amount
FROM transactions t
LEFT JOIN partners p ON p.id=t.partner_id
GROUP BY t.amount
ORDER BY Count(t.id) DESC
LIMIT 1) as most_occuring_transaction_amount

10-04 17:48