我有一个名为的Sell表:有下列字段:
sellId roadId number seatType
1 T12 1 '硬座'
2 T12 1 '软座'
3 T12 1 '硬座'
4 T13 1 '硬座'
5 T13 1 '软座'
我要得到每个roadId的不同seatType的number的总和,就是按照roadId分组,还要判断seatType这个字段,我要的结果就是:
roadId seatType为硬座的number的和 seatType为软座的number的和
T12 2 1
T13 1 1要怎么做?请各位大哥帮帮忙,谢了。
sellId roadId number seatType
1 T12 1 '硬座'
2 T12 1 '软座'
3 T12 1 '硬座'
4 T13 1 '硬座'
5 T13 1 '软座'
我要得到每个roadId的不同seatType的number的总和,就是按照roadId分组,还要判断seatType这个字段,我要的结果就是:
roadId seatType为硬座的number的和 seatType为软座的number的和
T12 2 1
T13 1 1要怎么做?请各位大哥帮帮忙,谢了。
每个roleid都会有对应的软座、硬座么
如果没有得出什么结果
select 1,'T12',1,'硬座'
union
select 2,'T12',1,'软座'
union
select 3,'T12',1,'硬座'
union
select 4,'T13',1,'硬座'
union
select 5,'T13',1,'软座'select roadid,sum(case when seattype= '硬座' then number else 0 end) ,
sum(case when seattype= '软座' then number else 0 end)
from tmp_ll
group by roadid
group by roadId
如果是别的数据库
也许会不一样
结果
T12
2 1 T13
1 1
我也是看了好一会,
才明白原来就是一个行列转换
(SELECT SUM(NUMBER) FROM SEAT SS1 WHERE SEATTYPE='硬座' AND S1.ROADID = SS1.ROADID) AS N1,
(SELECT SUM(NUMBER) FROM SEAT SS2 WHERE SEATTYPE='软座' AND S1.ROADID = SS2.ROADID) AS N2
FROM SEAT S1假设表名为:SEAT 结果如下:
T12 2 1
T13 1 1