SQL-запрос для выбора записей, который проверяет наличие хотя бы одной допустимой записи с заданными критериями

#sql #sql-server

#sql #sql-сервер

Вопрос:

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

Таблица будет иметь приведенный ниже формат.

 id | start_date | end_date | status | user_id
---------------------------------------------
  

Для этого я должен получить всех пользователей с активным правом доступа. Запрос для того же самого добавлен ниже.

 select *
from users u
  inner join eligibility e on users.id=eligibility.user_id
where e.start_date <= '2019-03-22'
  and e.end_date >= '2019-03-22'
  and e.status<> 'inactive'
  

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

Теперь я должен проверить всех неподходящих пользователей, использующих эти записи.

В основном нам придется выбирать пользователей, у которых нет допустимых записей. Если у пользователя есть хотя бы одна приемлемая запись, то пользователь считается приемлемым.

Примеры данных и ожидаемые результаты добавлены ниже.

Таблица пользователей :-

 id | first_name | last_name
---------------------------
1  |    John    |  Doe
2  |    Sam     |  Ronald
3  |   Alice    |  Wayne
4  |    Dean    |  Marcus
5  |    Bony    |  Ignatius
6  |    Kim     |  Pharm
7  |   Tony     |  Ryan
  

Таблица соответствия :-

 id | start_date | end_date    | status | user_id
-------------------------------------------------
 1    2018-06-23  2018-12-31  | active |   1
 2    2018-06-23  2019-01-30  | active |   1
 3    2018-06-23  2018-12-31  | active |   3
 4    2018-06-23  2019-12-22  | active |   3
 5    2018-06-23  2018-12-31  |inactive|   4
 6    2018-06-23  2019-03-10  | active |   4
 7    2018-06-23  2018-12-31  | active |   5
 8    2018-06-23  2019-12-31  | active |   5
 9    2018-06-23  2018-01-31  | active |   6
 10   2018-06-23  2019-12-24  | active |   6
 11   2018-06-23  2018-12-31  |inactive|   7
 12   2018-06-23  2019-02-22  | active |   7
 13   2018-06-23  2019-12-31  | active |   1
 14   2018-06-23  2019-12-31  | active |   3
  

С этими данными и текущей датой пользователи с Id: 1,3,5 и 6 имеют право.
А пользователи с идентификатором 2 (у них нет записи о приемлемости), 4 и 7 не подходят, если вы посмотрите на данные.

ПРИМЕЧАНИЕ: статус «активный» не означает, что она активна. Мы также должны проверить диапазон дат.

Теперь я хочу знать, как я могу запросить неподходящих пользователей.

Комментарии:

1. Вы используете MySQL или MS SQL Server?

2. Я использую MS SQL Server

3. Затем вы можете удалить тег <mysql> .

4. Я сделал это. Теперь вы можете помочь мне с решением?

5. «Я пробовал некоторые варианты, но ничего не сработало для получения всех неподходящих пользователей». Не могли бы вы опубликовать эти попытки? Почему они не сработали так, как вы ожидали, или не вернули желаемые результаты? Не могли бы вы поделиться некоторыми образцами данных и ожидаемыми результатами, которые вы ищете для этого образца?

Ответ №1:

Это условия для пользователя, имеющего право:

 e.start_date <= '2019-03-22' and e.end_date >= '2019-03-22' and e.status<> 'inactive'
  

Итак, если в таблице нет строки для пользователя eligibility , удовлетворяющей этим условиям, то этот пользователь не подходит.
Вы можете использовать not exists :

 select u.* from users u 
where not exists (
  select 1 from eligibility
  where 
    u.id = user_id 
    and
    start_date <= '2019-03-22' and end_date >= '2019-03-22'
    and 
    status <> 'inactive'
)
  

Комментарии:

1. Спасибо за решение!

Ответ №2:

Вы пробовали between раньше?

 select * from users u
where exists(select 0 from eligible e
where u.user_id = e.user_id and 
'2019-03-22' between e.start_date and e.end_date and
e.status<> 'inactive')
  

Комментарии:

1. Я обновил вопрос данными и ожидаемыми результатами. Пожалуйста, взгляните

Ответ №3:

Что это вам дает:

 select * from users u
LEFT join eligibility e on users.id=eligibility.user_id
where e.user_id is NULL
  

Комментарии:

1. Это не сработает, поскольку для каждой приемлемой записи есть идентификатор пользователя.

2. Я обновил вопрос данными и ожидаемыми результатами. Пожалуйста, взгляните

3. @Sachin, это сработает. я сразу вижу из вашего примера данных, что идентификатор Сэма Рональда отсутствует в таблице соответствия требованиям. Критерии диапазона дат могут быть добавлены обратно, чтобы сузить набор данных.

4. Это один из случаев, когда у пользователя нет соответствующей записи. Но если вы посмотрите на пользователя: Dean Marcus , у него есть две записи в таблице приемлемости, которые недействительны на текущую дату. ie: истекшие права