入门选定项目,没有选择
问题描述:
我想找出选择什么项目,什么没被选中成为0入门选定项目,没有选择
这是列表框(或下拉菜单)是有用的,什么是强调它的意思是它已经加入(LEFT JOIN)
我不能让SQL工作:
SELECT StoreID, ft.id, name, count(1) as Selected
FROM `store_food_type` AS sft
LEFT JOIN food_type AS ft
ON ft.id = sft.food_type_id
WHERE StoreID = 17633
GROUP BY ft.id
UNION
SELECT 0, ft.id, name, 0 as Selected FROM food_type AS ft
编辑:它并没有产生一个错误,它显示的结果,但name
是重复的,我不想
它看起来是这样的:
+------------+------+----------+----------+
| StoreID | id | name | Selected |
+------------+------+----------+----------+
| 17633 | 1 | Indian | 1 |
| 17633 | 8 | American | 1 |
| 0 | 1 | Indian | 0 |
| 0 | 2 | English | 0 |
| 0 | 25 | Kurdish | 0 |
| 0 | 5 | Chinese | 0 |
| 0 | 7 | Turkish | 0 |
| 0 | 8 | American | 0 |
+------------+------+----------+----------+
我想要的结果出现这样的:因为在你查询UNION
的
+------------+------+----------+----------+
| StoreID | id | name | Selected |
+------------+------+----------+----------+
| 17633 | 1 | Indian | 1 |
| 17633 | 8 | American | 1 |
| 0 | 25 | Kurdish | 0 |
| 0 | 5 | Chinese | 0 |
| 0 | 7 | Turkish | 0 |
| 0 | 8 | American | 0 |
+------------+------+----------+----------+
答
你行得到复制。事实上,你的整个查询是反向的 - 你试图获得所有食物类型的列表,并且如果它们被列在给定商店中,则有指示符。
试试这个:
SELECT ft.id, ft.name, COALESCE(sft.storeId, 0) as storeId,
CASE WHEN sft.storeId IS NOT NULL THEN '1'
ELSE '0' END as selected
FROM food_type a ft
LEFT JOIN store_food_type as sft
ON sft.food_type_id = ft.id
AND sft.storeId = 17633
虽然你不严格需要selected
列,因为你可以简单地测试结果列storeId
是否0。
答
也许最简单的方法是将集团的结果从被联合查询 - 就像这样:
SELECT MAX(StoreID) StoreID, id, MAX(name) name, MAX(Selected) Selected FROM
(SELECT StoreID, ft.id, name, count(1) as Selected
FROM `store_food_type` AS sft
LEFT JOIN food_type AS ft
ON ft.id = sft.food_type_id
WHERE StoreID = 17633
GROUP BY ft.id
UNION
SELECT 0, ft.id, name, 0 as Selected FROM food_type AS ft) sq
GROUP BY id
答
我不知道这个答案是比任何由马克·班尼斯特提供的UNION选择更好的,但它是另一种方式来做到这一点。
SELECT sft.storeid, ft.id, ft.name, count(sft.storeid) selected
FROM store_food_type sft
FULL JOIN food_type ft ON ft.id = sft.food_type_id
WHERE sft.storeid = 17633 or sft.storeid is null
GROUP BY ft.id, ft.name, sft.storeid
如果一个食品只能在任何给定的商店中选择一次,那么“selected”字段和GROUP BY是多余的。
+0
我觉得MySQL不支持'FULL JOIN' – 2012-03-09 21:14:40
此查询以何种方式失效?它是否产生错误,是否运行没有错误,但不选择行或返回错误的行? – 2012-03-09 16:57:11
@MarkBannister我已经更新了我的问题。 – 2012-03-09 17:07:13
同一家商店可以多次选择食物吗? – Griffin 2012-03-09 17:57:54