sudo -u postgres psql sanguo -c "CREATE TABLE heroes(id serial, name VARCHAR(20),fight int,intelligence int,city VARCHAR(20),nation VARCHAR(20),items VARCHAR(20));"
sudo -u postgres psql sanguo -c "INSERT INTO heroes(name, fight, intelligence,nation,city,items) VALUES('刘备',60,65,'蜀','涿','双股剑'),('关羽',96,96,'蜀','解良','青龙偃月刀 赤兔马'),('张飞',98,60,'蜀','涿','丈八蛇矛'),('吕布','100','86','董卓','长安','方天画戟'),('马超','97','86','蜀','天水','铁骑尖'),('赵云','97','90','蜀','蓟','龙胆'),('典韦','96','57','魏','陈留','铁戟'),('许褚','96','65','魏','陈留',''),('甘宁','95','84','吴','建邺','双匕首'),('庞德','95','80','魏','天水',''),('黄忠','94','85','蜀','长沙','猛将弓'),('文丑','94','79','袁绍','蓟',''),('太史慈','93','82','吴','建邺',''),('华雄','92','82','董卓','长安',''),('孙策','92','92','吴','建邺','霸王枪'),('颜良','92','80','袁绍','蓟',''),('张辽','92','93','魏','蓟',''),('魏延','91','81','蜀','信阳',''),('夏侯渊','91','85','魏','陈留',''),('孙坚','90','93','吴','建邺',''),('文鸯','90','77','魏','蓟',''),('夏侯惇','90','89','魏','陈留','碎岩'),('周泰','90','77','吴','建邺',''),('曹彰','88','82','魏','许昌',''),('姜维','88','89','蜀','天水',''),('曹操','85','96','魏','陈留','孟德新书 玉玺 铜雀台赋'),('孙权','83','91','吴','建邺',''),('诸葛亮','60','98','蜀','南阳',''),('董卓','60','50','董卓','长安','魔王扑'),('袁绍','60','60','袁绍','长安','王道剑');"
\c sanguo
SELECT
city,
nation,
count(*)
FROM
heroes
WHERE
fight > 90
GROUP BY
nation,
city
ORDER BY
nation,
city
;
- 出生于南阳、隶属于蜀国的诸葛亮并没有被统计进来
- 这是什么原因
- 如果想要 修改使得能够统计出完整30名英雄的结果
- 应该怎么修改?