GROUP_CONCAT 제한 있음

GROUP_CONCAT 제한 있음

와 식사할 수 있다player-s와 다대다 관계skill-s

목표는 한 번의 질문으로 플레이어와 플레이어의 "Top 3 스킬"을 나열하는 것입니다.

만지작거리다

create table player(
  id int primary key
);

create table skill(
  id int primary key,
  title varchar(100)
);

create table player_skills (
  id int primary key,
  player_id int,
  skill_id int,
  value int
);

쿼리:

SELECT 
p.id,  
group_concat(s.title  SEPARATOR ', ') as skills

FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id

WHERE ps.value > 2
-- skills limit 3 some how ...
group by p.id 
order by s.id


-- expected result
-- player_ID, skills
-- 1 , 'one'
-- 2 , 'one'
-- 3 , 'two, three, four'

바이올린에서 볼 수 있듯이 쿼리 결과에는 3가지 스킬 제한만 누락되어 있습니다.
여러 가지 하위 쿼리를 시도했습니다.가입 등 효과가 없습니다.

어느 정도 확실한 방법 중 하나는 결과를 후처리하는 것입니다.GROUP_CONCAT:

substring_index(group_concat(s.title SEPARATOR ','), ',', 3) as skills

물론 이것은 스킬 이름에 쉼표가 포함되어 있지 않고 그 양이 상당히 적다고 가정합니다.

만지작거리다

의 기능 요구GROUP_CONCAT명시적인 것을 뒷받침하다LIMIT아쉽게도 절은 아직 해결되지 않았습니다.

업데이트: 사용자 Strawberry가 지적한 바와 같이 이 표는player_skills흥분해야 한다(player_id, skill_id)주요 키로, 그렇지 않으면 스키마는 동일한 스킬을 플레이어에 여러 번 할당할 수 있습니다.group_concat예상대로 작동하지 않습니다.

증가하다GROUP_CONCAT함수 길이 사용GLOBAL group_concat_max_len GROUP_CONCAT()최대 길이는 1024자입니다.
할 수 있는 것은, 다음의 설정을 실시하는 것입니다.GLOBAL group_concat_max_lenmysql에서

SET GLOBAL group_concat_max_len = 1000000;

이거 한번 써보면 확실히 효과가 있을 거예요.

훨씬 더 깨끗한 해결책이 있습니다.다른 안에 포장하다SELECT진술.

SELECT GROUP_CONCAT(id) FROM (
    SELECT DISTINCT id FROM people LIMIT 4
) AS ids;

/* Result 134756,134754,134751,134750 */

MariaDB 10.3.3+ 를 사용하고 있는 경우 가능합니다.

GROUP_CONCAT()의 LIMIT 구 지원(MDEV-11297)

SELECT p.id,  
   GROUP_CONCAT(s.title ORDER BY title  SEPARATOR ', ' LIMIT 3) as skills
FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id
WHERE ps.value > 2
GROUP BY p.id 
ORDER BY s.id;

db <> 데모 표시

여기 또 다른 해결책이 있습니다.관계를 해결하기 위한 임의의 메커니즘이 포함되어 있으며, 당신의 스키마와 약간 다른 스키마를 채택하고 있습니다.

SELECT a.player_id
     , GROUP_CONCAT(s.title ORDER BY rank) skills
  FROM
     ( SELECT x.*, COUNT(*) rank
         FROM player_skills x
         JOIN player_skills y 
           ON y.player_id = x.player_id
          AND (y.value > x.value
           OR (y.value = x.value AND y.skill_id <= x.skill_id))
        GROUP 
           BY player_id, value, skill_id
       HAVING COUNT(*) <= 3
     ) a
  JOIN skill s
    ON s.skill_id = a.skill_id
 GROUP 
    BY player_id;

http://sqlfiddle.com/ #!2/34497/18

덧붙여서 프레젠테이션레이어/어플리케이션레벨 코드가 있는 경우는, 거기서 GROUP_CONCAT 의 모든 처리를 고려해 주세요.좀 더 유연해요.

이러한 문제를 해결하려면 앞서 언급한 지침을 따르십시오.

설명 1:그룹 콘시트의 제한을 설정하고 쿼리를 작성합니다.

SET SESSION group_concat_max_len = 1200000;

지침 2: 그러면 주어진 두 가지 예를 따라 솔루션을 찾을 수 있습니다.

예 1:

SELECT GROUP_CONCAT(app_id) AS ids FROM (
      SELECT DISTINCT app_id FROM email_queue 
) AS ids;

예 2:

select GROUP_CONCAT(caption)  from email_queue group BY process_type_id;

주 1: 이것은 example1과 example2의 쿼리를 위한 표 구조입니다.

를 클릭합니다.

주 2: 여기서 1200000은 그룹 콘텍스트 데이터에 대해 쿼리가 최대 1200,000자를 허용함을 의미합니다.

사용자 변수를 사용하여 분할된row_number를 시뮬레이트한 후 행을 제한하여 적용할 수 있습니다.group_concat:

select p.id,
    group_concat(s.title separator ', ') as skills
from player p
left join (
    select distinct ps.player_id,
        ps.skill_id,
        @rn := if(@player_id = player_id, @rn+1, if(@player_id := player_id, 1, 1)) as seqnum
    from player_skills ps
    cross join (select @rn := 0, @player_id := null) x
    where ps.value > 2
    order by player_id, value desc
    ) ps on p.id = ps.player_id and ps.seqnum <= 3
left join skill s on ps.skill_id = s.id
group by p.id;

데모

이 방법에서는 표를 두 번 이상 읽을 필요가 없습니다.

언급URL : https://stackoverflow.com/questions/23608464/group-concat-with-limit