Mysql添加别名，有复杂性

发布于 2024-10-21 16:19:31 字数 2449 浏览 5 评论 0原文

所以我建立了一个预订系统，但我在一些价格计算方面遇到了一些问题。

最初我有

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type IN ('single','double') AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

哪个工作得很好，除了如果你尝试计算同一类型的多个房间类型它效果不太好

无论如何，我尝试将房间类型的查询分开，并将值乘以该类型的房间数量预订。我正在使用这个（一起添加MySQL别名字段），这意味着我必须添加一个额外级别的子查询让事情有点混乱。

我现在已经快到了，但它无法识别我的子查询中的字段名之一，我预计这是由于它执行查询的顺序所致。

我现在所拥有的是：

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT sum_price_single, sum_price_double, sum_price_single + sum_price_double AS sum_price FROM
        (SELECT
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_single,
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_double
            FROM booking_dates
        )
    x)
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

并且给我以下错误

“where”中的未知列“c_id” 条款'

有什么想法吗？这应该输出一份酒店列表（c_content 表中的值）以及每家酒店的价格。价格由各个日期的总和组成，但还必须考虑到多间房以及也有不同价格的混合房型。

原文

So I have built a booking system, and I am having a few problems with some of the price calculations.

Originally I had

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type IN ('single','double') AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

Which worked fine, except it didnt work very well if you try to calculate multiple room types of the same type

Anyways I tried to split the queries out for room types, and multiple the value by the number of rooms for that type in the booking. I was using this ( Adding MySQL alias fields together ) which meant I had to add an extra level of sub queries which confused things a bit.

I am nearly there now, but its not recognising one of the fieldnames in my sub queries, I expect due to the order in which it is executing the query.

What I have now is:

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT sum_price_single, sum_price_double, sum_price_single + sum_price_double AS sum_price FROM
        (SELECT
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_single,
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_double
            FROM booking_dates
        )
    x)
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

And is giving me the following error

Unknown column 'c_id' in 'where
clause'

Any ideas? This should output a list of hotels (values from c_content table), and a price for each one. The price consists of the SUM of various dates, but also has to take into account mtuliple rooms, and mixed room types which have different prices also.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雨落星ぅ辰 2024-10-28 16:19:31

子选择可能会成为性能杀手，应尽可能避免。嵌套子选择的危害性要大很多倍，并且会导致类似于您遇到的范围问题。我建议删除所有子子选择，如果您有时间，请重写查询，使其根本不需要子选择。

与此同时，我认为类似以下内容应该可以解决您的范围界定问题。

SELECT *, sum_price_single + sum_price_double AS sum_price FROM (
    SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_single,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_double
    FROM c_content
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
    WHERE site_id = '15'
) AS t1

本质上是将核心子选择向上移动两级，并将单精度和双精度的“总和”包装到外部选择中。确认一下，这不是最佳选择......但它应该可以解决您眼前的问题。我可能会在午餐后重新审视这个问题：P

Subselects are can be a performance killer, and should be avoided where possible. Nested sub-selects are many times more evil, and will cause scope problems similar to the one you're encountering. I'd recommend removing all of the sub-sub-selects, and if you have time, rewriting the query such that it requires no subselects at all.

In the meantime, something like the following should resolve your scoping problem, I think.

SELECT *, sum_price_single + sum_price_double AS sum_price FROM (
    SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_single,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_double
    FROM c_content
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
    WHERE site_id = '15'
) AS t1

Essentially moving the core subselects up two levels, and wrapping the "sum" of single and double into an outer select. To confirm, this isn't optimal ... but it should solve your immediate problem. I may revisit the problem after lunch :P

回复收藏 0 原文

醉梦枕江山 2024-10-28 16:19:31

不是最终的解决方案，就像 Jeff Parkers 更喜欢的那样，但这是我用来在短期内解决它的查询

SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1)
+
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15' ORDER BY sum_price desc

Not the final solution, as prefer Jeff Parkers, but here is the query I used to solve it in the short term

SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1)
+
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15' ORDER BY sum_price desc

回复收藏 0 原文

~没有更多了~