MYSQL 将结果从具有多行的多个子查询中分割出来

2022-01-23 00:00:00 subquery mysql

我有两个查询，第一个生成一个类似的表

I have two queries, the first one generating a table like

SELECT COUNT(channel) AS messages FROM Chats WHERE message LIKE '%word%' GROUP BY UNIX_TIMESTAMP(time) DIV 3600
+-----+
| 123 |
| 127 |
| 332 |
| 219 |
+-----+

第二个

SELECT COUNT(channel) AS messages FROM Chats GROUP BY UNIX_TIMESTAMP(time) DIV 3600
+-----+
| 222 |
| 579 |
| 590 |
| 377 |
+-----+

现在我想将第一个表中的所有结果除以第二个表中的相应值(结果四舍五入，必须手动计算):

Now I would like to divide all results from the first table by the corresponding value in the second one (results rounded, had to calculate it manually):

+------+
| 0.55 | #123/222
| 0.46 | #127/279
| 0.56 | #332/590
| 0.58 | #219/377
+------+

推荐答案

您可以通过以下查询来完成整个事情:

Here's one way you can accomplish the whole thing by the following query:

SELECT 
(dividendTable.messages / divisorTable.messages) AS result
FROM
(
    SELECT 
    firstTable.messages,
    @rn1 := @rn1 + 1 AS row_number
    FROM 
    (
        SELECT
            COUNT(channel) AS messages
        FROM Chats
        WHERE   message LIKE '%word%'
        GROUP BY    UNIX_TIMESTAMP(time) DIV 3600
    ) FirstTable, (SELECT @rn1 := 0) var1
) AS dividendTable
INNER JOIN
(   
    SELECT 
    secondTable.messages,
    @rn2 := @rn2 + 1 AS row_number
    FROM
    (
        SELECT 
            COUNT(channel) AS messages 
        FROM Chats 
        GROUP BY UNIX_TIMESTAMP(time) DIV 3600
    ) secondTable, (SELECT @rn2 := 0) var2
) AS divisorTable
ON dividendTable.row_number = divisorTable.row_number;

注意:

如果要将结果四舍五入到小数点后 2 位，则使用以下内容作为查询的第一行:

If you want the result rounded up to 2 digits after the decimal point then use the following as the first line of the query:

SELECT
ROUND((dividendTable.messages / divisorTable.messages),2) AS result

在这里演示

警告:您没有在查询中使用任何 ORDER BY.您可能会遇到随机行为.

Caution: You haven't used any ORDER BY in your query. You might get random behavior.

相关文章