mysql - 尝试从多个表和列中选择所有 ID，并按最匹配的顺序排序

Question

我想做的是选择所有匹配最多的 ID，并按照匹配次数最多的顺序列出它们

TABLE - SUNDAYID | 8AM | 9AM | 10AM | 11AMA  |  0  |  1  |  0   |  0 B  |  0  |  0  |  1   |  1 C  |  0  |  0  |  0   |  1TABLE - MONDAYID | 8AM | 9AM | 10AM | 11AMA | 0 | 0 | 1 | 1 B | 0 | 1 | 0 | 0 C | 0 | 0 | 0 | 1TABLE - TUESDAYID | 8AM | 9AM | 10AM | 11AMA | 0 | 1 | 0 | 0B | 0 | 1 | 0 | 0C | 0 | 0 | 0 | 1

例如:我想找到星期日上午 9 点和上午 11 点、星期一上午 10 点和上午 11 点以及星期二上午 9 点的所有 ID，然后按点击次数最多的顺序排序

我会得到以下返回。

• 4 次点击
• B 2 次命中
• C 2 次命中

Answer 1

首先，让我们通过查询将数据整理成更易于使用的内容:

SELECT ID, 0 AS dow, 8 as hr, `8AM` AS hits
FROM SUNDAY
UNION ALL
SELECT ID, 0 AS dow, 9 as hr, `9AM` AS hits
FROM SUNDAY
UNION ALL
SELECT ID, 0 AS dow, 10 as hr, `10AM` AS hits
FROM SUNDAY
UNION ALL
SELECT ID, 0 AS dow, 11 as hr, `11AM` AS hits
FROM SUNDAY
UNION ALL
SELECT ID, 1 AS dow, 8 as hr, `8AM` AS hits
FROM MONDAY
UNION ALL
SELECT ID, 1 AS dow, 9 as hr, `9AM` AS hits
FROM MONDAY
UNION ALL
SELECT ID, 1 AS dow, 10 as hr, `10AM` AS hits
FROM MONDAY
UNION ALL
SELECT ID, 1 AS dow, 11 as hr, `11AM` AS hits
FROM MONDAY
UNION ALL
SELECT ID, 2 AS dow, 8 as hr, `8AM` AS hits
FROM TUESDAY
UNION ALL
SELECT ID, 2 AS dow, 9 as hr, `9AM` AS hits
FROM TUESDAY
UNION ALL
SELECT ID, 2 AS dow, 10 as hr, `10AM` AS hits
FROM TUESDAY
UNION ALL
SELECT ID, 2 AS dow, 11 as hr, `11AM` AS hits
FROM TUESDAY

SQL Fiddle example of the data this returns

然后您可以从这个(派生的)表中进行选择:

SELECT ID, SUM(hits) AS hits
FROM
(
    --above query, either as a view or derived table
    --or permanently changed
) hitsTable
WHERE (dow = 0 AND hr IN (9,11))
OR (dow = 1 AND hr IN (10,11))
OR (dow = 2 AND hr = 9)
GROUP BY ID
ORDER BY SUM(hits) DESC

SQL Fiddle example

请注意此处 WHERE 子句的简单性。如果您可以控制您的架构，我会建议永久更改数据存储为这种格式的方式。它会让您的生活变得更轻松。如果不能，我建议至少制作一个以这种方式组合所有表的 View ，这样您就可以轻松地查询它们。

---

你能用你当前的模式来做吗？当然，它可能看起来“更短”或“更简单”，但如果您动态构建这些查询，它就不那么漂亮了:

SELECT ID, SUM(hits) AS hits
FROM
(
    SELECT ID, `9AM` + `11AM` AS hits
    FROM SUNDAY
    UNION ALL
    SELECT ID, `10AM` + `11AM` AS hits
    FROM MONDAY
    UNION ALL
    SELECT ID, `9AM` AS hits
    FROM TUESDAY
) x
GROUP BY ID
ORDER BY SUM(hits) DESC