面试高频sql问题
2021/11/15 2:11:53
本文主要是介绍面试高频sql问题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
第一题:
create table demo1( rq date, shfu VARCHAR(20) ); insert into demo1(rq,shfu) VALUES ('2005-05-09','胜'), ('2005-05-09','胜'), ('2005-05-09','负'), ('2005-05-09','负'), ('2005-05-10','胜'), ('2005-05-10','负'), ('2005-05-10','负'); select * from demo1; select rq,count(case shfu when '胜' then 1 end) as 胜,count(case shfu when '负' then 1 end) as 负 from demo1 GROUP BY rq
第二题:
create table demo2( yuw int, shx int, yy int ); INSERT INTO demo2(yuw,shx,yy) VALUES (70,80,58) SELECT * from demo2 select (case when yuw>=80 then '优秀' when yuw>=60 and yuw<80 then'及格' else '不及格' end ) as 语文, (case when shx>=80 then '优秀' when shx>=60 and shx<80 then'及格' else '不及格' end ) as 数学, (case when yy>=80 then '优秀' when yy>=60 and yy<80 then'及格' else '不及格' end ) as 英语
第三题:
create table demo3( id int PRIMARY key AUTO_INCREMENT, rq date, pm VARCHAR(20), shl int ) INSERT INTO demo3(rq,pm,shl) VALUES ('2005-05-12','火腿肠',500), ('2005-05-12','方便面',100), ('2005-05-12','方便面',200), ('2005-05-13','矿泉水',200), ('2005-05-13','可乐',150), ('2005-05-14','可乐',50), ('2005-05-14','矿泉水',300) select * from demo3 #1.各种商品共有多少 最大一笔和最小一笔入库分别是多少 select pm,count(pm), max(shl),min(shl) FROM demo3 GROUP BY pm
#2.显示各种物品最后一次记录 select pm,max(rq) from demo3 GROUP BY pm
第四题
create table AAA( mc VARCHAR(20), sl int ) create table BBB( mc VARCHAR(20), sl int ) INSERT into AAA(mc,sl) VALUES ('A',100), ('B',120) insert into BBB(mc,sl) VALUES ('A',10), ('A',20), ('B',10), ('B',20), ('B',30) select * from AAA select * from BBB #用一条sql语句算出商品A,B还剩多少 select a.mc,a.sl-(select sum(b.sl) from BBB b WHERE b.mc= a.mc) as sl from AAA a GROUP BY a.mc
第五题:
#sql语句计算SPH00017453 + 5000的结果SPH00022453 怎么写 select concat(substring('SPH00017453',1,6),17453+5000)```
这篇关于面试高频sql问题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15鸿蒙生态设备数量超8亿台
- 2024-05-13TiDB + ES:转转业财系统亿级数据存储优化实践
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?