gpt4 book ai didi

mysql - SQL 子查询未产生正确的结果

转载 作者:行者123 更新时间:2023-11-29 11:15:51 26 4
gpt4 key购买 nike

我使用的是 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 |
+-------------+--------------+--------------+-------+----------+---------------+

抱歉这篇文章太长了。如有任何帮助,我们将不胜感激。

******************************** 查询更新**************** ******************

我终于明白了!外部查询是罪魁祸首,一旦将其删除,我就得到了所需的结果。这是最终的查询(不使用右连接,也不使用 block )。感谢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))

现在,如果我能弄清楚如何为每个企业只返回 1 张图片。我尝试使用聚合 MAX(most_liked_picture.lifetime_likes) 和 GROUP BYbusinesss.id,但是它删除了我的订单,所以我没有得到正确的图片。

最佳答案

This simple query :

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)
;

关于mysql - SQL 子查询未产生正确的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39782560/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com