本文展示了一个复杂的SQL查询案例，该查询从多个角度汇总特定编号的数据，并通过左连接和内连接的方式整合不同类型的文件信息。此外，还介绍了两个触发器的创建过程，用于在插入新记录到指定表前更新另一个表的有效性标记。
selec A.hblfile,B.hbltxlfile from
(select id,hblfile from file where number = '2012050002' group by hblfile having hblfile != '' order by id desc limit 1) A,
(select id,hbltxlfile from file where number = '2012050002' group by hbltxlfile having hbltxlfile != '' order by id desc limit 1) B
select F.number,A.hblfile,B.hbltxlfile,C.mblfile,D.mbltxlfile,E.loadingfile from file F
left join
(select id,number,hblfile from file where number = '2012050002' group by hblfile having hblfile != '' order by id desc limit 1) A
on F.number = A.number
left join
(select id,number,hbltxlfile from file where number = '2012050002' group by hbltxlfile having hbltxlfile != '' order by id desc limit 1) B
on F.number = B.number
inner join
(select id,number,mblfile from file where number = '2012050002' group by mblfile having mblfile != '' order by id desc limit 1) C
on F.number = C.number
left join
(select id,number,mbltxlfile from file where number = '2012050002' group by mbltxlfile having mbltxlfile != '' order by id desc limit 1) D
on F.number = D.number
left join
(select id,number,loadingfile from file where number = '2012050002' group by loadingfile having loadingfile != '' order by id desc limit 1) E
on F.number = E.number
where F.number = '2012050002' limit 1
create trigger Tupdate
before insert on cargo2
for each row
begin
declare @number varchar(30)
select @number=number from inserted
update cargo1 set valid = '1' where number = @number
end
create trigger Tupdate
before insert on cargo2
for each row
begin
declare @number
select @number = number for inserted
update cargo1 set valid = '1' where number = @number
end;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
