以前:Select2发布
更改为:一个强烈相关的SQL问题。(不想浪费赏金,因为我没有得到答案,我的赏金仍然开放!)
基本上,我有两个问题。一个是一般性的,一个是基于搜索的。
一:获得一切(这里没有问题)
SELECT store_info.storeID, store_info.name, store_info.address, store_info.phone, GROUP_CONCAT(brands.name) AS brands
FROM store_info, brands, stores, store_brands
WHERE stores.city = $cityID
AND store_brands.store = stores.ID
AND brands.id = store_brands.brand
AND stores.ID = store_info.storeID
GROUP BY store_info.storeID
ORDER BY store_info.name
这个查询正是我想要它做的。它给我的商店的品牌格式为brand1,brand2,brand3,brand4,brand5
等。
现在,我还有一个搜索功能,你可以根据品牌搜索商店。这基本上是相同的查询,有一个小的调整(此处出现问题):
SELECT store_info.storeID, store_info.name, store_info.address, store_info.phone, GROUP_CONCAT(brands.name) AS brands
FROM store_info, brands, stores, store_brands
WHERE stores.city = $cityID
AND store_brands.store = stores.ID
AND brands.ID = store_brands.brand
AND stores.ID = store_info.storeID
AND brands.ID IN (" . implode(",", $brandIDs) . ")
GROUP BY store_info.storeID
ORDER BY store_info.name
最后一个查询的问题是,我仍然希望它返回所有品牌。我只是想操纵稍后搜索的品牌。在我看来,我应该能够以多种方式做到这一点:
1)返回查询中的所有品牌,并在php中浏览时操纵品牌(我已经存储了搜索词,但我不知道如何在不获取所有商店的情况下做到这一点,这不是我想做的事情)
2)返回一个包含搜索到的匹配品牌的字段,另一个包含剩余品牌的字段。(理想的选择,但我不知道怎么做。将整个查询放入一个带有调整的联接中似乎效率低下)
3)(看起来效率很低,我不喜欢)从查询中获得找到的商店,运行一个新的查询来寻找与找到的商店相关的品牌,然后将结果与php结合起来。然而,这将增加一个额外的查询,效率是首要任务。
我遇到了这样一个问题,如果有人搜索两个品牌,并且有两个品牌都匹配的商店,它只会显示两个品牌中的一个。(我猜是第一支)。
如果您对最后一个问题有任何帮助,我们将不胜感激!
提前感谢!
编辑:深入了解搜索功能搜索功能搜索与搜索的品牌相关的商店。它应该显示与上述品牌相关的商店,但不限于只返回搜索到的品牌。
GROUP_CONCAT
忽略NULL
,因此您可以创建一个匹配的GROUP_CONCAT
,如下所示:
SELECT
store_info.storeID,
store_info.name,
store_info.address,
store_info.phone,
GROUP_CONCAT(brands.name) AS brands,
GROUP_CONCAT(
CASE WHEN brands.ID IN (" . implode(",", $brandIDs) . ")
THEN brands.name
ELSE NULL
END
) AS available_brands
FROM
stores
INNER JOIN store_info ON (stores.ID = store_info.storeID)
INNER JOIN store_brands ON (stores.ID = store_brands.store)
INNER JOIN brands ON (store_brands.brand = brands.id)
WHERE stores.city = $cityID
GROUP BY store_info.storeID
ORDER BY store_info.name
Fiddle:http://sqlfiddle.com/#!9/83e576/1
您可以第二次加入store_brands
表(下面代码中的AS restraint
),并对其进行限制:
SELECT store_info.storeID, store_info.name, store_info.address, store_info.phone, GROUP_CONCAT(DISTINCT(brands.name)) AS brands
FROM store_info, brands, stores, store_brands, store_brands AS restraint
WHERE stores.city = $cityID
AND store_brands.store = stores.ID
AND brands.ID = store_brands.brand
AND stores.ID = store_info.storeID
AND restraint.ID IN (" . implode(",", $brandIDs) . ")
GROUP BY store_info.storeID
ORDER BY store_info.name
通过两次加入该表,它将用于(1)该商店中可用的所有品牌,以及(2)将所选商店限制为具有一个或多个指定品牌的商店。
如果SELECT
子句中的CASE
语句根据匹配条件返回true或false,也就是说,如果brands.ID
在$brandIDs
的列表中。
SELECT
store_info.storeID,
store_info.name,
store_info.address,
store_info.phone,
GROUP_CONCAT(brands.name) AS brands,
(
CASE
WHEN brands.ID IN (" . implode(",", $brandIDs) . ") THEN 1
ELSE 0 --no match
END
) AS matches
FROM store_info, brands, stores, store_brands
WHERE stores.city = $cityID
AND store_brands.store = stores.ID
AND brands.ID = store_brands.brand
AND stores.ID = store_info.storeID
GROUP BY store_info.storeID
ORDER BY store_info.name
其思想是,如果brands.ID
是$brandIDs
中的id之一,则在matches
列中将返回1
。对于其他任何内容,都将返回一个0
。