mysql - 在mysql中使用group by

我有以下表格创建脚本：

CREATE TABLE assetcost(
assettype VARCHAR(20) PRIMARY KEY,
rentamt INT
);

CREATE TABLE furnishitem(
itemid VARCHAR(4) PRIMARY KEY CHECK(itemid LIKE 'I%'),
description VARCHAR(30),
availablesets INT,
assettype VARCHAR(25),
specialCharge CHAR(1) CHECK(specialcharge IN ('Y','N')),
FOREIGN KEY(assettype) REFERENCES assetcost(assettype)
);

CREATE TABLE custdetail(
custid VARCHAR(5) PRIMARY KEY CHECK(custid LIKE 'C%'),
custname VARCHAR(30)
);


CREATE TABLE transaction(
transid INT UNIQUE,
custid VARCHAR(5) ,
itemid VARCHAR(4),
sets INT,
days INT,
amount INT,
returned char(1) Check (returned in('Y','N')),
FOREIGN KEY(custid)REFERENCES custdetail(custid),
FOREIGN KEY(itemid)REFERENCES furnishitem(itemid)
);

我在编写查询以显示已支付/已支付最低总金额的客户的custid和custname时遇到错误，而不管返回的项目是什么。
我的问题是：

select t.custid,c.custname
    -> from transaction t inner join custdetail c
    -> on t.custid=c.custid
    -> group by t.custid
    -> having sum(amount)=(select min(sum(amount) from transaction group by custid);

最佳答案

你也许可以用一种更优雅的方式解决这个问题，但是一个快速解决问题的方法是：

SELECT t.custid, c.custname
FROM TRANSACTION t
INNER JOIN custdetail c ON t.custid = c.custid
GROUP BY t.custid
HAVING sum(amount) = (
    SELECT sum(amount)
    FROM TRANSACTION
    GROUP BY custid
    ORDER BY 1 ASC
    LIMIT 1
    );

你不可能一次就能做到。此方法将获取具有最小值的custid的sum行。

关于mysql - 在mysql中使用group by，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20194993/