다른 행에 다른 조건을 충족하는 값을 선택?

이 .... 내가 알아낼 수있는 매우 기본적인 쿼리입니다

이제 나는이 같은 두 개의 열 테이블이 있다고 가정 해 봅시다 :

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

나는이 어떻게해야합니까 1. 나만 반환하려는 대한 결과는 사용자 ID이며, 위의 예를 사용하여 roleids 1, 2, 3을 모두 별개의 사용자 ID를 얻으려면?

해결법

==============================
1.
```
SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;
```
이 글을 읽는 사람에게 내 대답은 간단하고 직관적이며, '수용'상태를 얻었으나, @cletus에 의해 주어진 답을 읽어 가서 마십시오. 그것은 훨씬 더 나은 성능을 가지고있다.

저스틴 큰 소리로 생각하고, 기록하는 또 다른 방법 @cletus 설명 자체가 조인입니다 :
```
SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);
```
이것은 쉽게 읽을 수 있습니다, 그리고 MySQL은 같은 튜플의 비교를 지원합니다. MySQL은이 쿼리에 대해 지능적으로 인덱스를 포함하는 활용하는 방법을 알고 있습니다. 그냥 실행을 통해 설명하고이 인덱스를 읽는 것, 심지어 데이터 행을 터치하지 않는 방법 세 가지 테이블의 노트에서 "인덱스 사용"을 참조하십시오.

내 맥북에서 MySQL 5.1.48을 사용하여 210 만 행 (PostTags에 대한 스택 오버플로 7 월 데이터 덤프) 이상이 쿼리를 실행, 그것은 1.08 초에 결과를 반환했습니다. innodb_buffer_pool_size에 할당 된 충분한 메모리와 괜찮은 서버에서, 그것은 더 빨리해야한다.
==============================
2.나는 그것을 테스트하기로 결정 그래서 좋아, 나는이에을 downvoted있어 :

나는 그것을 테스트하기로 결정 그래서 좋아, 나는이에을 downvoted있어 :
```
CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);
```
이 프로그램을 실행 :
```
<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records 

$start = microtime(true);

echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";

function query($str) {
    mysql_query($str);
    if (mysql_error()) {
        echo "$str: " . mysql_error() . "\n";
    }
}
?>
```
산출:
```
499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.
```
즉 50 만 무작위로 사용자 역할 조합을 추가하고 선택 기준과 일치하는 25,000 약이 있습니다.

첫 번째 쿼리 :
```
SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3
```
쿼리 시간 : 0.312s
```
SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1
```
쿼리 시간 : 0.016s

맞습니다. 내가 제안 된 버전이 20 배 빠르게 집계 버전보다 가입 할 수 있습니다.

미안하지만 난 현실 세계에서의 생활과 일을하고 실제 우리 테스트 SQL에서이 작업을 수행하고 그 결과 자체에 대한 이야기.

그 이유는 아주 명확해야한다. 집계 쿼리는 테이블의 크기와 비용을 확장합니다. 각 행은, 집계 처리 HAVING 절 통해 (또는 생략) 필터링된다. (가) 소정의 역할에 기초하여 상기 사용자의 서브 세트를 선택 (인덱스를 사용하여) 할 버전에 가입 한 후 제 역할에 대해 그 일부를 확인하고 최종적으로 제 역할에 대해 서브셋있다. (관계형 대수 기준) 각각의 선택은 점점 더 작은 하위 집합에서 작동합니다. 이것에서 당신은 결론을 내릴 수있다 :

의 성능은 버전이 일치 낮은 발생률도 더 좋아진다 가입.

세 가지 언급 역할을했다 (위의 50 만 샘플 점 만점) 500 사용자가 있었다 경우, 버전이 훨씬 빠르게 얻을 것이다 가입 할 수 있습니다. 집계 버전은하지 않습니다 (및 성능 향상이 버전은 분명히 너무 얻을에 가입하는 대신 25K의 500 사용자를 운반의 결과이다).

난 진짜 데이터베이스 (즉, 오라클)이 처리 할 방법을 볼 수도 궁금했다. 나는 기본적으로 오라클 XE에서 같은 운동을 반복 그래서 (앞의 예에서 MySQL은 같은 윈도우 XP 데스크탑 컴퓨터에서 실행되는) 그 결과는 거의 동일하다.

조인은 눈살을 찌푸리게 것 같다하지만 입증 한대로 집계 쿼리 속도가 느린 크기 순서가 될 수 있습니다.

업데이트 : 일부 광범위한 테스트 후, 사진이 더 복잡하고 대답은 데이터, 데이터베이스 및 기타 요인에 따라 달라집니다. 이야기의 교훈은 테스트, 테스트, 테스트입니다.
==============================
3.이 작업을 수행하는 고전적인 방법은 관계형 분할 문제로 취급하는 것입니다.

