MySQL结合来自同一方案表的联合的结果

问题描述：

有两个具有相同结构的表：'imsc_storage_users'&'imsc_storage_users_archive'。MySQL结合来自同一方案表的联合的结果

我现在的SELECT/UNION：

SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
FROM `imsc_storage_users` 
UNION DISTINCT 
SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
FROM `imsc_storage_users_archive` 
ORDER BY `correct` DESC,`minutes` ASC

我得到这些结果：

+--------------+---------------------+-----------+---------+---------+------+---------+ 
| cli   | ts     | questions | answers | correct | last | minutes | 
+--------------+---------------------+-----------+---------+---------+------+---------+ 
| 111111111111 | 2011-12-22 11:13:57 |  30 |  29 |  14 | 30 | 1305.47 | 
| 222222222222 | 2011-12-15 13:39:16 |  26 |  24 |  13 | 24 | 15.67 | 
| 333333333333 | 2011-12-15 13:39:39 |  26 |  25 |  11 | 25 | 15.18 | 
| 444444444444 | 2011-12-15 13:39:39 |  25 |  21 |  11 | 25 | 280.53 | 
| 111111111111 | 2011-12-22 11:13:57 |  25 |  21 |  10 | 25 | 373.87 | 
| 555555555555 | 2011-12-19 15:46:15 |  11 |  10 |  5 | 10 |  3.8 | 
| 666666666666 | 2011-12-15 13:39:16 |  14 |  10 |  4 | 10 | 321.64 | 
| 777777777777 | 2011-12-19 08:34:36 |  15 |  11 |  4 | 13 | 474.66 |

注意， '111111111111' 出现了两次？

我希望它被合并，所以在结果集中我得到一行'111111111111'，它合并/总和所有字段; 'questions'= >> 55 ....等'。

什么是正确的SQL？
性能在这里不是问题。

谢谢！

所以，当同样的'cli'有'ts'不同的值？你如何将它们结合起来？'MIN'？'MAX'会发生什么？ – Lamak 2012-01-05 13:54:01

答

SELECT `cli`,max(`ts`) AS ts, sum(`questions`) as questions, sum(`answers`) as answers,sum(`correct`) as correct,sum(`last`) as last,sum(`minutes`) as minutes 
FROM (
    SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
    FROM `imsc_storage_users` 
    UNION ALL 
    SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
    FROM `imsc_storage_users_archive` 
) AS baseview 
GROUP BY cli 
ORDER BY `correct` DESC,`minutes` ASC

请注意，UNION DISTINCT将消除两个表之间的任何确切重复项，即使它们可能应包含在总和值中。 – 2012-01-05 13:56:24

@MarkBannister先生，你有一只鹰的眼睛！适当纠正。 – 2012-01-05 13:57:42

Eugen：不完全（http://dev.mysql.com/doc/refman/5.0/en/union.html）;尝试UNION ALL。 – 2012-01-05 14:04:57

答

尝试：

SELECT `cli`, 
     `ts`, 
     sum(`questions`), 
     sum(`answers`), 
     sum(`correct`), 
     sum(`last`), 
     sum(`minutes`) 
FROM (SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
     FROM `imsc_storage_users` 
     UNION ALL 
     SELECT `cli`,`ts`,`questions`, `answers`,`correct`,`last`,`minutes` 
     FROM `imsc_storage_users_archive`) V 
group by `cli`, `ts` 
ORDER BY 5 DESC, 7 ASC

MySQL结合来自同一方案表的联合的结果

相关推荐