sql联合查询语句(两张表)是:
select A.ID,A.VALUE,A.TYPE,A.NAME,B.KEY,B.ID,B.VALUE,B.NAME
min(VALUE),max(VALUE) from A left join B on A.ID = B.ID
where B.NAME="你输入的名字"
and B.VALUE >(select min(VALUE) from B where NAME="你输入的名字"))
and B.VALUE <(select min(VALUE) from B where NAME="你输入的名字"));
延展阅读:
A表字段stuid,stuname。
B表字段bid,stuid,score,coursename,status。
要用一条sql查出A表中所有记录的对应的stuid,max(score),coursename,status,并且status=1,sql语句要求跨数据库,不能使用rownum,top,limit等方言。
比如数据:
A
select t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdatefrom pch1 t0
left join opch t1 on t0.[DocEntry] =t1.[DocEntry]
where
t1.docdate<'2017-12-01' --条件1
and t0.itemcode='GD01002' -----条件2
union ----关键部分,字段一样时,可以通过union链接成一个语句,当部分查询字段没有时,可以根据类型补空或者0
select --t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
from ign1 t0
left join oign t1 on t0.[DocEntry] =t1.[DocEntry]
WHERE
t1.docdate<'2017-12-01' --条件1
and t0.itemcode='GD01002' -----条件2
group by t0.itemcode
……--后面继续就行 --第二种,建临时表
if(object_id('temp..#a') > 0)
drop table #a
create table #a
(
itemcode varchar(100),
quantity int,
docstatus int,
canceled int,
docdate date
)
insert into #a(quantity,docstatus,docstatus,canceled ,docdate)
select t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
from pch1 t0
left join opch t1 on t0.[DocEntry] =t1.[DocEntry]
where
t1.docdate<'2017-12-01' --条件1
and t0.itemcode='GD01002' -----条件2
insert into #a(quantity,docstatus,docstatus,canceled ,docdate)
select --t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
from ign1 t0
left join oign t1 on t0.[DocEntry] =t1.[DocEntry]
WHERE
t1.docdate<'2017-12-01' --条件1
and t0.itemcode='GD01002' -----条件2
group by t0.itemcode
……--继续插入数据
--最后查询
select * from #a --关于存储过程
Create proc sp_Test
(
@d date,
@code varchar(100)
)
as
begin
--这里只放一个语句,用于参数的示例,只需要将上面的语句放到存储过程中,并将参数替换就可以了
select --t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
from ign1 t0
left join oign t1 on t0.[DocEntry] =t1.[DocEntry]
WHERE
t1.docdate<@d --条件1
and t0.itemcode=@code -----条件2
group by t0.itemcode
end
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)