MySQL 5.7.15를 사용하고 있습니다.
특정 위치 근처의 where 절과 일치하는 모든 비즈니스 목록을 얻으려고합니다. 각 비즈니스에 대해 가장 좋아하는 사진을 가져와야합니다. 비즈니스가 1이 아닌 플랜 유형이있는 경우 가장 많이 좋아하는 사진이 필요합니다. 여기서 most_liked_picture.business_picture의 값이 1 인 경우, 그렇지 않으면 가장 좋아하는 사진이 필요합니다. 이를 위해 모든 비즈니스를 제공하는 외부 쿼리와 하위 쿼리가있는 내부 쿼리를 만들어 order by 절을 충족하는 가장 좋아하는 사진을 제공했습니다.
내부 쿼리 중 두 개가 원하는 결과를 생성하지 않습니다. 일부 행에 대한 결과를 제공하지만 전부는 아니지만 매우 이상합니다. 지금은 내부 쿼리의 결과를 외부 쿼리 당 하나로 제한하지 않았습니다. 내부 쿼리 하위가 올바른 결과를 생성 할 수 없기 때문입니다.
하위 쿼리를 분리하면 올바른 결과를 얻을 수 있으므로 쿼리가 좋은지 알 수 있습니다.이 쿼리를 결합하는 방식이어야합니까?
나는 SQL로 스스로 배웠으므로 표준이 아니거나 이상한 것을 본다면 부드럽게하십시오.
다음은 테이블 구조와 데이터입니다.
businesses
+----+----------------------------+-----------+------------+----------------|
| id | name | lat | lng | point_location |
+----+----------------------------+-----------+------------+----------------|
| 1 | test_business_1 | 28.418908 | -81.586254 | POINT(lng,lat) |
| 2 | Sea_World | 32.764800 | 117.226600 | POINT(lng,lat) |
| 3 | Disneyland | 33.812100 | 117.919000 | POINT(lng,lat) |
| 4 | Disney World | 28.417839 | -81.581235 | POINT(lng,lat) |
| 5 | business near Disney World | 28.408642 | -81.572607 | POINT(lng,lat) |
+----+----------------------------+-----------+------------+----------------|
business_plans
+----+--------------+-------------+---------+------------+------------+
| id | plan_type_id | business_id | user_id | start_date | end_date |
+----+--------------+-------------+---------+------------+------------+
| 1 | 1 | 1 | 1 | 2015-01-01 | 2015-12-31 |
| 2 | 2 | 1 | 2 | 2016-01-01 | 2016-12-31 |
| 3 | 2 | 2 | 2 | 2016-01-01 | 2016-12-31 |
| 4 | 1 | 3 | 1 | 2016-01-01 | 2016-12-31 |
| 5 | 2 | 4 | 1 | 2016-01-01 | 0000-00-00 |
+----+--------------+-------------+---------+------------+------------+
pictures
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+
| id | business_id | user_id | image_path | title | lat | lng | point_location | lifetime_likes | lifetime_dislikes | month_likes | month_dislikes | business_picture | business_main_picture | business_icon | effective_date | expire_date | updated_at | created_at |
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+
| 1 | 2 | 1 | sea world image_path_1 | sea world logo | 32.764800 | 117.226600 | POINT(lng,lat) | 5 | 5 | 5 | 5 | 1 | 0 | 1 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 2 | 3 | 0 | disney_image_path_2 | disney main picture | 33.812100 | 117.919000 | POINT(lng,lat) | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 3 | 3 | 2 | disney user uploaded pic | NULL | 33.812100 | 117.919000 | POINT(lng,lat) | 5 | 5 | 5 | 5 | 0 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 4 | 3 | 0 | disney expired pic | disney expired pic | 33.812100 | 117.919000 | POINT(lng,lat) | 20 | 0 | 20 | 0 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 5 | 3 | 2 | disney_highest_points | disney highest points | 33.812100 | 117.919000 | POINT(lng,lat) | 10 | 2 | 10 | 2 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 6 | 4 | 1 | disneyworld_highest_business_pic | disneyworld_highest_business_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 20 | 1 | 20 | 1 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-22 22:40:50 | 2016-01-01 00:00:00 |
| 7 | 4 | 1 | disneyworld_highest_user_point_pic | disneyworld_highest_user_point_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 45 | 1 | 45 | 1 | 0 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-22 22:36:46 | 2016-01-01 00:00:00 |
| 8 | 5 | 2 | near_disneyworld_highest_business_pic | near_disneyworld_highest_business_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 20 | 1 | 20 | 1 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-23 00:08:20 | 2016-01-01 00:00:00 |
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+
users
+----+-----------+--------------------+
| id | username | picture_path |
+----+-----------+--------------------+
| 1 | test | user1_picture_Path |
| 2 | username2 | |
| 3 | username3 | |
| 4 | username5 | |
| 5 | username5 | |
| 6 | username6 | |
| 7 | username7 | NULL |
+----+-----------+--------------------+
user_picture_swipe
+----+------------+-------------+---------+-------+
| id | picture_id | business_id | user_id | liked |
+----+------------+-------------+---------+-------+
| 1 | 1 | 2 | 2 | 0 |
| 2 | 2 | 3 | 2 | 1 |
| 3 | 2 | 3 | 1 | 1 |
| 4 | 3 | 3 | 1 | 1 |
| 5 | 4 | 3 | 1 | 0 |
| 6 | 7 | 4 | 1 | 1 |
| 7 | 6 | 4 | 1 | 0 |
| 9 | 8 | 5 | 2 | 1 |
+----+------------+-------------+---------+-------+
다음은 쿼리입니다.
SELECT businesses.id AS business_id, businesses.name AS business_name,
most_liked_picture.business_plan_type_id, most_liked_picture.picture_id,
businesses.lat, businesses.lng, most_liked_picture.image_path,
most_liked_picture.picture_title, most_liked_picture.lifetime_likes,
most_liked_picture.business_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839)) AS
distance_from_user,
most_liked_picture.uploaded_username,
most_liked_picture.uploaded_user_image_path,
most_liked_picture.user_liked_picture
FROM businesses LEFT JOIN
(SELECT most_liked_picture.id AS picture_id, businesses.id AS business_id,
businesses.name AS business_name,
current_business_plan.plan_type_id AS business_plan_type_id,
businesses.lat, businesses.lng, most_liked_picture.image_path,
title AS picture_title, most_liked_picture.lifetime_likes,
business_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235,
28.417839)) AS distance_from_user,
CASE business_picture
WHEN 0 THEN user_uploaded_picture.username
ELSE ""
END AS uploaded_username,
CASE business_picture
WHEN 0 THEN user_uploaded_picture.picture_path
ELSE ""
END AS uploaded_user_image_path,
IFNULL(current_user_liked_picture, NULL) AS user_liked_picture
FROM users AS user_uploaded_picture RIGHT JOIN (
(SELECT business_id, plan_type_id
FROM business_plans
WHERE (CURRENT_DATE() BETWEEN start_date AND end_date) OR
((end_date IS NULL) AND (CURRENT_DATE >= start_date))
) AS current_business_plan
RIGHT JOIN (businesses LEFT JOIN (
(SELECT picture_id, liked AS current_user_liked_picture
FROM user_picture_swipes
WHERE (user_id = 1)
) AS user_picture_swipe
RIGHT JOIN pictures AS most_liked_picture
ON user_picture_swipe.picture_id = most_liked_picture.id)
ON businesses.id = most_liked_picture.business_id)
ON current_business_plan.business_id = businesses.id)
ON user_uploaded_picture.id = most_liked_picture.user_id
WHERE ST_Within(businesses.point_location,
envelope(linestring(POINT(-81.581235 - 5 /
abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)),
POINT(-81.581235 + 5 / abs(cos(radians(28.417839)) * 69),28.417839 +
(5 / 69)))))
ORDER BY
IF(IFNULL(current_business_plan.plan_type_id, 1) != 1,
IFNULL(most_liked_picture.business_picture, 0), 0) DESC,
lifetime_likes DESC
) AS most_liked_picture ON businesses.id = most_liked_picture.business_id
WHERE ST_Within(businesses.point_location, envelope(linestring(POINT(-81.581235
- 5 / abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)),POINT(-81.581235
+ 5 / abs(cos(radians(28.417839)) * 69),28.417839 + (5 / 69)))))
ORDER BY ST_Distance_Sphere(businesses.point_location, POINT(-81.581235,
28.417839));
내가 얻은 결과는 다음과 같습니다 (페이지에 맞게 단축).
+-------------+--------------+--------------+-------+----------+---------------+
| business_id | name | plan_type_id | likes | username | liked_picture |
+-------------+--------------+--------------+-------+----------+---------------+
| 4 | Disney World | NULL | 20 | | 0 |
| 4 | Disney World | NULL | 45 | test | 1 |
| 1 | test_business| 2 | | NULL | NULL |
| 5 | business near| NULL | 20 | | NULL |
+-------------+--------------+--------------+-------+----------+---------------+
내가 얻어야하는 결과 :
+-------------+--------------+--------------+-------+----------+---------------+
| business_id | name | plan_type_id | likes | username | liked_picture |
+-------------+--------------+--------------+-------+----------+---------------+
| 4 | Disney World | 2 | 20 | test | 0 |
| 4 | Disney World | 2 | 45 | test | 1 |
| 1 | test_business| 2 | | NULL | NULL |
| 5 | business near| NULL | 20 | username2| 1 |
+-------------+--------------+--------------+-------+----------+---------------+
이 게시물이 너무 깁니다. 도움을 주시면 감사하겠습니다.
******************************* 쿼리 업데이트 **************** ***************
나는 마침내 그것을 알아 냈습니다! 외부 쿼리가 범인이었습니다. 일단 제거되면 원하는 결과를 얻었습니다. 다음은 최종 쿼리입니다 (오른쪽 조인 및 from에 블록 사용 없음). 올바른 방향으로 나를 가리키는 Used_By_Already에게 감사드립니다.
SELECT businesses.id AS business_id, businesses.name AS business_name,
current_business_plan.plan_type_id AS business_plan_type_id, businesses.lat, businesses.lng,
most_liked_picture.id AS picture_id, most_liked_picture.image_path, title AS picture_title,
most_liked_picture.lifetime_likes, business_picture, picture_uploaded_user.username AS uploaded_username,
picture_uploaded_user.picture_path AS uploaded_user_image_path,
user_picture_swipe.current_user_liked_picture AS user_liked_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839)) AS distance_from_user
FROM
(SELECT business_id, plan_type_id
FROM business_plans
WHERE (CURRENT_DATE() BETWEEN start_date AND end_date) OR ((end_date IS NULL) AND
(CURRENT_DATE >= start_date))) AS current_business_plan
INNER JOIN businesses ON current_business_plan.business_id = businesses.id
LEFT JOIN pictures AS most_liked_picture ON businesses.id = most_liked_picture.business_id
LEFT JOIN users AS picture_uploaded_user ON most_liked_picture.user_id = picture_uploaded_user.id
LEFT JOIN
(SELECT picture_id, liked AS current_user_liked_picture
FROM user_picture_swipes
WHERE (user_id = 1)) AS user_picture_swipe ON most_liked_picture.id = user_picture_swipe.picture_id
WHERE ((expire_date IS NULL) OR (CURRENT_DATE() <= expire_date)) AND
ST_Within(businesses.point_location, envelope(linestring(POINT(-81.581235 - 5 / abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)), POINT(-81.581235 + 5 / abs(cos(radians(28.417839)) * 69),28.417839 + (5 / 69)))))
ORDER BY
IF(IFNULL(current_business_plan.plan_type_id, 1) != 1, IFNULL(most_liked_picture.business_picture, 0), 0) DESC,
most_liked_picture.lifetime_likes DESC,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839))
이제 각 비즈니스에 대해 하나의 사진 만 반환하는 방법을 알아낼 수 있다면. 나는 집계 MAX (most_liked_picture.lifetime_likes) 및 GROUP BY business.id를 사용해 보았지만 내 주문을 제거하여 올바른 사진을 얻지 못했습니다.
이 간단한 쿼리 :
SELECT
b.*
, bp.business_id
, bp.plan_type_id
, p.likes
, p.b_pic
, u.username
, u.picture_path
FROM business_plans AS bp
INNER JOIN businesses AS b ON bp.business_id = b.id
left join pictures AS p on b.id = p.id
left join users AS u on p.user_id = u.id
WHERE (CURRENT_DATE() BETWEEN bp.start_date AND bp.end_date)
OR
(bp.end_date IS NULL AND CURRENT_DATE() >= bp.start_date)
다음과 같은 결과를 생성합니다.
id name lat lng point_location business_id plan_type_id likes b_pic username picture_path
-- --------------- --- --- -------------- ----------- ------------ ----- ----- --------- ------------------
1 test_business_1 28 -82 null 1 1 5 1 test user1_picture_Path
1 test_business_1 28 -82 null 1 2 5 1 test user1_picture_Path
2 Sea_World 33 117 null 2 2 1 1 null null
3 Disneyland 34 118 null 3 1 5 0 username2 null
4 Disney World 28 -82 null 4 2 20 1 null null
(5 row(s) returned)
(25 row(s) affected)
처음부터 다시 시작하는 것이 좋습니다.
아마도 이것이 시작점입니까? 하지만 정말 잘 모르겠어요
SELECT b.*, bp.*
FROM business_plans AS bp
INNER JOIN businesses AS b on bp.business_id = b.id
-- sample data needs start_date or end_date
WHERE (CURRENT_DATE() BETWEEN start_date AND end_date)
OR (end_date IS NULL AND CURRENT_DATE >= start_date)
또한 테스트 데이터가 where 절을 충족하는지 확인하십시오.
CREATE TABLE businesses
(`id` int, `name` varchar(26), `lat` numeric, `lng` numeric, `point_location` int)
;
INSERT INTO businesses
(`id`, `name`, `lat`, `lng`, `point_location`)
VALUES
(1, 'test_business_1', 28.418908, -81.586254, NULL),
(2, 'Sea_World', 32.764800, 117.226600, NULL),
(3, 'Disneyland', 33.812100, 117.919000, NULL),
(4, 'Disney World', 28.417839, -81.581235, NULL),
(5, 'business near Disney World', 28.408642, -81.572607, NULL)
;
CREATE TABLE business_plans
(`id` int, `plan_type_id` int, `business_id` int)
;
INSERT INTO business_plans
(`id`, `plan_type_id`, `business_id`)
VALUES
(1, 1, 1),
(2, 2, 1),
(3, 2, 2),
(4, 1, 3),
(5, 2, 4)
;
CREATE TABLE pictures
(`id` int, `business_id` int, `user_id` int, `lat` int, `lng` int, `point` int, `likes` int, `b_pic` int)
;
INSERT INTO pictures
(`id`, `business_id`, `user_id`, `lat`, `lng`, `point`, `likes`, `b_pic`)
VALUES
(1, 2, 1, 32.764800, 117.226600, NULL, 5, 1),
(2, 3, 0, 33.812100, 117.919000, NULL, 1, 1),
(3, 3, 2, 33.812100, 117.919000, NULL, 5, 0),
(4, 3, 0, 33.812100, 117.919000, NULL, 20, 1),
(5, 3, 2, 33.812100, 117.919000, NULL, 10, 1),
(6, 4, 1, 28.417839, -81.581235, NULL, 20, 1),
(7, 4, 1, 28.417839, -81.581235, NULL, 45, 0),
(8, 5, 2, 28.417839, -81.581235, NULL, 20, 1)
;
CREATE TABLE users
(`id` int, `username` varchar(9), `picture_path` varchar(18))
;
INSERT INTO users
(`id`, `username`, `picture_path`)
VALUES
(1, 'test', 'user1_picture_Path'),
(2, 'username2', NULL),
(3, 'username3', NULL),
(4, 'username5', NULL),
(5, 'username5', NULL),
(6, 'username6', NULL),
(7, 'username7', NULL)
;
CREATE TABLE user_picture_swipe
(`id` int, `picture_id` int, `business_id` int, `user_id` int, `liked` int)
;
INSERT INTO user_picture_swipe
(`id`, `picture_id`, `business_id`, `user_id`, `liked`)
VALUES
(1, 1, 2, 2, 0),
(2, 2, 3, 2, 1),
(3, 2, 3, 1, 1),
(4, 3, 3, 1, 1),
(5, 4, 3, 1, 0),
(6, 7, 4, 1, 1),
(7, 6, 4, 1, 0),
(9, 8, 5, 2, 1)
;
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다