我的数据库中有一个名为Player的表,如下所示
id placex placey wealth strenght goalx goaly
---------------------------------------------------------------
1 500 20 3335 2500 500 440
2 500 20 777 2000 20 500
3 20 500 1000 2000 500 20
4 500 440 1000 2000 20 20 我需要一个查询来显示id和财富,条件是玩家a位于相同的位置,所以应该有一个placey的副本,供不同的玩家使用。
在这个表中,我们将在同一个位置使用id 1的player和id 2的player,我写了下面的
SELECT
id, placex, placey, wealth
FROM
Player
WHERE
placex IN (SELECT placex FROM Player HAVING COUNT(id) > 1);我得到这个作为输出
+----+--------+--------+--------+
| id | placex | placey | wealth |
+----+--------+--------+--------+
| 1 | 500 | 20 | 3335 |
| 2 | 500 | 20 | 777 |
| 4 | 500 | 440 | 1000 |
+----+--------+--------+--------+我需要用id 4消除玩家,所以如何修改我的查询以包括placey的dups。
我希望能得到这个
| id | placex | placey | wealth |
1 500 20 3335
2 500 20 777发布于 2013-12-07 20:07:53
运行这个,让我知道你的结果:
SELECT A.id, A.placex, A.placey, A.wealth
FROM Player A,
( SELECT placex, placey
FROM Player
GROUP BY placex, placey
HAVING COUNT(*) > 1
) B
WHERE A.placex = B.placeX
AND A.placey = B.placey发布于 2013-12-07 19:59:42
Select id, wealth From Player
inner Join (
Select PlaceX, PlaceY From Player Having(count(id) > 1) Occupied
On Occupied.PlaceX = Player.PlaceX and Occupied.PlaceY = Player.PlaceY在子查询为您提供更多选项之前,请始终查看联接。
发布于 2013-12-07 20:05:35
SELECT id,placex,placey,wealth FROM player WHERE placey IN(
SELECT placey FROM Player
GROUP BY placey
HAVING COUNT(placey)>1)https://stackoverflow.com/questions/20445800
复制相似问题