MySQLでのコラボレーションの検索

テーブルをそれ自体と結合し、personフィールドでグループ化します。

SELECT u2.person, COUNT(u1.project) AS collaborations
FROM users u1
JOIN users u2 ON u2.project = u1.project
WHERE u1.person != u2.person AND u1.person = 'Jimmy'
GROUP BY u2.person;

クエリは、ジミーが参加したプロジェクトを選択しますu1。からの行は、からの行u2によってフィルタリングされますu1。両方のテーブルのユーザーが一致する重複エントリは、WHERE句でフィルタリングされます。最後に、結果セットはによってグループ化されperson、COUNT関数はグループごとの行数を計算します。

パフォーマンス

ノートのインデックスperson及びproject列（または2つの別個のインデックス）が大幅に上記のクエリのパフォーマンスを向上させるであろう。特定のインデックス構成は、テーブル構造によって異なります。けれども、私は次のことが非常に十分だと思う2とテーブルのvarcharためのフィールドpersonとproject、たとえば、：

ALTER TABLE users ADD INDEX `project` (`project`(10));
ALTER TABLE users ADD INDEX `person` (`person`(10));

正規化

ただし、個人とプロジェクトは、数値IDを使用して別々のテーブルに保存したいと思います。3番目のテーブルはコネクタの役割を果たすことができますperson_id - project_id。つまり、正規化をお勧めします。正規化されたテーブルを使用すると、テキストフィールドの肥大化したインデックスを作成する必要はありません。

正規化されたテーブルは次のようになります。

CREATE TABLE users (
  id int unsigned NOT NULL AUTO_INCREMENT,
  name varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`),
  -- This index is needed, if you want to fetch users by names
  INDEX name (name(8))
);
CREATE TABLE projects (
  id int unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`)
);
CREATE TABLE collaborations (
  project_id int unsigned NOT NULL DEFAULT 0,
  user_id int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY(`project_id`, `user_id`)
);

正規化された構造のクエリは、もう少し複雑に見えます。

-- In practice, the user ID is retrieved from the calling process
-- (such as POST/GET HTTP requests, for instance).
SET @user_id := (SELECT id FROM users WHERE name LIKE 'Jimmy');

SELECT u.name person, COUNT(p.id) collaborations
FROM collaborations c
JOIN collaborations c2 USING(project_id)
JOIN users u ON u.id = c2.user_id
JOIN projects p ON p.id = c2.project_id
WHERE c.user_id = @user_id AND c.user_id != c2.user_id
GROUP BY c2.user_id;

ただし、高速であり、特に大規模なデータセットの場合、インデックスに必要なスペースは大幅に小さくなります。

元の答え

各人のプロジェクトの総数を取得するには、COUNT関数withGROUP BY句を使用します。

SELECT person, COUNT(*) AS collaborations
FROM users
GROUP BY person;