php - 按多列限制查询

Question

我的查询:

    $units = DB::table('units')
    ->join('locations', 'locations.id', '=', 'units.location_id')
    ->join('castles', 'castles.id', '=', 'units.castle_id')
    ->join('unit_types', 'unit_types.id', '=', 'units.unit_type_id')
    ->select('units.location_id',
        'units.previous_location_id',
        'units.id as unit_id',
        'units.castle_id',
        'castles.guild_id',
        'units.unit_type_id',    
        'units.current_health',
        'unit_types.damage',
        'unit_types.range')
    ->get()
    ->groupBy('location_id', 'castle_id');

结果是这样的一些测试数据:

{
    "1": [
        {
            "location_id": 1,
            "previous_location_id": 1,
            "unit_id": 2,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 3,
            "current_health": 90,
            "damage": 10,
            "range": 3
        }
    ],
    "2": {
        "1": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 3,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 5,
            "current_health": 100,
            "damage": 20,
            "range": 2
        },
        "6": {
            "location_id": 2,
            "previous_location_id": 5,
            "unit_id": 7,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    },
    "3": {
        "2": {
            "location_id": 3,
            "previous_location_id": 3,
            "unit_id": 4,
            "castle_id": 3,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "3": {
            "location_id": 3,
            "previous_location_id": 3,
            "unit_id": 5,
            "castle_id": 3,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
    "4": {
        "5": {
            "location_id": 4,
            "previous_location_id": 4,
            "unit_id": 8,
            "castle_id": 4,
            "guild_id": null,
            "unit_type_id": 20,
            "current_health": 300,
            "damage": 40,
            "range": 2
        },
        "7": {
            "location_id": 4,
            "previous_location_id": 1,
            "unit_id": 1,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
    "5": {
        "4": {
            "location_id": 5,
            "previous_location_id": 5,
            "unit_id": 6,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    }
}

我试图找到一种方法来限制查询，以便我只在 location_id 处获得结果相同但castle_id是不同的，它看起来像这样:

{
  "2": {
        "1": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 3,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 5,
            "current_health": 100,
            "damage": 20,
            "range": 2
        },
        "6": {
            "location_id": 2,
            "previous_location_id": 5,
            "unit_id": 7,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    },
    "4": {
        "5": {
            "location_id": 4,
            "previous_location_id": 4,
            "unit_id": 8,
            "castle_id": 4,
            "guild_id": null,
            "unit_type_id": 20,
            "current_health": 300,
            "damage": 40,
            "range": 2
        },
        "7": {
            "location_id": 4,
            "previous_location_id": 1,
            "unit_id": 1,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
}

我试过类似 ->whereColumn('units.location_id', '<>', 'units.castle_id') 的东西但这在 castle_id 的情况下不起作用碰巧有一把相似的 key 。

---

编辑当我有超过 1 个 castle_id 时，这个原始 SQL 会告诉我在给定位置:

SELECT location_id, 
COUNT(DISTINCT castle_id)
FROM units
GROUP BY location_id
ORDER BY COUNT DESC;

但我只想像这样直接选择那些行:

SELECT location_id, 
FROM units
WHERE (DISTINCT castle_id);

---

编辑 #2

这是一个假设:

• units有 100 行
• 10 行有 location_id: 1
• 20 行有 location_id: 3
• 等...

在具有 location_id: 1 的 10 行中所有 10 个也有 castle_id: 76

在有 location_id: 3 的 20 行中其中 13 个有 castle_id: 99其中 7 个有 castle_id: 42

我正在尝试确定行何时具有相同的 location_id但是castle_id不相同。

在这种情况下，我会跳过 10 行，保留符合我的条件的 20 行，然后检查剩余的 70 行。

---

编辑 #3这是请求的 SQL fiddle :http://sqlfiddle.com/#!9/fad08

---

编辑#4这是我的新查询:

    $units = DB::table('units AS u1')
    ->select('u1.location_id',
        'u1.previous_location_id', 
        'u1.id as unit_id',
        'u1.castle_id',
        'c1.guild_id',
        'u1.unit_type_id',
        'u1.current_health',
        'ut.damage',
        'ut.range')
    ->distinct()
    ->join('units AS u2', 'u1.location_id', '=', 'u2.location_id')
    ->join('castles AS c1', 'c1.id', '=', 'u1.castle_id')
    ->join('castles AS c2', 'c2.id', '=', 'u2.castle_id')
    ->join('unit_types AS ut', 'ut.id', '=', 'u1.unit_type_id')
    ->whereColumn([
        ['u1.castle_id', '<>', 'u2.castle_id'],
        ['c1.guild_id', '<>', 'c2.guild_id']
        ])
    ->orderBy('location_id')
    ->get();

    echo $units;

基于 fubar 的回答，在 guild_id 时添加了另一个约束条件是一样的。

Answer 1

这个查询应该可以解决问题

SELECT id, location_id, castle_id FROM Units WHERE location_id IN (
    SELECT a.location_id FROM Units a
    JOIN Units b on (
       b.location_id = a.location_id AND 
       b.castle_id <> a.castle_id
    )
) ORDER BY location_id