SQL 面试题（一）

问题来自于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