Two ~~~~~ 求一个 select sql

Question

表  A  
id       type  store   username
12 type1 2 Tom
4 type2 2 Tom
5 type1 2 Tom
6 type1 2 Tom
11 type1 2 Joye
1 type1 2 Joye
3 type3 2 Joye
2 type2 2 Joye
8 type1 2 Han
9 type1 2 Han
10 type1 2 Han
13 type2 2 Lan
7 type3 2 Hanid  字段可以不用管 以下是问题  得到如下的结果集username  type1  type2 type3  sum
Joye       4       2     2     8
Tom        6       2     0     8
Han        6       0     2     8
Lan        0       2     0     2

Answer 1

请用 标准Sql  谢谢

Answer 2

SELECT username, type1, type2, type3, type1 + type2 + type3 SUM
  FROM (SELECT   username, SUM (CASE
                                   WHEN TYPE = 'type1'
                                      THEN STORE
                                   ELSE 0
                                END) type1,
                 SUM (CASE
                         WHEN TYPE = 'type2'
                            THEN STORE
                         ELSE 0
                      END) type2,
                 SUM (CASE
                         WHEN TYPE = 'type3'
                            THEN STORE
                         ELSE 0
                      END) type3
            FROM a
        GROUP BY username)

Answer 3

不好意思。刚忙的呢写了个decode的。
select username,
       sum(decode(type,'type1', store, 0)) type1,
       sum(decode(type,'type2', store, 0)) type2,
       sum(decode(type,'type3', store, 0)) type3,
       sum(store)
  from tet
 group by username
也可以用case when实现。

Answer 4

SELECT   username, SUM (CASE
                                   WHEN TYPE = 'type1'
                                      THEN STORE
                                   ELSE 0
                                END) type1,
                 SUM (CASE
                         WHEN TYPE = 'type2'
                            THEN STORE
                         ELSE 0
                      END) type2,
                 SUM (CASE
                         WHEN TYPE = 'type3'
                            THEN STORE
                         ELSE 0
                      END) type3，
                    sum(store) as sum
            FROM a
        GROUP BY username
应该也可以吧！

Answer 5

select username sum(decode(type,'type1',score,0)) type1,.....,sum(store) sum from a group by username