SQL查询统计

Question

create table test
(
  tId int identity(1001,1),
  tType int,
  tWeek int,
  tTotal int
)insert into test(tType,tWeek,tTotal)values(1,1,5)
insert into test(tType,tWeek,tTotal)values(2,1,2)insert into test(tType,tWeek,tTotal)values(1,2,3)
insert into test(tType,tWeek,tTotal)values(2,2,4)insert into test(tType,tWeek,tTotal)values(1,3,2)
insert into test(tType,tWeek,tTotal)values(2,3,1)insert into test(tType,tWeek,tTotal)values(1,4,6)
insert into test(tType,tWeek,tTotal)values(2,4,7)select * from test要求查询统计：
tWeek=1,2，tType相同记录的tTotal总分，
tWeek=3,4，tType相同记录的tTotal总分，
select sum(tTotal) from test where tType=1 and tWeek in (1,2) group by tType 这只能取出tType=1，并tWeek在1,2时，它的总分，结果类似：
tType_1_Week(1,2)_tTotal |  tType_2_Week(1,2)_tTotal   |   tType_1_Week(3,4)_tTotal   |  tType_2_Week(3,4)_tTotal       8                              6                            8                                    8现希望能实现查询结果为一条记录得到所有tType分别在1,2和3,4时,tTotal的总值，因为实际中数据比较多，而且有相联表，所以希望能有效率较高的SQL统计查询。

Answer 1

...你这是mssql吧 mysql自增列不是你这么写的...下面是mysql的正确写法drop table if exists test;create table test
(
  tId int not null auto_increment primary key,
  tType int,
  tWeek int,
  tTotal int
);
insert into test(tType,tWeek,tTotal)values(1,1,5);
insert into test(tType,tWeek,tTotal)values(2,1,2);insert into test(tType,tWeek,tTotal)values(1,2,3);
insert into test(tType,tWeek,tTotal)values(2,2,4);insert into test(tType,tWeek,tTotal)values(1,3,2);
insert into test(tType,tWeek,tTotal)values(2,3,1);insert into test(tType,tWeek,tTotal)values(1,4,6);
insert into test(tType,tWeek,tTotal)values(2,4,7);select sum(case when tWeek in(1,2) and tType =1 then tTotal end) as `tType_1_Week(1,2)_tTotal`,
sum(case when tWeek in(3,4)  and tType =1 then tTotal end) as `tType_1_Week(3,4)_tTotal`,
sum(case when tWeek in(1,2)  and tType =2 then tTotal end) as `tType_2_Week(1,2)_tTotal`,
sum(case when tWeek in(3,4)  and  tType =2 then tTotal end) as `tType_2_Week(3,4)_tTotal`
from test ;
+--------------------------+--------------------------+--------------------------+--------------------------+
| tType_1_Week(1,2)_tTotal | tType_1_Week(3,4)_tTotal | tType_2_Week(1,2)_tTotal | tType_2_Week(3,4)_tTotal |
+--------------------------+--------------------------+--------------------------+--------------------------+
|                        8 |                        8 |                        6 |                        8 |
+--------------------------+--------------------------+--------------------------+--------------------------+

Answer 2

如果你是mssql也可以这么些 
select sum(case when tWeek in(1,2) and tType =1 then tTotal end) as `tType_1_Week(1,2)_tTotal`,
sum(case when tWeek in(3,4)  and tType =1 then tTotal end) as tType_1_Week(3,4)_tTotal,
sum(case when tWeek in(1,2)  and tType =2 then tTotal end) as tType_2_Week(1,2)_tTotal,
sum(case when tWeek in(3,4)  and  tType =2 then tTotal end) as tType_2_Week(3,4)_tTotal
from test ;