我的桌子就像
爱表(爱)
animal_id | user_id | time
1 1
2 1
1 3
1 5
2 3
3 1
动物表(动物)
id | type | name
1 air animal 1
2 ground animal 2
3 water animal 3
4 space animal 4
5 air animal 5
6 ground animal 6
我的查询
( select count(*) from loves where animal_id = a.animal_id) as n_l,
a.name
FROM animal a ,loves lp
WHERE a.animal_id = lp.animal_id
AND lp.user_id = $MYUSERID
AND a.type IN ($MYANIMALTYPES)
ORDER BY lp.time DESC
LIMIT 5
现在如果$MYUSERID = 1
和$MYANIMALTYPES = 'air','ground'
我应该从上面的示例中获取输出
n_l | name
3 animal 1
2 animal 2
但是由于某种原因,我想得到以下的组合
$MYANIMALTYPES = 'air','ground'
,
$MYANIMALTYPES = 'air','water'
,
$MYANIMALTYPES = 'space'
,
和
$MYANIMALTYPES = 'space','water
我必须分别成组运行上述查询4次$MYANIMALTYPES
。
我的问题是我要查询的查询太多了。有什么方法可以在一个查询中获得我想要的东西吗?
更新
为了简单理解,我如何将下面的两个查询合并为一个查询?
( select count(*) from loves where animal_id = a.animal_id) as n_l,
a.name
FROM animal a ,loves lp
WHERE a.animal_id = lp.animal_id
AND lp.user_id = $MYUSERID
AND a.type IN ('ground','air')
ORDER BY lp.time DESC
LIMIT 5
和
( select count(*) from loves where animal_id = a.animal_id) as n_l,
a.name
FROM animal a ,loves lp
WHERE a.animal_id = lp.animal_id
AND lp.user_id = $MYUSERID
AND a.type IN IN ('air','space')
ORDER BY lp.time DESC
LIMIT 5
您可以使用查询的这种适应方式。它将联接添加到您感兴趣的4种类型组合中:
SELECT ( SELECT count(*)
FROM loves
WHERE animal_id = a.animal_id) as n_l,
a.name,
trim(concat(types.type1, ' ', types.type2)) grp
FROM animal a
INNER JOIN loves lp
ON a.animal_id = lp.animal_id
INNER JOIN ( SELECT 'air' type1, 'ground' type2
UNION ALL
SELECT 'air', 'water'
UNION ALL
SELECT 'space', ''
UNION ALL
SELECT 'space', 'water' ) AS types
ON a.type IN (types.type1, types.type2)
WHERE lp.user_id = $MYUSERID
GROUP BY a.name,
trim(concat(types.type1, ' ', types.type2))
ORDER BY 3, lp.time DESC
LIMIT 15
我还设置了更高的限制,因为您现在将在一个查询中获得所有结果。
这是一个小提琴。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句