从交叉引用表中获取唯一值 - Fetch unique values from cross reference table

Fetch unique values from cross reference table

本文关键字：获取唯一引用 | 更新日期: 2023-09-27

现在我有一个交叉引用表，看起来像这样:

       Table job2pos
╔═══════════╦═════════════╗
║  job_id   ║ position_id ║
╠═══════════╬═════════════╣
║  1        ║  10         ║
║  2        ║  10         ║
║  2        ║  12         ║
║  3        ║  11         ║
║  3        ║  13         ║
║  4        ║  10         ║
║  5        ║  13         ║
╚═══════════╩═════════════╝

我想选择所有具有postion_id = 10和position_id = 12的job_id，这将导致job_id = 1,2,2,4。但我只希望job_id = 2只出现一次在我的获取结果。

我试过的代码如下:

$positions = ["10","12"];
$sql = "SELECT job2pos.job_id FROM job2pos
WHERE job2pos.pos_id IN (?,?)";
$stmt = $pdo->prepare($sql);
foreach ($positions as $key => &$val) {
  $stmt->bindParam($key, $val, 'PDO::PARAM_INT);
}
$stmt->execute();
var_dump($stmt->fetchAll('PDO::FETCH_UNIQUE));

，上面的尝试将产生以下结果:

array(3) {
  [1]=> array(0) {}
  [2]=> array(0) {}
  [4]=> array(0) {}
}

所以我确实得到了唯一的值，额外的2是不重复的，但我如何使job_id不显示为索引，而是一个值在一个数组?

注:如果你能指出我的代码中任何不必要的代码，那就太好了。

特殊的SELECT DISTINCT结构将只从您的查询中带来不同的值。因此，您可以将代码重写为:

$postions = ["10","12"];
$sql = "SELECT DISTINCT job2pos.job_id FROM job2pos WHERE job2pos.pos_id IN (?,?)";
$stmt = $pdo->prepare($sql);
// btw you don't have to bind elements one by one
// you can pass array as an argument to execute:
$stmt->execute($positions);
print_r($stmt->fetchAll('PDO::FETCH_ASSOC));