Запрос LEFT JOIN с применением статистических выражений

@diadlos · Регистрация: 07.12.2016

Author24 — интернет-сервис помощи студентам

Добрый день!

В рамках одной БД имеется две таблицы (проверки и ошибки).

TableA (TableA.ID, TableA.Datechecks, TableA.CheckedFIO, TableA.CheckTypes)
TableB (TableB.ID, TableB.IDA, TableB.Mistake, TableB.MistakeCriticality)

Необходимо подготовить запрос, который бы отображал следующие данные:
ФИО, Кол-во проверок, Общее кол-во ошибок, Кол-во ошибок_Ошибка, Кол-во ошибок_Гр.ошибка, Кол-во проверок без ошибок, кол-во проверок без грубых ошибок.

Подобный запрос смог реализовать только без группировки по ФИО, вот что получилось (уверен, что можно было бы еще проще написать):

SQL

SELECT (SELECT COUNT(*)
 FROM (SELECT DISTINCT TableA.ID FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.IDA)) AS Проверки,
 COUNT(TableB.ID) AS Ошибки,
 SUM(IIf(TableB.MistakeCriticality='Грубая ошибка',1,0)) AS Ошибки_ГРУБАЯ,
 SUM(IIf(TableB.MistakeCriticality='Ошибка',1,0)) AS Ошибки_ОШИБКА,
 (SELECT COUNT(*) FROM (SELECT DISTINCT TableA.ID
                                     FROM TableA
                                     LEFT JOIN TableB
                                     ON TableA.ID = TableB.IDA WHERE TableB.IDA IS NULL)) AS Проверки_без_ошибок,
  (Проверки-(SELECT COUNT(*)  FROM (SELECT DISTINCT TableA.ID
                                                           FROM TableA
                                                           LEFT JOIN TableB ON TableA.ID = TableB.IDA
                                                           WHERE TableB.MistakeCriticality = 'Грубая ошибка'))) AS Проверки_без_Грубых_ошибок
 FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.IDA

При попытке добавить группировку по ФИО, т.е. добавляю поле TableA.CheckedFIO и в конце пишу GROUP BY TableA.CheckedFIO, выдает актуальные данные только по "Ошибки", "Ошибки_ГРУБАЯ", "Ошибки_ОШИБКА", все остальные данные имеют одинаковое значение, т.е. сумму, без разбивки по конкретным ФИО.

Пожалуйста, помогите составить корректный запрос, чтобы все значения в данных отображались с учетом группировки по ФИО.

Заранее спасибо!

@Вячеслав Я · 17.08.2017, 19:22

diadlos, а пример базы можете выложить? Чтобы уж наверняка.

@diadlos · 18.08.2017, 10:48 **[ТС]**

Вячеслав Я, вот тестовая БД.

shanemac51 · 18.08.2017, 11:39

Сообщение от diadlos

Подобный запрос смог реализовать только без группировки по ФИО, вот что получилось (уверен, что можно было бы еще проще написать):

сколько же в запросе уровней вложения под-запросов
не смогла сосчитать

Ох, нелегкая это работа -
Из болота тащить бегемота!

@diadlos · 18.08.2017, 11:50 **[ТС]**

shanemac51, все из-за того, что требуются кол-во уникальных значений, а как я понял в Access вывести кол-во уникальных можно только select count(*) from (select distinct .......).
Ну и как я говорил, можно конечно было сделать его попроще, я так думаю (просто я пока не знаю как

).

mobile · 18.08.2017, 12:08

diadlos, попробуйте запрос

SQL

transform COUNT(*)
SELECT Checks.CheckedFIO, COUNT(*) AS Проверки, Проверки+SUM(Mistake_Criticality="Грубая ошибка") AS БезГрубыхОшибок
FROM Checks INNER JOIN CheckMistakes ON Checks.ID = CheckMistakes.ID_Check
GROUP BY Checks.CheckedFIO
pivot CheckMistakes.Mistake_Criticality

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

