ti-enxame.com

MySQL JOIN a linha mais recente apenas?

Eu tenho um cliente de mesa que armazena um customer_id, e-mail e referência. Há uma tabela adicional customer_data que armazena um registro histórico das alterações feitas no cliente, ou seja, quando há uma alteração feita, uma nova linha é inserida.

Para exibir as informações do cliente em uma tabela, as duas tabelas precisam ser unidas, no entanto, somente a linha mais recente de customer_data deve ser unida à tabela de clientes.

Fica um pouco mais complicado, pois a consulta é paginada, portanto, tem um limite e um deslocamento.

Como posso fazer isso com o MySQL? Eu acho que estou querendo colocar um DISTINCT lá em algum lugar ...

A consulta no minuto é assim:

SELECT *, CONCAT(title,' ',forename,' ',surname) AS name
FROM customer c
INNER JOIN customer_data d on c.customer_id=d.customer_id
WHERE name LIKE '%Smith%' LIMIT 10, 20

Além disso, estou certo em pensar que posso usar o CONCAT com o LIKE dessa maneira?

(Eu aprecio que INNER JOIN pode ser o tipo errado de JOIN para usar. Eu realmente não tenho idéia de qual é a diferença entre os diferentes JOINs. Eu vou olhar para isso agora!)

64
bcmcfc

Você pode tentar o seguinte:

SELECT    CONCAT(title, ' ', forename, ' ', surname) AS name
FROM      customer c
JOIN      (
              SELECT    MAX(id) max_id, customer_id 
              FROM      customer_data 
              GROUP BY  customer_id
          ) c_max ON (c_max.customer_id = c.customer_id)
JOIN      customer_data cd ON (cd.id = c_max.max_id)
WHERE     CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%' 
LIMIT     10, 20;

Note que um JOIN é apenas um sinônimo para INNER JOIN.

Caso de teste:

CREATE TABLE customer (customer_id int);
CREATE TABLE customer_data (
   id int, 
   customer_id int, 
   title varchar(10),
   forename varchar(10),
   surname varchar(10)
);

INSERT INTO customer VALUES (1);
INSERT INTO customer VALUES (2);
INSERT INTO customer VALUES (3);

INSERT INTO customer_data VALUES (1, 1, 'Mr', 'Bobby', 'Smith');
INSERT INTO customer_data VALUES (2, 1, 'Mr', 'Bob', 'Smith');
INSERT INTO customer_data VALUES (3, 2, 'Mr', 'Jane', 'Green');
INSERT INTO customer_data VALUES (4, 2, 'Miss', 'Jane', 'Green');
INSERT INTO customer_data VALUES (5, 3, 'Dr', 'Jack', 'Black');

Resultado (consulta sem o LIMIT e WHERE):

SELECT    CONCAT(title, ' ', forename, ' ', surname) AS name
FROM      customer c
JOIN      (
              SELECT    MAX(id) max_id, customer_id 
              FROM      customer_data 
              GROUP BY  customer_id
          ) c_max ON (c_max.customer_id = c.customer_id)
JOIN      customer_data cd ON (cd.id = c_max.max_id);

+-----------------+
| name            |
+-----------------+
| Mr Bob Smith    |
| Miss Jane Green |
| Dr Jack Black   |
+-----------------+
3 rows in set (0.00 sec)
105
Daniel Vassallo

Se você estiver trabalhando com consultas pesadas, é melhor mover a solicitação para a última linha na cláusula where. É muito mais rápido e parece mais limpo.

SELECT c.*,
FROM client AS c
LEFT JOIN client_calling_history AS cch ON cch.client_id = c.client_id
WHERE
   cch.cchid = (
      SELECT MAX(cchid)
      FROM client_calling_history
      WHERE client_id = c.client_id AND cal_event_id = c.cal_event_id
   )
62
Danny Coulombe

Presumindo que a coluna de incremento automático em customer_data é denominada Id, você pode fazer:

SELECT CONCAT(title,' ',forename,' ',surname) AS name *
FROM customer c
    INNER JOIN customer_data d 
        ON c.customer_id=d.customer_id
WHERE name LIKE '%Smith%'
    AND d.ID = (
                Select Max(D2.Id)
                From customer_data As D2
                Where D2.customer_id = D.customer_id
                )
LIMIT 10, 20
10
Thomas

Para qualquer pessoa que precise trabalhar com uma versão mais antiga do MySQL (pré-5.0 ish), você não conseguirá fazer subconsultas para esse tipo de consulta. Aqui está a solução que eu pude fazer e pareceu funcionar muito bem.

SELECT MAX(d.id), d2.*, CONCAT(title,' ',forename,' ',surname) AS name
FROM customer AS c 
LEFT JOIN customer_data as d ON c.customer_id=d.customer_id 
LEFT JOIN customer_data as d2 ON d.id=d2.id
WHERE CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%'
GROUP BY c.customer_id LIMIT 10, 20;

