여러 열 중복의 발생을 기준으로 표시 및 그룹화하는 SQL

여러 열 중복의 발생을 기준으로 표시 및 그룹화하는 SQL

이렇게 생겼지만 행이 많은 테이블이 있습니다.

+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+
| systemid  | colcitrixid | colleague  |  time |   date    |  week |  period |  year  |  usern  |
| 32855671  |   nameu02   |  somename  | 10:12 | 2019-11-01|   23  |    5    |  2019  | othname |
|
|
|   ...     |     ...     |    ...     |  ...  |    ...    |  ...  |   ...   |  ...   |   ...   |
+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+

(이 스택 오버플로 편집기 너비에 적합하도록 열 이름이 요약되고 일부 누락되었습니다.)

systemid, colcitrixid, date가 모두 동일한 중복 행 목록을 얻기 위해 쿼리를 실행하여 작업할 다른 테이블에 넣을 수 있도록 하고 싶습니다.

지금까지 제가 한 것은 다음과 같습니다.

SELECT
    systemid,
    colleaguecitrixid,
    colleague,
    TIME,
    DATE,
    WEEK,
    period,
    fincyear,
    username,
    COUNT(systemid) AS mids,
    COUNT(colleaguecitrixid) AS colleagues,
    GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS ids
FROM
    table_name_2019
WHERE
    period = '9' AND calltype = 'Advice' AND enabled = '1' AND isarchived = '0' AND DATE <> '$todaysdate'
GROUP BY
    systemid,
    colleague,
    DATE
HAVING
    COUNT(systemid) > 1 AND COUNT(colleaguecitrixid) > 1

거의 작동하지만, 다른 동료 citrixid 항목이 포함된 중복 항목을 보여주기 때문에 무언가를 놓치고 있습니다.원래 테이블의 이 행들을 바탕으로 이렇게 결과를 만들어 내고 있습니다.

+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+
| systemid  | colcitrixid | colleague  |  time |   date    |  week |  period |  year  |  usern  |
| 32852671  |   nameu02   |  Jack      | 10:12 | 2019-11-01|   23  |    5    |  2019  | othname |
| 32852671  |   nameu02   |  Jack      | 10:14 | 2019-11-01|   23  |    5    |  2019  | name5   |
| 32852671  |   nameu09   |  James     | 10:14 | 2019-11-01|   23  |    5    |  2019  | name5   |
| 34855673  |   nameu05   |  Bob       | 11:18 | 2019-11-03|   23  |    5    |  2019  | name2   |
| 34855673  |   nameu05   |  Bob       | 11:18 | 2019-11-03|   23  |    5    |  2019  | othname |
| 32851672  |   nameu08   |  Sarah     | 13:17 | 2019-11-01|   23  |    5    |  2019  | name2   |
| 32851672  |   nameu08   |  Sarah     | 13:19 | 2019-11-01|   23  |    5    |  2019  | name3   |
| 32851672  |   nameu08   |  Sarah     | 13:21 | 2019-11-01|   23  |    5    |  2019  | name1   |
| 32855671  |   nameu06   |  Jim       | 10:19 | 2019-11-01|   23  |    5    |  2019  | othname |
| 32855671  |   nameu06   |  Jim       | 10:22 | 2019-11-01|   23  |    5    |  2019  | othname |
|   ...     |     ...     |    ...     |  ...  |    ...    |  ...  |   ...   |  ...   |   ...   |
+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+

(참고, 3행, 다른 동료인 제임스를 볼 수 있지만 시스템 ID는 잭과 같은 위의 행과 동일합니다.)

하지만 systemid와 ceoper citrixid와 date가 중복된 것만 포함하고 싶습니다. 이렇게 그룹화되어 있습니다.

+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+
| systemid  | colcitrixid | colleague  |  time |   date    |  week |  period |  year  |  usern  |
| 32852671  |   nameu02   |  Jack      | 10:12 | 2019-11-01|   23  |    5    |  2019  | othname |
| 32852671  |   nameu02   |  Jack      | 10:14 | 2019-11-01|   23  |    5    |  2019  | name5   |
| 34855673  |   nameu05   |  Bob       | 11:18 | 2019-11-03|   23  |    5    |  2019  | name2   |
| 34855673  |   nameu05   |  Bob       | 11:18 | 2019-11-03|   23  |    5    |  2019  | othname |
| 32851672  |   nameu08   |  Sarah     | 13:17 | 2019-11-01|   23  |    5    |  2019  | name2   |
| 32851672  |   nameu08   |  Sarah     | 13:19 | 2019-11-01|   23  |    5    |  2019  | name3   |
| 32851672  |   nameu08   |  Sarah     | 13:21 | 2019-11-01|   23  |    5    |  2019  | name1   |
| 32855671  |   nameu06   |  Jim       | 10:19 | 2019-11-01|   23  |    5    |  2019  | othname |
| 32855671  |   nameu06   |  Jim       | 10:22 | 2019-11-01|   23  |    5    |  2019  | othname |
|   ...     |     ...     |    ...     |  ...  |    ...    |  ...  |   ...   |  ...   |   ...   |
+-----------+-------------+------------+-------+-----------+-------+---------+--------+---------+

(결과가 위에서 그린 것처럼 보이는 원래 테이블의 행을 보여주고 있습니다. 제 쿼리 결과가 위와 같이 형식이 지정되지 않은 것으로 알고 있습니다. 나중에 단계를 위해 쿼리의 다른 부분과 형식이 필요합니다.)

systemid, colcitrixid와 날짜는 모두 같습니다.

쿼리는 다음과 같이 표시되어야 합니다.

SELECT systemid, colcitrixid, date, COUNT(*),
       GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS ids
FROM table_name_2019
WHERE period = '9' AND
      calltype = 'Advice' AND
      enabled = '1' AND isarchived = '0' AND
      DATE <> CURDATE()
GROUP BY systemid, colcitrixid, date
HAVING COUNT(*) > 1;

안 보여…colcitrixid데이터상에당신 말이 진짜라면colleaguecitrixid, 그럼 그걸로 해요.

참고:

• 집계 쿼리에서 모든 열은GROUP BY집계 열에 대한 인수여야 합니다.
• MySQL에는 날짜를 반환하는 기능이 있습니다.대부분의 상황에서 그 가치를 전달할 필요가 없습니다.
• 그DISTINCT인에GROUP_CONCAT()불필요할 수도 있습니다라는 칼럼.id중복되지 않아야 합니다.

DB의 버전이 10.2+인 경우 다음을 사용할 것을 고려합니다.

COUNT(colleaguecitrixid) OVER (PARTITION BY systemid, colleague, date)분석 함수:

SELECT systemid,
       colleaguecitrixid,
       colleague,
       time,
       date,
       week,
       period,
       fincyear,
       username,
       mids,
       colleagues,
       ids 
  FROM
  (
   SELECT t.*,      
          COUNT(systemid) AS mids,
          COUNT(colleaguecitrixid) OVER (PARTITION BY systemid, colleague, date) AS colleagues,
          GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS ids
     FROM table_name_2019 t
    WHERE period = '9' 
      AND calltype = 'Advice' 
      AND enabled = '1' 
      AND isarchived = '0' 
      AND DATE <> '$todaysdate'
  ) t1  
  WHERE colleagues > 1

언급URL : https://stackoverflow.com/questions/59344296/sql-to-show-and-group-by-occurrences-of-multiple-column-duplicates