@diadlos · 18.08.2017, 13:05 **[ТС]**

mobile, попробовал запрос, но он сразу выдал некорректные значения по полю "Проверки", т.е. он подсчитал кол-во ID с группировкой по ФИО, а нужно кол-во уникальных ID с группировкой по ФИО. У нас же получается, что LEFT JOIN выводит все проверки и ошибки, при этом часть таблицы по проверкам дублируется, если в одной проверке была найдена не одна ошибка, а две и более.

Ниже скрин таблицы с корректными данными, которые должны быть (высчитал вручную

).

ФИО сотрудника	Всего проверок	Всего ошибок	Ошибки _ГРУБАЯ	Ошибки _ОШИБКА	Проверки без ошибок	Проверки без грубых ошибок
Иванов Иван Иванович	5	4	2	2	3	3
Королев Антон Сергеевич	1	3	1	2	0	0
Петров Петр Петрович	7	10	5	5	4	4
Федоров Федр Федорович	7	5	1	4	4	6

mobile · 18.08.2017, 14:26

diadlos, попробуйте вариант

SQL

SELECT CheckedFIO, SUM(vsego) AS ВсегоПроверок, SUM(c) AS ВсегоЗаписей,
SUM(vsego_err) AS ВсегоОшибок,
SUM(err_hard) AS ОшибкиГрубые,
SUM(err_err) AS ОшибкиОшибка,
ВсегоЗаписей-ОшибкиГрубые-ОшибкиОшибка AS ПроверкиБезОшибок,
SUM(not_err_hard) AS ПроверкиБезГрубыхОшибок
FROM
(SELECT Checks.ID, Checks.CheckedFIO, 1 AS vsego,  COUNT(*) AS c,
-SUM(CheckMistakes.Mistake_Criticality IS NOT NULL) AS vsego_err, 
-SUM(Mistake_Criticality="Грубая ошибка") AS err_hard, 
-SUM(Mistake_Criticality="ошибка") AS err_err,
-SUM(CheckMistakes.Mistake_Criticality IS NULL) AS notErr,
iif(err_hard>0,0,1) AS not_err_hard
FROM Checks LEFT JOIN CheckMistakes ON Checks.ID = CheckMistakes.ID_Check
GROUP BY Checks.ID, Checks.CheckedFIO) t
GROUP BY t.CheckedFIO

@diadlos · 18.08.2017, 15:22 **[ТС]**

mobile, он сработал! Огромное спасибо за помощь!!!
Однако у меня возникли вопросы, при разборе этого запроса, не мог бы ты немного пояснить:

1. Почему в команде "SUM(CheckMistakes.Mistake_Criticality IS NOT NULL)" мы используем "-", т.е. почему без "-" выводится отрицательное значение? Да и в других командах, где используем "-".

2. В команде "COUNT(*) AS c" количество чего именно мы считаем? Судя по таблице мы считаем кол-во err_hard, err_err, notErr, not_err_hard. Если это так, то почему у нас там не учитывается vsego_err? Ведь COUNT(*) идет перед все этой комбинацией.

Сори, если вопросы глупые ...

mobile · 18.08.2017, 18:11

Сообщение от diadlos

1. Почему в команде "SUM(CheckMistakes.Mistake_Criticality IS NOT NULL)" мы используем "-", т.е. почему без "-" выводится отрицательное значение? Да и в других командах, где используем "-".

Потому что суммируются логические выражения, которые могут быть только True или False. True в Access=-1, а False=0. Поскольку суммируем только True, то результат отрицательный и перед ним ставится минус.

Сообщение от diadlos

В команде "COUNT(*) AS c" количество чего именно мы считаем?

Считаем общее количество записей в группе по Проверяемому. Для Иванова всего 7 записей, у Петрова 14 и так далее. По Вашему ТЗ этот параметр как бы не нужен, но я добавил для наглядности.

