mysql - 使用联接和子查询的多个搜索参数的SQL选择

  显示原文与译文双语对照的内容
0 0

我花了几个小时来寻找问题的答案,但没有得到。

我想从玩家。村子和联盟-tables和日历表中选择一个查询来选择一切。

选择必须按下列规则过滤:

  1. 按日期选择最新信息。
  2. 如果玩家在此时拥有 <= 数量的村庄,请选择
  3. 仅在玩家总数为时,选择

    1. 还有 3造成我头部伤害的原因。 如何将这些内容添加到我的查询中?

以下是我当前的查询:

SELECT players.name AS player, 
 players.uid as uid, 
 players.tid, 
 villages.name AS village, 
 villages.vid as vid, 
 villages.fid as fid, 
 alliances.name AS alliance, 
 alliances.aid as aid, 
 SQRT( POW( least(abs($xcoord - villages.x),
 400-abs($xcoord - villages.x)), 2 ) + 
 POW( least(abs($ycoord - villages.y),
 400-abs($ycoord - villages.y)), 2 ) ) AS distance
FROM histories
LEFT JOIN players ON players.uid = histories.uid
LEFT JOIN villages ON villages.vid = histories.vid
LEFT JOIN alliances ON alliances.aid = histories.aid
LEFT JOIN histories h2 
 ON ( histories.vid = h2.vid AND histories.idhistory <h2.idhistory )
WHERE h2.vid IS NULL
 AND histories.uid!= $uid
 AND SQRT( POW(least(abs($xcoord - villages.x),
 400-abs($xcoord - villages.x)), 2 ) + 
 POW(least(abs($ycoord - villages.y),
 400-abs($ycoord - villages.y)), 2 ) ) <$rad
ORDER BY distance

注意:xcoord和ycoord是从搜索表单中发布的。

示例输出:

PLayer| Village | Alliance | Distance
P1 | V1 | A1 | 1 
P2 | V4 | A2 | 2 
P1 | V2 | A1 | 3 
P1 | V3 | A1 | 4 
P2 | V5 | A2 | 5 

感谢你的帮助。 : )

这里查询可以查找小于 2个村庄的玩家。 我只是不能把我的原始查询和这个。 可行 吗?

SELECT
b.*, count(b.uid) as hasvillages
FROM 
histories b
WHERE 
b.vid IN (SELECT a.vid FROM villages a) 
GROUP BY 
b.uid
HAVING 
count(b.uid) <2 

这里的是SQLFIDDLE的链接。

这里是我的数据库of图像图片的链接

时间:原作者:6个回答

0 0

经过一周的尝试,我终于找到答案了。 通过这里查询,我可以使用以下搜索参数:

  1. 按日期查找最新行
  2. 通过限制播放机的村庄数量查找行。
  3. 通过限制播放机的总体总体填充来查找行。
  4. 通过计算距离查找行。
  5. 从选择中排除玩家或者联盟。

下面是查询

SELECT players.name AS player, players.uid as uid, players.tid, 
villages.name AS village, villages.vid as vid, villages.fid as fid, 
alliances.name AS alliance, alliances.aid as aid, 
SQRT( POW( least(abs(100 - villages.x),400-abs(100 - villages.x)), 2 ) + 
POW( least(abs(100 - villages.y),400-abs(100 - villages.y)), 2 ) ) AS distance
 FROM histories
 LEFT JOIN players ON players.uid = histories.uid
 LEFT JOIN villages ON villages.vid = histories.vid
 LEFT JOIN alliances ON alliances.aid = histories.aid
 WHERE histories.uid IN 
 (SELECT b.uid FROM histories b
 WHERE (b.vid IN (SELECT a.vid FROM villages a) and b.date
 in (select max(date) from histories))
 GROUP BY b.uid HAVING count(b.uid) <4 AND
 sum(b.population) <2000)
 AND histories.uid!= 1
 and histories.date in (select max(date) from histories)
 AND SQRT( POW( least(abs(100 - villages.x),400-abs(100 - villages.x)),2)+ 
 POW( least(abs(100 - villages.y),400-abs(100 - villages.y)), 2 ) ) <200
 ORDER BY distance
原作者:
...