MySQL 查詢相同 userDeviceId 對應多筆 user 記錄：子查詢 + GROUP BY 實現

資料結構

USER 表
userDeviceId | userCode | channelId | ...
-------------|----------|-----------|-----
dev001       | userA    | 301040    |
dev001       | userB    | 301040    |  <-- 同一 deviceId 對應多 userCode
dev002       | userC    | 301040    |
dev003       | userD    | 301040    |
dev003       | userE    | 301040    |
dev003       | userF    | 301040    |  <-- 三筆

需求

找出 channelId = '301040' 且 **同一 userDeviceId 對應 2 筆以上 userCode** 的所有記錄。

最佳 SQL

SELECT *
FROM `USER`
WHERE `userDeviceId` IN (
    SELECT `userDeviceId`
    FROM `USER`
    WHERE `channelId` = '301040'
    GROUP BY `userDeviceId`
    HAVING COUNT(*) >= 2
);

執行邏輯

1. 子查詢：先篩選 channelId='301040' 的資料，按 userDeviceId 分組，HAVING COUNT(*) >= 2 保留出現 ≥2 次的裝置 ID。
2. 外層查詢：撈取所有符合這些 userDeviceId 的完整記錄 (含 userCode 等欄位)。

效能優化建議

• 索引：CREATE INDEX idx_channel_device ON USER(channelId, userDeviceId); 覆蓋子查詢所需欄位，避免全表掃描。
• 大資料量改用 JOIN (MySQL 5.6+ 優化器通常能自動轉換，但寫法更明確)：

  SELECT u.*
  FROM `USER` u
  JOIN (
      SELECT `userDeviceId`
      FROM `USER`
      WHERE `channelId` = '301040'
      GROUP BY `userDeviceId`
      HAVING COUNT(*) >= 2
  ) d ON u.`userDeviceId` = d.`userDeviceId`;
  

• 只需統計數量**時，直接用子查詢 SELECT COUNT(DISTINCT userDeviceId) ...。

HAVING COUNT(*) = 1