Início / dba / Perguntas / 211289
Accepted
Philippe Gachoud
Asked: 2018-07-04 18:38:31 +0800 CST

maior n por grupo com uma junção de 3 mesas

  • 772

Trabalhando em um projeto com os seguintes objetos:

  • People

    • Eu iria

  • Vehicles

    • Eu iria
    • modelo: texto

e uma tabela de links para proprietários que passa pelas alterações de proprietários

  • Owner_Changes
    • old_person_id
    • new_person_id
    • id_veiculo
    • encontro

Estou tentando obter um proprietário de veículo (Pessoas->Mudanças de proprietário->Veículos, e a única maneira de conseguir isso é obter a última alteração de proprietário por data.

Estou tentando juntar People/OwnerChanges/Vehicles com a seguinte consulta e obter sempre os veículos antigos associados às pessoas

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

Ids 23e 111são exemplos de duplicatas que eu gostaria de escolher o mais recente por data (DESC): insira a descrição da imagem aqui

Qualquer ajuda ou documentação sobre o maior n por grupo com 3 tabelas será muito apreciada.

A instrução de criação da tabela está a seguir:

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

Versão: MariaDB 10.1 :

pg@vmDebDev1: ~$ mysql --version mysql Ver 15.1 Distrib 10.1.26-MariaDB, para debian-linux-gnu (x86_64) usando readline 5.2

mysql join

1 respostas

  1. Best Answer

    Qualquer ajuda ou documentação sobre o maior n por grupo com 3 tabelas será muito apreciada.

    Tabelas de dados de origem:

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

    Consulta:

    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

    violino

    ATUALIZAÇÃO COM JUNÇÕES

    Por favor, escreva com JOIN...ON em vez de vírgula. – Rick James

    Isso, é claro, reduzirá a visibilidade, mas se você quiser...

    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
    • 1

relate perguntas