Essencialmente, isso é encontrar o ID máximo de sua tabela de dados unindo-a ao cliente e, em seguida, unindo a tabela de dados ao ID máximo encontrado. A razão para isso é que selecionar o máximo de um grupo não garante que o restante dos dados corresponda ao id, a menos que você o reconecte a si mesmo.

Eu não testei isso em versões mais recentes do MySQL, mas funciona em 4.0.30.

9
payne8

Eu sei que esta questão é antiga, mas tem muita atenção ao longo dos anos e acho que falta um conceito que pode ajudar alguém em um caso semelhante. Eu estou adicionando aqui por causa da integralidade.

Se você não pode modificar seu esquema original de banco de dados, muitas boas respostas foram fornecidas e resolvem o problema.

Se você puder , no entanto, modificar seu esquema, aconselho adicionar um campo em sua tabela customer que contém a id do último registro customer_data para este cliente:

CREATE TABLE customer (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  current_data_id INT UNSIGNED NULL DEFAULT NULL
);

CREATE TABLE customer_data (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   customer_id INT UNSIGNED NOT NULL, 
   title VARCHAR(10) NOT NULL,
   forename VARCHAR(10) NOT NULL,
   surname VARCHAR(10) NOT NULL
);

Consultando clientes

A consulta é tão fácil e rápida quanto possível:

SELECT c.*, d.title, d.forename, d.surname
FROM customer c
INNER JOIN customer_data d on d.id = c.current_data_id
WHERE ...;

A desvantagem é a complexidade extra ao criar ou atualizar um cliente.

Atualizando um cliente

Sempre que você quiser atualizar um cliente, insira um novo registro na tabela customer_data e atualize o registro customer.

INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(2, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = 2;

Criando um cliente

Criar um cliente é apenas uma questão de inserir a entrada customer e, em seguida, executar as mesmas instruções:

INSERT INTO customer () VALUES ();

SET @customer_id = LAST_INSERT_ID();
INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(@customer_id, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = @customer_id;

Empacotando

A complexidade extra para criar/atualizar um cliente pode ser assustadora, mas pode ser facilmente automatizada com gatilhos.

Finalmente, se você estiver usando um ORM, isso pode ser muito fácil de gerenciar. O ORM pode cuidar de inserir os valores, atualizar os IDs e unir as duas tabelas automaticamente para você.

Aqui está como seu modelo Customer mutável se pareceria:

class Customer
{
    private int id;
    private CustomerData currentData;

    public Customer(String title, String forename, String surname)
    {
        this.update(title, forename, surname);
    }

    public void update(String title, String forename, String surname)
    {
        this.currentData = new CustomerData(this, title, forename, surname);
    }

    public String getTitle()
    {
        return this.currentData.getTitle();
    }

    public String getForename()
    {
        return this.currentData.getForename();
    }

    public String getSurname()
    {
        return this.currentData.getSurname();
    }
}

E o seu modelo CustomerData imutável, que contém apenas getters:

class CustomerData
{
    private int id;
    private Customer customer;
    private String title;
    private String forename;
    private String surname;

    public CustomerData(Customer customer, String title, String forename, String surname)
    {
        this.customer = customer;
        this.title    = title;
        this.forename = forename;
        this.surname  = surname;
    }

    public String getTitle()
    {
        return this.title;
    }

    public String getForename()
    {
        return this.forename;
    }

    public String getSurname()
    {
        return this.surname;
    }
}
2
Benjamin
SELECT CONCAT(title,' ',forename,' ',surname) AS name * FROM customer c 
INNER JOIN customer_data d on c.id=d.customer_id WHERE name LIKE '%Smith%' 

eu acho que você precisa mudar c.customer_id para c.id

outra estrutura de tabela de atualização

2
Pramendra Gupta

Você também pode fazer isso

SELECT    CONCAT(title, ' ', forename, ' ', surname) AS name
FROM      customer c
LEFT JOIN  (
              SELECT * FROM  customer_data ORDER BY id DESC
          ) customer_data ON (customer_data.customer_id = c.customer_id)
GROUP BY  c.customer_id          
WHERE     CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%' 
LIMIT     10, 20;
0
Ajay Kumar

É uma boa ideia registrar os dados reais na tabela " customer_data ". Com esses dados, você pode selecionar todos os dados da tabela "customer_data" como desejar.

0
Burçin

Isso pode ajudar

Como selecionar o conjunto mais recente de registros datados de uma tabela mysql

Você pode usar uma subconsulta para obter o (s) registro (s) mais recente (s) e, em seguida, associar-se ao seu cliente.

0
Jaydee