MySQL 中的一对多查询
在 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 中的本机变量存在)。谢谢你!
编辑: 为了进一步简化,我使用这种格式:
<代码>
创建表租金(
tenant_id int 不为空,
年年,
amount_paid int,
外键(tenant_id)引用租户(tenant_id)
);
如果我理解 duffymo 下面所说的内容,我应该使用 group by (我知道我在某个地方误解了,因为它只显示了每年的第一个示例): <代码>
选择租户.first_name、租金.year、租金.amount_paid
来自租户
左连接租金
ON Tenant.tenant_id = Rent.tenant_id
按年份分组;
这就是我想要的查询的样子,每年下面的数字是支付的金额(我实际上只是意识到它比我解释的要复杂一点):
first_name 2009 2008 2007
约翰 500 500 NULL
安 1000 NULL NULL
鲍勃 空 700 700
What's the best way to query one-to-many in MySQL? This is a simplified version of the database I am working on (if anything doesn't look right tell me):
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)
);
(The reason that there is month and date_paid in the Rent table is because the tenant does not necessarily pay the rent all at once). What I want the tenant's name to appear once which would just be a Left Join, but I want all the amount paid in a particular month listed as columns for each tenant, I am not sure how to go about that. I am not really sure how to do that since your are dealing with an unknown amount of columns, haven't touched that yet in MySQL. Or is there a better strategy? Also, how would I go about creating my own variable like MONTH-YEAR (I don't think that exists as a native variable in MySQL). Thank you!
Edit:
Just to simplify it further I am using this format:
create table rent(
tenant_id int not null,
year year,
amount_paid int,
foreign key (tenant_id) references tenant(tenant_id)
);
If I understand what duffymo said below I should use group by (I know I am misunderstanding somewhere because it only shows the first example for each year):
SELECT Tenant.first_name, Rent.year, Rent.amount_paid
FROM Tenant
LEFT JOIN Rent
ON Tenant.tenant_id = Rent.tenant_id
GROUP BY year;
This is what I want the query to look like, the number under each year is the amount paid (I actually just realized it's a little bit more complex than what I how explained):
first_name 2009 2008 2007
John 500 500 NULL
Ann 1000 NULL NULL
Bob NULL 700 700
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如果您有“月份”和“年份”列,则可以执行 GROUP BY 来选择按您希望的方式细分的支付金额。如果您有 PAID_DATE 列,执行此操作的一种方法是使用在设置 PAID_DATE 时运行的 BEFORE INSERT 触发器。这样用户就不必输入值,并且可以保证数据完整性。
If you have MONTH and YEAR columns, you can do a GROUP BY to select amount paid broken out as you'd wish. If you have a PAID_DATE column, one way to do this would be to have a BEFORE INSERT trigger that runs when the PAID_DATE is set. That way users don't have to enter values, and data integrity can be guaranteed.