是否有一种方法来插入SQL中最频繁发生的值?

Question

我需要在MySQL中做一个快速计算后将数据从一个表插入到另一个表中，以比较用户的交易金额(500,150和100)与他们的总金额(500 * 2 = 1000,150 * 3 = 450,100 * 5 = 500)，并将金额最高的交易金额(500)插入到另一个表(donationrequests)作为ASK。

我如何比较多个记录，然后在MySQL中只插入一个?我对这部分很纠结。下面是我的模式和我目前在哪里。

CREATE TABLE transactions
(
DONOR_ID TINYTEXT NOT NULL,
TRANSACTION_AMT INT
);
INSERT INTO transactions
(
DONOR_ID, TRANSACTION_AMT
) VALUES 
("ALLEN", 500 ),
("ALLEN", 500 ),
("ALLEN", 150 ),
("ALLEN", 150 ),
("ALLEN", 150 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 )
CREATE TABLE donationrequests
(
DONOR_ID TINYTEXT NOT NULL,
ASK int
PRIMARY KEY (USER)
);

INSERT INTO donationrequests (DONOR_ID, ASK)
SELECT DISTINCT DONOR_ID AS d, SUM(TRANSACTION_AMT) AS t
FROM transactions
GROUP BY d
ORDER BY t DESC;

这会在捐赠请求中产生("ALLEN"， 1950)它应该产生("ALLEN"， 500)

Answer 1

您可以使用ROW_NUMBER对每个donos的交易SUM进行排名，然后插入">donationrequests">

INSERT INTO donationrequests 
WITH cte AS(
SELECT *, 
ROW_NUMBER() OVER(PARTITION BY DONOR_ID 
ORDER     BY SUM(TRANSACTION_AMT) DESC
) AS rn
FROM transactions
GROUP BY DONOR_ID, TRANSACTION_AMT
)
SELECT DONOR_ID, TRANSACTION_AMT 
FROM cte 
WHERE rn = 1

点击这里查看演示。