получить только записи, которые имеют похожие значения в столбце A и разные значения в столбце B

#sql #postgresql

Вопрос:

В моем наборе данных есть 2 таблицы:

животные с animal_id и animal_type
владельцы с animal_id и owner_name

Я хочу получать записи только для тех животных ( их имена владельцев), у владельцев которых есть КОШКА и другое домашнее животное.

Вот моя схема:

 CREATE TABLE IF NOT EXISTS `animals` (
  `animal_id` int(6) unsigned NOT NULL,
  `animal_type` varchar(200) NOT NULL,
  PRIMARY KEY (`animal_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `animals` (`animal_id`, `animal_type`) VALUES
  ('1', 'cat'),
  ('2', 'dog'),
  ('3', 'cat'),
  ('4', 'cat'),
  ('5', 'dog'),
  ('6', 'dog'),
  ('7', 'cat'),
  ('8', 'dog'),
  ('9', 'cat'),
  ('10', 'hamster');

CREATE TABLE IF NOT EXISTS `owners` (
  `animal_id` int(6) unsigned NOT NULL,
  `owner_name` varchar(200) NOT NULL,
  PRIMARY KEY (`animal_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `owners` (`animal_id`, `owner_name`) VALUES
  ('1', 'CatOwner'),
  ('2', 'DogOwner'),
  ('3', 'CatsOwner'),
  ('4', 'CatsOwner'),
  ('5', 'DogsOwner'),
  ('6', 'DogsOwner'),
  ('7', 'CatDogOwner'),
  ('8', 'CatDogOwner'),
  ('9', 'CatHamsterOwner'),
  ('10', 'CatHamsterOwner');

Я могу фильтровать и показывать только записи для владельцев, у которых более одного питомца:

 SELECT *
FROM animals AS a
JOIN owners AS o
ON a.animal_id = o.animal_id 
WHERE o.owner_name IN (SELECT o.owner_name
                       FROM animals AS a
                       JOIN owners AS o
                       ON a.animal_id = o.animal_id 
                       GROUP BY o.owner_name HAVING COUNT(o.owner_name) > 1)

Пожалуйста, скажите мне, как я могу сделать это таким образом:

1. Указанный DDL недопустим для Postgres. Похоже, вы на самом деле используете MySQL

2. НАЛИЧИЕ выражений в регистре.

3. @a_horse_with_no_name это всего лишь пример использования sqlfiddle.com / , в моих таблицах больше столбцов

Ответ №1:

Я бы предложил оконные функции:

 SELECT ao.*
FROM (SELECT a.*, o.owner_name,
             SUM(CASE WHEN a.animal_type = 'cat' THEN 1 ELSE 0 END) OVER (PARTITION BY o.owner_name) as num_cats,
             COUNT(*) OVER (PARTITION BY o.owner_name) as num_animals
      FROM owners o JOIN
           animals a
           ON a.animal_id = o.animal_id 
     ) ao
WHERE num_cats > 0 AND num_animals >= 2;

Примечание: мне не ясно, является ли условие для более чем одного животного или животного, которое не является кошкой. Если последнее, то используйте:

 SELECT ao.*
FROM (SELECT a.*, o.owner_name,
             SUM(CASE WHEN a.animal_type = 'cat' THEN 1 ELSE 0 END) OVER (PARTITION BY o.owner_name) as num_cats,
             COUNT(*) OVER (PARTITION BY o.owner_name) as num_animals
      FROM owners o JOIN
           animals a
           ON a.animal_id = o.animal_id 
     ) ao
WHERE num_cats > 0 AND num_animals <> num_cats;