这里有3个表。
1) 酒店
-----------------
|Hotel_ID | Name |
-----------------
| 1 |Shangrila |
----------------------
| 2 |GoldHill |
----------------------
| 3 |BayBeach |
----------------------
2) 功能
----------------------
|Feature_ID| Feature |
----------------------
| 1 | Goft |
----------------------
| 2 |Internet |
----------------------
3) Brdige_Hotel_Feature
------------------------
|Hotel_ID | Feature_ID |
------------------------
| 1 | 1 |
------------------------
| 1 | 2 |
-----------------------
| 2 | 1 |
-----------------------
这意味着每家酒店可能都有一个以上的功能。
比方说,如果我想从表3 Bridge_Hotel_Feature中获得结果,我的想法是这样的。如果Feature_ID=1,我得到酒店1和2。**如果Feature_ID=1,2。我只想酒店1。但我总是同时得到Hotel_ID 1和2。**请帮助我获得仅与Hotel_ID匹配的功能的解决方案。
下面是我尝试的代码。
SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge1_Hotel_Features b, Features f
where 0=0
AND b.Feature_ID = f.Feature_ID
AND b.Hotel_ID = r.Hotel_ID
<cfif #FORM.Feature_ID# IS NOT "">
AND f.Feature_ID IN (#FORM.Feature_ID#)
</cfif>
如果Feature_ID=1,2,则结果必须仅获取Hotel_ID=1
因为只有Hotel_ID=1同时具有这两个功能,对吗?假设#FORM.Feature_ID#
不包含重复项,请使用HAVING子句动态标识具有所有请求功能的酒店。
SELECT Hotel_ID, COUNT(Feature_ID) AS FeatureCount
FROM Bridge_Hotel_Feature
<!--- find matching features --->
WHERE Feature_ID IN ( <cfqueryparam value="#FORM.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
GROUP BY Hotel_ID
<!--- having ALL of the requested features --->
HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(FORM.Feature_ID)#" cfsqltype="cf_sql_integer">
然后,您可以作为派生表或子查询联接到它。sql需要优化,但在概念上类似
SELECT h.Hotel_ID, h.Name, f.Feature
FROM Hotel h
INNER JOIN Bridge_Hotel_Feature b ON b.Hotel_ID = h.Hotel_ID
INNER JOIN Features f ON b.Feature_ID = f.Feature_ID
INNER JOIN
(
SELECT Hotel_ID, COUNT(Feature_ID) AS FeatureCount
FROM Bridge_Hotel_Feature
<!--- find matching features --->
WHERE Feature_ID IN ( <cfqueryparam value="#FORM.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
GROUP BY Hotel_ID
<!--- having ALL of the requested features --->
HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(FORM.Feature_ID)#" cfsqltype="cf_sql_integer">
) ck ON ck.Hotel_ID = h.Hotel_Id
将sql更改为:
SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge1_Hotel_Features b, Features f
where b.Feature_ID = f.Feature_ID
AND b.Hotel_ID = h.Hotel_ID
<cfif #FORM.Feature_ID# IS NOT "">
AND b.Feature_ID IN (#FORM.Feature_ID#)
</cfif>
本质上,where子句的可选部分应该限制Bridge_Hotel_Features表中的feature_id。
您需要使用EXIST来解决此问题。
SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge_Hotel_Feature b, Feature f
where b.Feature_ID = f.Feature_ID
AND b.Hotel_ID = h.Hotel_ID
AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 1 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)
AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 2 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)
编辑:以上是查询所需外观的示例。为了使其动态,您将添加一个循环。
我自己没有coldfusion的经验,所以我不能逐字逐句地告诉你该如何处理那个代码。
但是,您需要做的是将以下代码部分包装在一个循环中,并将其附加到每个复选框的查询字符串中,并用每个复选框中的feature_id替换where子句中的feature _id。AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 1 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)
我希望这能让你更清楚。