练习题1
原表格:
要求处理后呈现如下:
结果如下
//创建表格
create table team (name char(8),result nchar(1),id serial);
insert into TEAM (name, result) values
(N'火箭',1),
(N'奇才',1),
(N'湖人',1),
(N'火箭',0),
(N'火箭',0),
(N'奇才',1)
;
=> select * from team;
NAME | RESULT | COUNTRY | ID
--------+--------+---------+----
奇才 | 胜 | CN | 4
奇才 | 胜 | CN | 6
湖人 | 胜 | CN | 5
火箭 | 胜 | CN | 3
火箭 | 败 | CN | 1
火箭 | 败 | CN | 2
(6 rows)
=> select name,
sum(case when result = N'胜' then 1 else 0 end) as ’胜‘,
sum(case when result = N'败' then 1 else 0 end) as ’败‘
from team group by name;
-> -> ->
NAME | ’胜‘ | ’败‘
--------+------+------
火箭 | 1 | 2
奇才 | 2 | 0
湖人 | 1 | 0
(3 rows)
练习题2:
原表格:
处理后呈现成以下格式:
结果:
操作方法如下:
//创建表
create table test1 (Ticket varchar(4),Amount int);
//写入参数
=> insert into test1 (Ticket, Amount) values
('tk1',10),
('tk2',20),
('tk3',-30),
('tk4',0),
('tk5',-10);
-> INSERT 0 5
//查表
=> select * from test1;
TICKET | AMOUNT
--------+--------
tk1 | 10
tk2 | 20
tk3 | -30
tk4 | 0
tk5 | -10
(5 rows)
//结果输出
=> select ticket,
(
case
when AMOUNT >= 0 then AMOUNT
else 0
end
) as income,
(
case
when AMOUNT >=0 then 0
else AMOUNT
end
) as expense
from test1;
-> TICKET | INCOME | EXPENSE
--------+--------+---------
tk1 | 10 | 0
tk2 | 20 | 0
tk3 | 0 | -30
tk4 | 0 | 0
tk5 | 0 | -10
(5 rows)```