SQL - выбор только определенных строк, если они существуют

ProgramBox

SQL — выбор только определенных строк, если они существуют

Post author:admin
Запись опубликована:1 мая, 2023
Post category:Вопросы по программированию

#sql

Вопрос:

У меня есть таблица, которая содержит домашние адреса и почтовые адреса. Это выглядит следующим образом:

 ID   Name   StNum   StName     City    State   Zip    Type
--   ----   -----   ------     ----    -----   ---    ----
1    Joe    1234    Main St    Waco    TX      76767  HOM
1    Joe    2345    High St    Waco    TX      76763  MLG
2    Amy    3456    Broad St   Athens  GA      34622  HOM
3    Mel    987     Front St   Cary    NC      65331  HOM
3    Mel    1111    Main Ave   Hilo    HI      99779  MLG

Мне нужно написать инструкцию SQL, которая вернет почтовый адрес (запись MLG), только если он существует, а если нет, вернет домашний адрес (запись HOM).

Ожидаемые результаты из этой таблицы будут:

 ID   Name   StNum   StName     City    State   Zip    Type
--   ----   -----   ------     ----    -----   ---    ----
1    Joe    2345    High St    Waco    TX      76763  MLG
2    Amy    3456    Broad St   Athens  GA      34622  HOM
3    Mel    1111    Main Ave   Hilo    HI      99779  MLG

Любая помощь, которую вы можете предоставить, была бы высоко оценена! Спасибо!

1. Вы можете сделать это многими способами. Вы можете использовать Row_Number() поверх (разделение по порядку идентификаторов по типу desc), а затем взять все, где row_number = 1 — если у вас есть только HOM и MLG

2. какую базу данных вы используете? Пожалуйста, отметьте соответствующую базу данных

Ответ №1:

использовать коррелированный подзапрос

 select * from
(
select *,case when Type='MLG' then 1 else 0 end as typeval
from tablename
)A where typeval in (select max(case when Type='MLG' then 1 else 0 end) from tablename b 
where a.name=b.name)

Или, если ваша база данных поддерживает row_number() , вы можете попробовать ниже —

 select * from
(
select *, row_number() over(partition by name order by case when Type='MLG' then 1 else 0 end desc)
from tablename
)A where rn=1

Ответ №2:

В случае, если вы используете SQL Server, я бы решил это с помощью функции ROW_NUMBER.

 SELECT ID, Name, StNum, StName, City, State, Zip, Type
FROM (
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type DESC) AS Rn
      FROM yourtable
      ) 
  WHERE Rn = 1

Ответ №3:

Это можно сделать с помощью WHERE предложения, исключающего идентификаторы пользователей, у которых есть MLG

Схема (MySQL версии 5.7)

 CREATE TABLE test (
  `ID` INTEGER,
  `Name` VARCHAR(3),
  `StNum` INTEGER,
  `StName` VARCHAR(8),
  `City` VARCHAR(6),
  `State` VARCHAR(2),
  `Zip` INTEGER,
  `Type` VARCHAR(3)
);

INSERT INTO test
  (`ID`, `Name`, `StNum`, `StName`, `City`, `State`, `Zip`, `Type`)
VALUES
  ('1', 'Joe', '1234', 'Main St', 'Waco', 'TX', '76767', 'HOM'),
  ('1', 'Joe', '2345', 'High St', 'Waco', 'TX', '76763', 'MLG'),
  ('2', 'Amy', '3456', 'Broad St', 'Athens', 'GA', '34622', 'HOM'),
  ('3', 'Mel', '987', 'Front St', 'Cary', 'NC', '65331', 'HOM'),
  ('3', 'Mel', '1111', 'Main Ave', 'Hilo', 'HI', '99779', 'MLG');

Запрос # 1

 SELECT id,
       name,
       StNum,
       StName,
       City,
       State,
       Zip,
       Type
FROM test t1
WHERE t1.`Type` = 'MLG'
   OR t1.id NOT IN
   (
        SELECT id
        FROM test t2
        WHERE t2.`Type` = 'MLG'
   );

Вывод :

 | id  | name | StNum | StName   | City   | State | Zip   | Type |
| --- | ---- | ----- | -------- | ------ | ----- | ----- | ---- |
| 1   | Joe  | 2345  | High St  | Waco   | TX    | 76763 | MLG  |
| 2   | Amy  | 3456  | Broad St | Athens | GA    | 34622 | HOM  |
| 3   | Mel  | 1111  | Main Ave | Hilo   | HI    | 99779 | MLG  |

Просмотр в DB Fiddle

Или моя первая тупая версия :

Это можно сделать с помощью UNION

Схема (MySQL версии 5.7)

 CREATE TABLE test (
  `ID` INTEGER,
  `Name` VARCHAR(3),
  `StNum` INTEGER,
  `StName` VARCHAR(8),
  `City` VARCHAR(6),
  `State` VARCHAR(2),
  `Zip` INTEGER,
  `Type` VARCHAR(3)
);

INSERT INTO test
  (`ID`, `Name`, `StNum`, `StName`, `City`, `State`, `Zip`, `Type`)
VALUES
  ('1', 'Joe', '1234', 'Main St', 'Waco', 'TX', '76767', 'HOM'),
  ('1', 'Joe', '2345', 'High St', 'Waco', 'TX', '76763', 'MLG'),
  ('2', 'Amy', '3456', 'Broad St', 'Athens', 'GA', '34622', 'HOM'),
  ('3', 'Mel', '987', 'Front St', 'Cary', 'NC', '65331', 'HOM'),
  ('3', 'Mel', '1111', 'Main Ave', 'Hilo', 'HI', '99779', 'MLG');

Запрос # 1

 SELECT id,
       name,
       StNum,
       StName,
       City,
       State,
       Zip,
       Type
FROM test t1
WHERE t1.`Type` = 'MLG'
UNION ALL
SELECT id,
       name,
       StNum,
       StName,
       City,
       State,
       Zip,
       Type
FROM test t2
WHERE t2.id NOT IN (SELECT id FROM test t3 WHERE t3.`Type` = 'MLG')
ORDER BY id;

Вывод

 | id  | name | StNum | StName   | City   | State | Zip   | Type |
| --- | ---- | ----- | -------- | ------ | ----- | ----- | ---- |
| 1   | Joe  | 2345  | High St  | Waco   | TX    | 76763 | MLG  |
| 2   | Amy  | 3456  | Broad St | Athens | GA    | 34622 | HOM  |
| 3   | Mel  | 1111  | Main Ave | Hilo   | HI    | 99779 | MLG  |

Просмотр в DB Fiddle

Ответ №4:

Это запрос приоритизации. При двух значениях часто самым простым методом является union all with not exists (или not in ).

Это плохо обобщается для большего количества значений, удобно использовать row_number() with case :

 select t.*
from (select t.*,
             row_number() over (partition by id
                                order by (case when type = 'MLG' then 1 else 2 end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

В вашем конкретном случае вы могли бы использовать order by type desc , потому что два типа имеют приоритет в обратном алфавитном порядке. Тем не менее, я рекомендую использовать case , потому что намерение более явное.