现在向一个表插入如下数据insert into lifei(NumID,B_name,yesno)values(1,'曼城',0)
insert into lifei(NumID,B_name,yesno)values(2,'法兰克福',0)
insert into lifei(NumID,B_name,yesno)values(3,'曼城',1)
insert into lifei(NumID,B_name,yesno)values(4,'法兰克福',1)
insert into lifei(NumID,B_name,yesno)values(3,'皇马',1)
insert into lifei(NumID,B_name,yesno)values(4,'曼城',1)然后用查询语句,把它的结果查出来如下格式
名称 胜 负
法兰克福 1 1
皇马 2 0
曼城 3 1实现这样,应该怎么写呢?
insert into lifei(NumID,B_name,yesno)values(2,'法兰克福',0)
insert into lifei(NumID,B_name,yesno)values(3,'曼城',1)
insert into lifei(NumID,B_name,yesno)values(4,'法兰克福',1)
insert into lifei(NumID,B_name,yesno)values(3,'皇马',1)
insert into lifei(NumID,B_name,yesno)values(4,'曼城',1)然后用查询语句,把它的结果查出来如下格式
名称 胜 负
法兰克福 1 1
皇马 2 0
曼城 3 1实现这样,应该怎么写呢?
sum(case when yesno=1 then 1 else 0 end) as 胜,
sum(case when yesno=0 then 1 else 0 end) as 负
from lifei
group by name