Estou trabalhando com o servidor mariadb versão 10.6.4.
Esta é a minha definição de tabela:
CREATE TABLE `tmp_dba` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`case_id` bigint(20) DEFAULT NULL,
`client_id` bigint(20) NOT NULL,
`arrival` date DEFAULT NULL,
`departure` date DEFAULT NULL,
PRIMARY KEY (`ID`)
)
Aqui estão alguns dados de exemplo:
INSERT INTO `tmp_dba` VALUES
(1,10,1000,'2018-10-02','2019-04-25'),
(2,10,1000,'2019-04-26','2019-05-01'),
(3,10,1000,'2019-05-02',NULL),
(4,20,2000,'2018-11-21',NULL),
(5,20,2001,'2018-11-21',NULL),
(6,20,2002,'2018-11-21',NULL),
(7,30,3000,'2019-03-04','2022-01-01'),
(8,30,3001,'2019-03-04','2022-01-01'),
(9,30,3002,'2019-03-04','2022-01-01'),
(10,30,3003,'2019-03-04','2022-01-01'),
(11,30,3004,'2019-03-04','2022-01-01');
O que eu gostaria de conseguir é determinar o MIN(chegada) de cada grupo de case_id e client_id e caso a partida não seja nula MAX(partida) deve ser exibida caso contrário nula.
Eu quero acabar com apenas uma linha por cliente fornecendo os dados mencionados acima.
Por exemplo, para case_id = 10, quero ver 1 linha assim:
10;1000;2018-10-02;NULL.
Para case_id = 20, o resultado deve ser 4 linhas, devido a 4 combinações diferentes de case_id e client_id.
Para case_id = 30 deve haver 5 linhas mostradas, devido a 5 combinações diferentes de case_id e client_id.
Devo estar fazendo algo errado ao usar group by.
Outras informações:
- Os dados são consecutivos no sentido de que um novo registro inserido terá um ID maior que o antigo. Também será um novo recorde - em relação ao anterior - no futuro, sempre. Isso não significa que o próximo registro sempre teria uma chegada no dia seguinte à última partida.
- Um novo registro na tabela para o mesmo cliente sempre definirá a data de saída do registro anterior. A saída de registro anterior nesse caso não pode ser nula.
- Quando não houver saída e o cliente estiver "in" parado, a saída será sempre nula.
- A partida deve ser menor que a chegada, a menos que a partida seja nula.
- A chegada não pode ser nula.
Isso é factível?
Solução
SELECT
-- First solution, but wrong, GROUP_CONCAT(ID) avoids error below
-- ID, case_id, client_id, <-- only_full_group_by - error
GROUP_CONCAT(ID), case_id, client_id,
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id
Muito obrigado por sua ajuda.
Steffi
