SQL 面试题(一)
2023-09-11 14:22:25 时间
问题来自于CSDN问答,练练SQL吧。
测试数据SQL代码:
if OBJECT_ID('td_ls_2') is not null drop table td_ls_2 go if OBJECT_ID('td_ls_1') is not null drop table td_ls_1 go create table td_ls_1 ( dh varchar(18) primary key, Id_gsjg int, Ymd_rz varchar(8), Id_ck int, Id_hyk int, Id_user int ) go create table td_ls_2 ( dh varchar(18) foreign key references td_ls_1(dh), Id_sp int, sl int, Dj_hs decimal(10,1), Je_hs decimal(10,1), Flag_cx varchar(4) ) go insert into td_ls_1 values('LS0010111052600010',2,'20110526',2,5,14); insert into td_ls_1 values('LS0010111052600012',2,'20110526',3,0,14); insert into td_ls_1 values('LS0010111052700016',1,'20110527',1,0,18); insert into td_ls_1 values('LS0010111052700018',2,'20110527',2,17,18); insert into td_ls_1 values('LS0010111052800022',1,'20110528',1,17,14); insert into td_ls_1 values('LS0010111052800023',2,'20110528',3,0,125); insert into td_ls_1 values('LS0010111052900026',3,'20110529',4,0,14); go insert into td_ls_2 values('LS0010111052600010',4053,1,8,8,'no'); insert into td_ls_2 values('LS0010111052600010',202,2,7.3,14.6,'no'); insert into td_ls_2 values('LS0010111052600012',2131,2,7.6,15.2,'tjcx'); insert into td_ls_2 values('LS0010111052600012',2104,1,16.9,16.9,'tjcx'); insert into td_ls_2 values('LS0010111052700016',404,20,1,20,'tjcx'); insert into td_ls_2 values('LS0010111052700018',2383,2,8.3,16.6,'no'); insert into td_ls_2 values('LS0010111052800022',377,2,1.6,3.2,'tjcx'); insert into td_ls_2 values('LS0010111052800022',3310,3,1.4,4.2,'no'); insert into td_ls_2 values('LS0010111052800022',404,25,1,25,'tjcx'); insert into td_ls_2 values('LS0010111052800023',2131,2,7.6,15.2,'tjcx'); insert into td_ls_2 values('LS0010111052900026',3310,2,1.4,2.8,'no');
答案:
1、
select a.Id_hyk as 会员卡,a.dh as 单号,a.Ymd_rz as 日期,b.Id_sp as 商品ID,b.Je_hs as 金额 from td_ls_1 a inner join td_ls_2 b on a.dh = b.dh order by 会员卡
2、
select a.Ymd_rz as 日期,sum(b.Je_hs) as 销售金额 from td_ls_1 a inner join td_ls_2 b on a.dh = b.dh where a.Id_gsjg = 2 group by a.Ymd_rz
3、有两种方法:1、可以设置主外键关联,这样删除主表时就能同时删除子表记录(级联删除);2、先删子表记录再删主表记录
if OBJECT_ID('aa') is not null drop table aa; with a as( select a.dh from td_ls_1 a inner join td_ls_2 b on a.dh = b.dh where a.Ymd_rz = '20110529' ) select * into aa from a; --先删子表再删主表 delete from td_ls_2 where dh in (select dh from aa); delete from td_ls_1 where dh in (select dh from aa);
4、
select a.Id_user as 收银员,sum(b.Je_hs) as 销售金额 from td_ls_1 a inner join td_ls_2 b on a.dh = b.dh group by Id_user order by 销售金额 desc
相关文章
- SQL案例分析-应用系统用户权限设计.sql
- SQL面试题
- 2022 最新 Java 基础 面试题(二)
- 网易面试题——SQL整理
- 机器学习面试题——KNN(K Nearest Neighbors)K近邻分类算法
- 2022年互联网企业性能测试面试题(出现的频率相当高)
- 软件测试面试题中的sql题目
- 120道java最常问面试题!
- 【C语言】动态内存面试题(三)
- 计算机研究生复试常见面试题——计算机组成原理
- sql语言实践之自学SQL网(SQL Lesson12)
- 实战案例:Sql client使用sql操作FlinkCDC2Hudi、支持从savepoint恢复hudi作业
- Vue面试题总结(1)
- MySQL面试题之如何优化一条有问题的SQL语句?
- 面试题:如果向一个数据库表中插入多条数据很慢,如何解决
- Geeks面试题:Min Cost Path
- [Oracle工程师手记] 利用 DBMS_SQLTUNE.report_sql_monitor 生成 SQL 语句的监控信息
- (1.2)sql server for linux 开启代理服务(SQL AGENT),使用T-SQL新建作业
- 如何查看正在执行sql的语句及其父语句调用?如何查看正在执行SQL的具体参数值与执行计划?xml执行计划转为图形计划
- 【sql server复制】教你使用SQL SERVER复制
- SQL CHECK sql server免费监控单实例工具
- sql-如何提高SQL查询的效率?