mysql - SELECT count() 具有两个相同的条件

Question

我有以下数据库。对于每个对话，有两个参与者。当participant.participant_id = participantID1 和participant.participant_id = participantID2 时，我想统计conversation_id 的数量。

CREATE TABLE IF NOT EXISTS `users` (
    `user_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(16)
);

CREATE TABLE IF NOT EXISTS `conversation` (
    `conversation_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED,
    `time_started` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `time_closed` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE IF NOT EXISTS `participant` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `conversation_id` BIGINT UNSIGNED,
    `participant_id` BIGINT UNSIGNED,
    `time_joined` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `time_left` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id),
    FOREIGN KEY (participant_id) REFERENCES users(user_id)
);

我尝试了类似的方法，但最终计数 = 0:

SELECT count(conversation.conversation_id)
FROM `conversation`
INNER JOIN `participant` on participant.conversation_id = conversation.conversation_id
WHERE participant.participant_id = ? AND participant.participant_id = ?

我设法以不同的方式做到了这一点，但我想知道是否可以在一个查询中做到这一点。感谢您的帮助! :-)

Answer 1

我现在无法检查这个，但像这样的东西应该可以工作:

SELECT count(c.conversation_id)
FROM conversation c
INNER JOIN participant p1 ON p1.conversation_id = c.conversation_id
INNER JOIN participant p2 ON p2.conversation_id = c.conversation_id
WHERE p1.participant_id = ? AND p2.participant_id = ?