@diadlos · 18.08.2017, 18:51 **[ТС]**

Сообщение от mobile

Считаем общее количество записей в группе по Проверяемому. Для Иванова всего 7 записей, у Петрова 14 и так далее. По Вашему ТЗ этот параметр как бы не нужен, но я добавил для наглядности.

Все, я понял. Потом мы просто минусуем от кол-ва строк те строки, где есть ошибки и грубые ошибки.

В общем еще раз спасибо за помощь!

@Вячеслав Я 2833 / 1375 / 215 Регистрация: 13.05.2011 Сообщений: 4,217
	17.08.2017, 19:22	2
	diadlos, а пример базы можете выложить? Чтобы уж наверняка. 0

shanemac51 Модератор 11956 / 4824 / 779 Регистрация: 07.08.2010 Сообщений: 14,128 Записей в блоге: 4
	18.08.2017, 11:39	4
	Сообщение от diadlos Подобный запрос смог реализовать только без группировки по ФИО, вот что получилось (уверен, что можно было бы еще проще написать): сколько же в запросе уровней вложения под-запросов не смогла сосчитать Ох, нелегкая это работа - Из болота тащить бегемота! 0

@diadlos 1 / 1 / 1 Регистрация: 07.12.2016 Сообщений: 34
	18.08.2017, 11:50 [ТС]	5
	shanemac51, все из-за того, что требуются кол-во уникальных значений, а как я понял в Access вывести кол-во уникальных можно только select count(*) from (select distinct .......). Ну и как я говорил, можно конечно было сделать его попроще, я так думаю (просто я пока не знаю как ). 0

@diadlos 1 / 1 / 1 Регистрация: 07.12.2016 Сообщений: 34
	18.08.2017, 15:22 [ТС]	9
	mobile, он сработал! Огромное спасибо за помощь!!! Однако у меня возникли вопросы, при разборе этого запроса, не мог бы ты немного пояснить: 1. Почему в команде "SUM(CheckMistakes.Mistake_Criticality IS NOT NULL)" мы используем "-", т.е. почему без "-" выводится отрицательное значение? Да и в других командах, где используем "-". 2. В команде "COUNT() AS c" количество чего именно мы считаем? Судя по таблице мы считаем кол-во err_hard, err_err, notErr, not_err_hard. Если это так, то почему у нас там не учитывается vsego_err? Ведь COUNT() идет перед все этой комбинацией. Сори, если вопросы глупые ... 0

mobile 26805 / 14484 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782
	18.08.2017, 18:11	10
	Сообщение от diadlos 1. Почему в команде "SUM(CheckMistakes.Mistake_Criticality IS NOT NULL)" мы используем "-", т.е. почему без "-" выводится отрицательное значение? Да и в других командах, где используем "-". Потому что суммируются логические выражения, которые могут быть только True или False. True в Access=-1, а False=0. Поскольку суммируем только True, то результат отрицательный и перед ним ставится минус. Сообщение от diadlos В команде "COUNT(*) AS c" количество чего именно мы считаем? Считаем общее количество записей в группе по Проверяемому. Для Иванова всего 7 записей, у Петрова 14 и так далее. По Вашему ТЗ этот параметр как бы не нужен, но я добавил для наглядности. 1

@diadlos 1 / 1 / 1 Регистрация: 07.12.2016 Сообщений: 34
	18.08.2017, 18:51 [ТС]	11
	Сообщение от mobile Считаем общее количество записей в группе по Проверяемому. Для Иванова всего 7 записей, у Петрова 14 и так далее. По Вашему ТЗ этот параметр как бы не нужен, но я добавил для наглядности. Все, я понял. Потом мы просто минусуем от кол-ва строк те строки, где есть ошибки и грубые ошибки. В общем еще раз спасибо за помощь! 0

Опции темы

Запрос LEFT JOIN с применением статистических выражений

Решение