MySQL中的一对多查询 [英] One-to-many Query in MySQL

问题描述

在MySQL中查询一对多的最佳方法是什么?这是我正在使用的数据库的简化版本(如果有什么不正确的地方，请告诉我):

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)  
);  

(租金"表中有month和date_paid的原因是，租户不必一次付清所有租金).我想让租户的名字出现一次，这只是一次左加入，但是我希望每个月内列为每个租户的列中列出的特定月份的所有付款额，我不确定该怎么做.我不太确定该怎么做，因为您处理的列数未知，在MySQL中还没有涉及到.还是有更好的策略?另外，我将如何创建自己的变量，例如MONTH-YEAR(我认为MySQL中的本机变量不存在该变量).谢谢！

(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!

为了进一步简化，我正在使用以下格式:

create table rent(
tenant_id int not null,
year year,
amount_paid int,
foreign key (tenant_id) references tenant(tenant_id)
);


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)
);


如果我理解duffymo下面说的话，则应使用分组依据(我知道我在某个地方有误解，因为它仅显示每年的第一个示例):
SELECT Tenant.first_name, Rent.year, Rent.amount_paid
FROM Tenant
LEFT JOIN Rent
ON Tenant.tenant_id = Rent.tenant_id
GROUP BY year;


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
约翰500500空
Ann 1000 NULL NULL
鲍勃·空700700

first_name 2009 2008 2007
John 500 500 NULL
Ann 1000 NULL NULL
Bob NULL 700 700

推荐答案

如果您拥有MONTH和YEAR列，则可以根据需要选择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.

这篇关于MySQL中的一对多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！