每組最大 n 與 3 個表連接

處理具有以下對象的項目：

• People

  • ID

• Vehicles

  • ID
  • 型號：文字

以及通過所有者更改的所有者連結表

• Owner_Changes
  • old_person_id
  • new_person_id
  • 車輛編號
  • 日期

我正在嘗試獲取車主（人->所有者更改->車輛，實現這一目標的唯一方法是按日期獲取最新的車主更改。

我正在嘗試通過以下查詢加入 People/OwnerChanges/Vehicles 並始終獲取與人相關的舊車輛

SELECT  `people`.`id`, `people`.`name`, `people`.`surname`, `people`.`email`,
       `people`.`city`, `people`.`receives_news` , `vehicles`.`model`,
       `vehicles`.`id`, `owner_changes`.`date`
   FROM  `bm_prod`.`people`
   INNER JOIN  
       ( SELECT  vehicle_id, new_person_id, date, MAX(date) maxDate
           FROM  owner_changes
           GROUP BY  id 
       ) owner_changes  ON `owner_changes`.`new_person_id` = `people`.`id`
     AND  `owner_changes`.`date` = `owner_changes`.`maxDate`
   INNER JOIN  `bm_prod`.`vehicles`  ON (`owner_changes`.`vehicle_id`
                                                   = `vehicles`.`id`)
   WHERE  `receives_news` = '1'
     AND  NOT (`email` = '')
     AND  `people`.`receives_news` = '1'
     AND  LOWER(`vehicles`.`model`) LIKE '%mts%'
   ORDER BY  `vehicles`.`id` ASC, `owner_changes`.`date` DESC
   LIMIT  200 

Ids23和111是我想選擇最新的按日期 (DESC) 的重複範例：

任何關於 3 個表的每個組最大 n 的幫助或文件將不勝感激。

創建表語句如下：

CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`surname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`birthdate` date DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`fax_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`mobile_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Mobile Number',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`street_number` varchar(255) DEFAULT NULL,
`zipcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`city` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`country` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`company` varchar(255) NOT NULL COMMENT 'Company working in',
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'a comment on person',
`receives_news` tinyint(1) NOT NULL DEFAULT '1',
`enumerate_id` int(11) NOT NULL COMMENT 'person_category_id',
`driving_license_type_enumerate_id` int(11) DEFAULT NULL COMMENT 'enumerate_id',
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3299 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC


CREATE TABLE `vehicles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`provider_id` int(11) NOT NULL,
`model` varchar(255) COLLATE utf8_bin NOT NULL,
`displacement` int(11) DEFAULT NULL COMMENT 'Cylindrée',
`type_reception` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Reception_par_type',
`km` bigint(20) DEFAULT NULL,
`first_use` date DEFAULT NULL,
`color` varchar(255) COLLATE utf8_bin NOT NULL,
`enumerate_id` int(11) DEFAULT NULL COMMENT 'reference to enumerate->vehicle_usage_type',
`chassis_number` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Numéro de chassis',
`plate_number` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Numéro de plaque',
`vehicle_type_id` int(11) NOT NULL,
`warranty` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Warranty',
`production_year` int(11) DEFAULT NULL,
`comment` mediumtext COLLATE utf8_bin,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1792 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `owner_changes` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'identifier',
`vehicle_id` int(11) NOT NULL,
`new_person_id` int(11) DEFAULT NULL,
`old_person_id` int(11) DEFAULT NULL,
`buy_price` float DEFAULT NULL,
`sell_price` float DEFAULT NULL,
`date` date DEFAULT NULL,
`km` bigint(20) DEFAULT NULL,
`comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2336 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

版本：MariaDB 10.1：

pg@vmDebDev1: ~$ mysql –version mysql Ver 15.1 Distrib 10.1.26-MariaDB，使用 readline 5.2 的 debian-linux-gnu (x86_64)

任何關於 3 個表的每個組最大 n 的幫助或文件將不勝感激。

源數據表：

create table people (id int, name text) 
select 1 id, 'John' name union all
select 2   , 'Mark'      union all
select 3   , 'Igor';

create table vehicles (id int, model text) 
select 11 id,'digger' model union all
select 12   ,'crane';

create table ownerchanges (old_person_id int, new_person_id int, vehicle_id int, cdate date)
select null old_person_id, 1 new_person_id, 11 vehicle_id, '2018-01-01' cdate union all
select    1              , 3              , 11           , '2018-01-02'       union all
select    3              , 2              , 11           , '2018-01-03'       union all
select null              , 1              , 12           , '2018-01-01'       union all
select    1              , 3              , 12           , '2018-01-02' ;

詢問：

select v.id, v.model, p.id, p.name
from vehicles v, 
    people p,
    ownerchanges o,
    (select oc.vehicle_id, max(oc.cdate) maxdate -- select max date
     from ownerchanges oc                        -- of owner change
     group by oc.vehicle_id) last                -- for each vehicle
where o.vehicle_id = last.vehicle_id   -- use it to select
 and o.cdate = last.maxdate           -- proper change record
 and o.vehicle_id = v.id     -- and attach data
 and o.new_person_id = p.id; -- we need

小提琴

更新加入

請用 JOIN…ON 代替逗號。——里克·詹姆斯

當然，這會降低能見度，但是如果您願意…

select v.id, v.model, p.id, p.name
from ownerchanges o
join people p on o.new_person_id = p.id
join vehicles v on o.vehicle_id = v.id
join (select oc.vehicle_id, max(oc.cdate) maxdate
     from ownerchanges oc                       
     group by oc.vehicle_id) last  on o.vehicle_id = last.vehicle_id 
                                  and o.cdate = last.maxdate

引用自：https://dba.stackexchange.com/questions/211289