날짜와 함께 GROUP_CONCAT에서 NOT IN 연산자(<>)를 사용하는 방법

날짜와 함께 GROUP_CONCAT에서 NOT IN 연산자(<>)를 사용하는 방법

2021년 12월의 모든 날짜가 포함된 달력표가 있습니다(표 안에 몇 가지 날짜만 표시하지만 실제로는 같은 달의 모든 날짜가 포함되어 있는 것으로 알고 있습니다).

아이디	날짜.
01	2021-12-01
02	2021-12-02
03	2021-12-03
04	2021-12-04
05	2021-12-05

사용자 테이블이 있습니다.

아이디	이름.	num_module
01	앤드류	101
02	메리	102

테이블 어시스턴스가 있어요.

아이디	날짜.	num_module
01	2021-12-03	101
02	2021-12-04	101
03	2021-12-03	102
04	2021-12-04	102
05	2021-12-05	101
06	2021-12-06	102

저는 사원번호, 사원명, 출석일, 결근일 등을 표시하기 위한 조회를 실시했습니다.

SELECT u.num_employee,
       u.name,
       a.date AS attendances,
       c.date as faults FROM users u
JOIN (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.not_employee = u.num_employee
LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31') c ON c.date <> a.date

위의 질문에서는 다음과 같이 표시됩니다.

num_module	이름.	서포트	결함
101	앤드류	3,4,5	1,2,3,4,5,6,7,8,9,10...
102	메리	3,4,6	1,2,3,4,5,6,7,8,9,10...

출석란에는 각 종업원이 출석한 12월의 일수를 기입하고, 폴트에는 결석한 일수만을 기입합니다만, 12월의 일수는 모두 기재되어 있습니다.

attends 란에 표시되는 일수가 attends 란에 표시되지 않는 것이 문제인 것은 거의 확실합니다.특히 이 부분에서는 평가가 잘못되었다고 생각합니다.

ON c.date <> a.date

제가 지금 이 일을 하고 있기 때문에GROUP_CONCAT날짜를 다르게 평가해야 합니다.다음 정보를 얻으려면 어떻게 해야 합니까?

하지 않다	이름.	어텐던트	결함
101	앤드류	3,4,5	1,2,3,6,7,8,9,10...
102	메리	3,4,6	1,2,5,7,8,9,10...

사용 중인 MariaDB 버전을 지정하면 해당 쿼리를 CTE를 사용하도록 조정할 수 없습니다.phpMyAdmin에서 작업하고 있습니다.

하나의 솔루션은 하위 선택입니다.

이것은 mysql 5에서도 동작하며 mysql 8에서는 출석으로부터 CTE를 작성할 수 있습니다.

CREATE TABLE calendar (
  `ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO calendar
  (`ID`, `date`)
VALUES
  ('01', '2021-12-01'),
  ('02', '2021-12-02'),
  ('03', '2021-12-03'),
  ('04', '2021-12-04'),
  ('05', '2021-12-05'),
  ('06', '2021-12-06'),
  ('07', '2021-12-07'),
  ('08', '2021-12-08'),
  ('09', '2021-12-09'),
  ('10', '2021-12-10'),
  ('11', '2021-12-11');

CREATE TABLE users (
  `ID` INTEGER,
  `name` VARCHAR(6),
  `num_employee` INTEGER
);

INSERT INTO users
  (`ID`, `name`, `num_employee`)
VALUES
  ('01', 'Andrew', '101'),
  ('02', 'Mary', '102');

CREATE TABLE attendances (
  `ID` INTEGER,
  `date` VARCHAR(10),
  `num_employee` INTEGER
);

INSERT INTO attendances
  (`ID`, `date`, `num_employee`)
VALUES
  ('01', '2021-12-03', '101'),
  ('02', '2021-12-04', '101'),
  ('03', '2021-12-03', '102'),
  ('04', '2021-12-04', '102'),
  ('05', '2021-12-05', '101'),
  ('06', '2021-12-06', '102');

SELECT u.num_employee,
       u.name,
       a.date AS attendances,
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),a.date)) as faults FROM users u
JOIN (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.num_employee = u.num_employee

num_module | name | attendants | 장애-----------: | :----- | :---------- | :----------------101 | Andrew | 3,4,5 | 1,2,6,7,8,9,10,11102 | Mary | 3,4,6 | 1,2,5,7,8,9,10,11

db <>여기에 추가

직원과 날짜의 가능한 모든 쌍별 일치에 대해 사용자와 달력 테이블의 교차 결합을 고려합니다.그런 다음 지원에 왼쪽 참여하여 Aggregate 실행GROUP_CONCAT장애에 대한 하나의 조건부 식:

SELECT u.num_employee,
       u.name,
       GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM a.date)) AS attendances,
       GROUP_CONCAT(DISTINCT 
           IF(a.date IS NULL, EXTRACT(DAY FROM c.date), NULL)
       ) AS faults 
FROM calendar c
INNER JOIN users u
  ON c.date BETWEEN '2021-12-01' AND '2021-12-31'
LEFT JOIN attendances a
   ON c.date = a.date
   AND u.num_employee = a.num_employee
   AND a.date BETWEEN '2021-12-01' AND '2021-12-31'
GROUP BY u.num_employee,
         u.name;

Online Demo

언급URL : https://stackoverflow.com/questions/71259515/how-to-use-the-not-in-operator-in-a-group-concat-with-dates