반응형
3개의 다른 SELECT 쿼리를 통합하여 3개의 다른 카테고리에서 각각 10개의 값을 가져와 하나의 임시 테이블에 삽입하는 방법
이 있습니다.latest_comments테이블은 N분마다 다시 작성됩니다.내용물은 에서 추출한 것입니다.posts그리고.comments테이블과 통합됩니다.3가지 카테고리(빨간색, 녹색, 파란색)가 있으며 각 카테고리의 최신 코멘트는 10개씩 표시됩니다.latest_comments.
쿼리를 통합하거나 병합하지 않으면 다음과 같이 됩니다.
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10;
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'green'
ORDER BY c.date DESC
LIMIT 0, 10;
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'blue'
ORDER BY c.date DESC
LIMIT 0, 10;
3개의 SELECT 쿼리를 모두 병합하여 성능에 대해 최대한 최적화된 상태로 1개의 INSERT 쿼리만 사용할 수 있도록 하려면 어떻게 해야 합니까?
나는 내가 찾을 수 있는 모든 것을 시도했고 내 논리를 생각해내기 위해 할 수 있는 모든 것을 시도했다.어느 것도 동작하지 않고, 3개의 INSERT 쿼리로 기능이 저하되고 있는 것 같습니다.예를 들어 다음과 같은 것이 도움이 됩니다.
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10),
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'green'
ORDER BY c.date DESC
LIMIT 0, 10),
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'blue'
ORDER BY c.date DESC
LIMIT 0, 10);
eacj 쿼리에서 10 행을 원하는 경우 UNION ALL을 사용할 수 있습니다.
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10)
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id WHERE p.category = 'green'
ORDER BY c.date DESC LIMIT 0, 10 )
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id WHERE p.category = 'blue'
ORDER BY c.date DESC LIMIT 0, 10)
어쨌든 왼쪽 조인 테이블 열을 사용하고 있습니다.WHERE p.category = 'blue'여기서 이 조항은 내부 결합으로 작용한다.
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
INNER JOIN `posts` AS p ON c.post_id = p.id and p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10)
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
INNER JOIN `posts` AS p ON c.post_id = p.id AND p.category = 'green'
ORDER BY c.date DESC LIMIT 0, 10)
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
INNER JOIN `posts` AS p ON c.post_id = p.id AND p.category = 'blue'
ORDER BY c.date DESC LIMIT 0, 10);
또는 30개의 행이 필요한 경우 IN 절을 사용할 수 있습니다.
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
INNER JOIN `posts` AS p ON c.post_id = p.id and p.category IN ( 'red', 'green', 'blue')
ORDER BY c.date DESC
LIMIT 0, 30;
INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10)
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'green'
ORDER BY c.date DESC
LIMIT 0, 10)
UNION ALL
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'blue'
ORDER BY c.date DESC
LIMIT 0, 10)
언급URL : https://stackoverflow.com/questions/52274592/how-to-unify-3-different-select-queries-so-they-can-be-used-to-pull-10-values-e
반응형
'itsource' 카테고리의 다른 글
| PHP에서 NULL을 확인합니다. (0) | 2022.09.26 |
|---|---|
| MySQL/MariaDB는 정확히 1분 42초 후에 셧다운됩니다. (0) | 2022.09.26 |
| MySQL에 양수/음수 기호 저장 (0) | 2022.09.26 |
| Eclipse에서 프로젝트를 JAR로 자동 빌드 (0) | 2022.09.26 |
| "short 30 = 3 * 10"이 법적 할당인 이유는 무엇입니까? (0) | 2022.09.26 |