奋力将WHERE子句INNER JOIN

我这是应该选择最低的查询price_per_pax_after_tax从每backend_hotels_id date_startpackage_supplier ,这似乎是工作,直到我添加了一个WHERE子句。

这里的查询:

SELECT e.price_per_pax_after_tax, e.hotel_score, e.package_id, e.package_type FROM packages_sorted_YQU e INNER JOIN ( SELECT db_id, MIN( price_per_pax_after_tax ) AS lowest_price, package_id, hotel_score FROM `packages_sorted_YQU` WHERE `package_type` IN ('9', '10', '18') AND `package_duration` IN ('6', '8', '12') GROUP BY `date_start` , `package_supplier` , `backend_hotels_id` ) AS j ON j.db_id = e.db_id AND j.lowest_price= e.price_per_pax_after_tax AND j.hotel_score = e.hotel_score AND j.package_id = e.package_id;

该表是巨大的,但列出的所有字段都是INT除了date_start这是DATE

导致此问题的where子句:

WHERE `package_type` IN ('9', '10', '18') AND `package_duration` IN ('6', '8', '12')

如果没有where子句我得到超过400个结果,并与where子句我得到结果为零:(任何帮助将是非常赞赏。

--------------解决方案-------------

子查询:

SELECT db_id
, MIN( price_per_pax_after_tax ) AS lowest_price
, package_id
, hotel_score
FROM `packages_sorted_YQU`
WHERE `package_type` IN ('9', '10', '18')
AND `package_duration` IN ('6', '8', '12')
GROUP BY
`date_start`
, `package_supplier`
, `backend_hotels_id`

将产生不确定的结果,使用或不使用WHERE子句。 因为您的分组date_start, package_supplier, backend_hotels_id并在SELECT列表的列不会对他们的任何聚合函数: db_id, package_id, hotel_score

该查询要坚持工作,如果(date_start, package_supplier, backend_hotels_id)是主键或唯一。

这是PRIMARY KEY表以及是否有任何其他的UNIQUE的钥匙?

如果你的列package_typepackage_duration是类型的int ,你不必包裹里面价值'例如字符串。

SELECT e.price_per_pax_after_tax, e.hotel_score, e.package_id, e.package_type
FROM packages_sorted_YQU e
INNER JOIN (
SELECT db_id, MIN( price_per_pax_after_tax ) AS lowest_price, package_id, hotel_score
FROM `packages_sorted_YQU`
WHERE `package_type` IN (9, 10, 18)
AND `package_duration` IN (6, 8, 12)
GROUP BY
`date_start` , `package_supplier` , `backend_hotels_id`
) AS j
ON j.db_id = e.db_id
AND j.lowest_price= e.price_per_pax_after_tax
AND j.hotel_score = e.hotel_score
AND j.package_id = e.package_id;

大家好,感谢您的宝贵意见。 我已经解决了这个问题没有一个子查询,它的工作原理有点快了。

SELECT MIN
(
concat
(
LPAD(`price_per_pax_after_tax` , 5, '0'),
LPAD(`package_id` , 12, '0'),
LPAD(`hotel_score` , 7, '0')
)
) AS cat
FROM `packages_sorted_YQU`
WHERE `package_type` IN
(
9, 10, 18
)
AND `package_duration` IN
(
6, 7, 8
)
GROUP BY `date_start` , `package_supplier` , `backend_hotels_id`

然后,在PHP我掰开的串联:

while($r=mysql_fetch_array($q,MYSQL_ASSOC))
{
$a[lrp][] = intval(substr($r[cat], 0, 5));
$a[package_id][] = intval(substr($r[cat], 5, 12));
$a[hotel_score][] = substr($r[cat], 17, 7);
}

我是幸运的,唯一的浮点值是hotel_score所以我把去年-另外两个是类型为INT

分类:php 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.514 (s). 10 q(s)