이 작업을 수행하는 고전적인 방법은 관계형 분할 문제로 취급하는 것입니다.

영어로 : 원하는 역할 ID 값 중 어느 것도이 누락되지 누구를 위해 해당 사용자를 선택합니다.

난 당신이 UserRole 테이블을 참조하는하는 사용자 테이블을 가정합니다, 내가 원하는 역할 ID 값이 테이블에있는 가정합니다 :
```
create table RoleGroup(
  roleid int not null,
  primary key(roleid)
)
insert into RoleGroup values (1);
insert into RoleGroup values (2);
insert into RoleGroup values (3);
```
나 또한 그렇게 IN과 놀라움이없는 또는 NOT EXISTS, 모든 관련 컬럼이 널 (NULL) 입력하지 않은 가정합니다. 여기에 위의 영어 표현하는 SQL 쿼리입니다 :
```
select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where not exists (
    select R.roleid from UserRole as R
    where R.roleid = G.roleid
    and R.userid = U.userid
  )
);
```
를 작성하는 또 다른 방법은 이것이다
```
select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where G.roleid not in (
    select R.roleid from UserRole as R
    where R.userid = U.userid
  )
);
```
이 또는 "관계 부문"에 대한 웹 검색, 효율적인되는 등 인덱스, 플랫폼, 데이터에 따라 결국하지 않을 수 있으며, 당신이 많이 찾을 수 있습니다.
==============================
4.사용자 ID를 가정 역할 ID는 고유 한 인덱스에 포함 된 (2 개 기록이 없을 수 있음을 의미 여기서 아이디 = x 및 역할 ID = 1

사용자 ID를 가정 역할 ID는 고유 한 인덱스에 포함 된 (2 개 기록이 없을 수 있음을 의미 여기서 아이디 = x 및 역할 ID = 1
```
select count(*), userid from t
where roleid in (1,2,3)
group by userid
having count(*) = 3
```
==============================
5.
```
select userid from userrole where userid = 1
intersect
select userid from userrole where userid = 2
intersect
select userid from userrole where userid = 3
```
이 문제를 해결하지 않을까요? 이것은 일반적인 관계형 DB를에 어떻게 좋은 해결책이다? 이 자동 최적화 최적화 쿼리 것인가?
==============================
6.여기 일반성의 모든 종류의 (다른 3 역할 조합 또는 다른 N-역할 조합)이 필요한 경우 ... 나는 당신이 당신의 역할에 대한 비트 마스킹 시스템을 사용하여 쿼리를 수행하기 위해 비트 연산자를 사용하는 것이 좋습니다 것 ...

여기 일반성의 모든 종류의 (다른 3 역할 조합 또는 다른 N-역할 조합)이 필요한 경우 ... 나는 당신이 당신의 역할에 대한 비트 마스킹 시스템을 사용하여 쿼리를 수행하기 위해 비트 연산자를 사용하는 것이 좋습니다 것 ...

from https://stackoverflow.com/questions/477006/select-values-that-meet-different-conditions-on-different-rows by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 엔티티 LINQ는 방법 '선택 System.String ToString () 메소드를 인식하지 않고,이 방법은 저장 식으로 변환 될 수 없다 (0)	2020.03.14
[SQL] 열 수가 다른 두 개의 테이블을 Unioning (0)	2020.03.14
[SQL] LIMIT이 적용되기 전에 가장 좋은 방법은 결과 수를 얻을 수 있습니다 (0)	2020.03.14
[SQL] 어떻게 SQL 열 이름을 처리하는 것을 SQL 키워드처럼? (0)	2020.03.14
[SQL] 합니까 PostgreSQL을 지원 "악센트를 구분"정렬? (0)	2020.03.14

복붙노트

[SQL] 다른 행에 다른 조건을 충족하는 값을 선택?

다른 행에 다른 조건을 충족하는 값을 선택?

해결법

1.

2.나는 그것을 테스트하기로 결정 그래서 좋아, 나는이에을 downvoted있어 :

3.이 작업을 수행하는 고전적인 방법은 관계형 분할 문제로 취급하는 것입니다.

4.사용자 ID를 가정 역할 ID는 고유 한 인덱스에 포함 된 (2 개 기록이 없을 수 있음을 의미 여기서 아이디 = x 및 역할 ID = 1

5.

6.여기 일반성의 모든 종류의 (다른 3 역할 조합 또는 다른 N-역할 조합)이 필요한 경우 ... 나는 당신이 당신의 역할에 대한 비트 마스킹 시스템을 사용하여 쿼리를 수행하기 위해 비트 연산자를 사용하는 것이 좋습니다 것 ...

'SQL' 카테고리의 다른 글

티스토리툴바