mysql - 查询mysql中的sum和distinct

Question

我的表中有这样的数据:

==================== 
class | in   | out 
==================== 
1       35      5
2       37      3

我想要这样的结果:

============================ 
Info  |   class    | total
      | 1   |   2  |
============================ 
in      35    37       72
out     5      3        8

那么如何在 mysql 中进行查询来获得这些结果呢？

请帮帮我，我已经尝试了很多次但没有成功

谢谢

Answer 1

使用UNION组合两个主元。

SELECT "in" AS Info,
       MAX(CASE WHEN class = 1 THEN `in` END) AS class1,
       MAX(CASE WHEN class = 2 THEN `in` END) AS class2,
       SUM(`in`) AS total
FROM yourTable
UNION
SELECT "out" AS Info,
       MAX(CASE WHEN class = 1 THEN `out` END) AS class1,
       MAX(CASE WHEN class = 2 THEN `out` END) AS class2,
       SUM(`out`) AS total
FROM yourTable

DEMO