mysql - 尝试通过查询在输出下方打印

Question

我有 3 个表 Client、Property 和 Owner 下面是他们的模式，

create table Property (property_no varchar(20), p_address varchar(50), rent_start date, rent_finish 
date, rent_per_day int, primary key(property_no,rent_start, rent_finish));

create table Client (client_no varchar(10), client_name char(10), property_no varchar(10), foreign 
key(property_no) references Property(property_no),primary key(client_no,client_name,property_no));

create table Owner (owner_no varchar(10), owner_name char(10), property_no varchar(10), foreign 
key(property_no) references Property(property_no), primary key(owner_no,property_no));

insert into Property values('pg4', '6 Lawrence St.', '2000-01-07','2001-8-31', 50);
insert into Property values('pg16', '5 Novar Dr.', '2001-09-01','2002-9-1', 70);
insert into Property values('pg4', '6 Lawrence St.', '1999-09-02','2000-6-10', 50);
insert into Property values('pg36', '2 Manor Rd', '2000-10-10','2001-12-1', 60);
insert into Property values('pg16', '5 Novar Dr.', '2002-11-1','2003-8-10', 70);

insert into owner values('co40', 'tina', 'pg4');
insert into owner values('co93', 'tony', 'pg16');
insert into owner values('co93', 'tony', 'pg36');

insert into Client values('cr56','aline', 'pg4');
insert into Client values('cr56','aline', 'pg36');
insert into Client values('cr56','aline', 'pg16');
insert into Client values('cr76','john', 'pg4');
insert into Client values('cr76','john', 'pg16');

我正在尝试如下打印输出。

我尝试了下面的查询，它给了我奇怪的输出：

Select  c.client_No
     , p.property_No
     ,  c.client_Name
     , p.p_address
     , p.rent_start
     , p.rent_finish
     , DATEDIFF(p.rent_finish, p.rent_start) as Total_Days
     , p.Rent_Per_Day
     , DATEDIFF(p.rent_finish, p.rent_start)*p.Rent_Per_Day as Total_Rent
     , o.owner_No
     , o.owner_name 
 from Property p 
 right 
  join  Client c 
    on p.property_no = c.property_no 
  left 
  join Owner o 
    on p.property_no = o.property_no
 group 
    by property_no
     , rent_start
 order 
    by client_no desc
     , property_no desc;

输出是：

任何帮助表示赞赏。提前致谢。

标签： mysqlsqljoin

Answer 1

您的表格未正确规范化。在“客户”和“物业”表上，作为您的 property_no 也取决于租金的开始和结束。