在 MySQL 中查询一对多的最佳方式是什么?这是我正在开发的数据库的简化版本(如果有什么看起来不对劲告诉我):
CREATE TABLE Tenant(
tenant_id int NOT NULL,
first_name varchar(20),
last_name varchar(20),
PRIMARY KEY (tenant_id)
);
CREATE TABLE Rent(
tenant_id int NOT NULL,
month enum('JAN', 'FEB', ...),
date_paid date NOT NULL,
amount_paid int NOT NULL,
FOREIGN KEY (tenant_id) REFERENCES Tenant(tenant_id)
);
(Rent表中之所以有month和date_paid是因为租户不一定一次性付清租金)。我希望租户的名字出现一次,这只是左连接,但我希望将特定月份支付的所有金额列为每个租户的列,我不知道该怎么做。我不太确定如何做到这一点,因为您正在处理未知数量的列,还没有在 MySQL 中触及它。或者有更好的策略吗?另外,我将如何创建自己的变量,例如 MONTH-YEAR (我不认为它作为 MySQL 中的本机变量存在)。谢谢你!
编辑:
为了进一步简化,我使用这种格式:
create table rent(
tenant_id int not null,
year year,
amount_paid int,
foreign key (tenant_id) references tenant(tenant_id)
);
如果我理解 duffymo 下面所说的内容,我应该使用 group by (我知道我在某个地方误解了,因为它只显示了每年的第一个示例):
SELECT Tenant.first_name, Rent.year, Rent.amount_paid
FROM Tenant
LEFT JOIN Rent
ON Tenant.tenant_id = Rent.tenant_id
GROUP BY year;
这就是我希望查询的样子,每年下面的数字是支付的金额(实际上我刚刚意识到它比我解释的要复杂一点):
名字 2009 2008 2007
约翰 500 500 空
安 1000 空 空
鲍勃 空 700 700