SQL 面试题（一）

时间：2023-03-09 17:09:30
SQL 面试题（一）

问题来自于CSDN问答，练练SQL吧。

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,'',2,5,14);

insert into td_ls_1 values('LS0010111052600012',2,'',3,0,14);

insert into td_ls_1 values('LS0010111052700016',1,'',1,0,18);

insert into td_ls_1 values('LS0010111052700018',2,'',2,17,18);

insert into td_ls_1 values('LS0010111052800022',1,'',1,17,14);

insert into td_ls_1 values('LS0010111052800023',2,'',3,0,125);

insert into td_ls_1 values('LS0010111052900026',3,'',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 = ''

)

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

相关文章