select * from a,b,c where a.id=b.id and a.id=c.id --這個算嗎
select a.* from ta a join tb b on a.f1=b.f2 join tc c on b.f3=c.f4
四表联查问题: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
--学生的例子 s学生表 sc 成绩表 c 课程表select sname,cname,grade from s join sc on s.sno=sc.sno join c on sc.cno=c.cno
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID LEFT JOIN C ON A.ID=C.ID
--四表连接 select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
union union all left join
declare @ta table (id int,va varchar(10)) declare @tb table (id int,vb varchar(10))insert into @ta select 1,'aa' insert into @ta select 2,'bc' insert into @ta select 3,'ccc'insert into @tb select 1,'2' insert into @tb select 3,'58' insert into @tb select 4,'67' --内连接简单写法select a.id,a.va,b.id,b.vb from @ta a,@tb b where a.id=b.id--内连接select a.id,a.va,b.id,b.vb from @ta a inner join @tb b on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a join @tb b on a.id=b.id--左连接(左外连接) --返回left join 子句中指定的左表的所有行,以及右表所匹配的行。select a.id,a.va,b.id,b.vb from @ta a left join @tb b on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a left outer join @tb b on a.id=b.id--右连接(右外连接) --返回right join 子句中指定的右表的所有行,以及左表所匹配的行。select a.id,a.va,b.id,b.vb from @ta a right join @tb b on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a right outer join @tb b on a.id=b.id--完整外连接 --等同左连接+右连接select a.id,a.va,b.id,b.vb from @ta a full join @tb b on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a full outer join @tb b on a.id=b.id --交叉连接 --没有两个表之间关系的交叉连接,将产生连接所涉及的表的笛卡尔积。select a.id,a.va,b.id,b.vb from @ta a cross join @tb bselect a.id,a.va,b.id,b.vb from @ta a,@tb b--自连接 --一个表和其本身连接。select a.id,a.va,b.id,b.va from @ta a,@ta b where a.id=b.id+1
三个表 t1商品表 id size num date 1 l 12 2007-9-1 2 m 12 2007-9-1 t2 商品出货明晰表 id shangpin num date 1 1 12 2007-9-19 2 1 12 2007-9-20 3 1 2 2007-9-20 t3 商品入库明晰表 id shangpin num date 1 1 20 2007-9-20 2 1 20 2007-9-20 如何通过sql语句查询到 2007-9-20这一天1商品出货,和入库多少 也就是得出以下结果 id 商品 出货 入库 时间 1 1 14(12+2) 40(20+20) 2007-9-20 2 2 0 0 2007-9-20 -----------------------------------------------create table t1(id int, size varchar(10), num int, date datetime) insert into t1 values(1, 'l ', 12, '2007-9-1 ') insert into t1 values(2, 'm ', 12, '2007-9-1 ') create table t2(id int, shangpin int, num int, date datetime) insert into t2 values(1, 1, 12, '2007-9-19 ') insert into t2 values(2, 1, 12, '2007-9-20 ') insert into t2 values(3, 1, 2, '2007-9-20 ') create table t3(id int, shangpin int, num int, date datetime) insert into t3 values(1, 1, 20, '2007-9-20 ') insert into t3 values(2, 1, 20, '2007-9-20 ') go select t1.id,isnull(m.num,0) 出货,isnull(n.num,0) 入库 , 时间 = '2007-09-20 ' from t1 left join (select shangpin,sum(num) num from t2 where date = '2007-09-20 ' group by shangpin) m on t1.id = m.shangpin left join (select shangpin,sum(num) num from t3 where date = '2007-09-20 ' group by shangpin) n on t1.id = n.shangpin drop table t1,t2,t3 /* id 出货 入库 时间 ----------- ----------- ----------- ---------- 1 14 40 2007-09-20 2 0 0 2007-09-20 (所影响的行数为 2 行)
--這個算嗎
select a.* from ta a
join tb b on a.f1=b.f2
join tc c on b.f3=c.f4
select * from a
left inner join b on
a.a=b.b right
inner join c on
a.a=c.c inner
join d on
a.a=d.d where .....
from s join sc on s.sno=sc.sno
join c on sc.cno=c.cno
select * from a
left inner join b on a.a=b.b
right inner join c on a.a=c.c
inner join d on a.a=d.d where .....
union
union all
left join
declare @tb table (id int,vb varchar(10))insert into @ta select 1,'aa'
insert into @ta select 2,'bc'
insert into @ta select 3,'ccc'insert into @tb select 1,'2'
insert into @tb select 3,'58'
insert into @tb select 4,'67' --内连接简单写法select a.id,a.va,b.id,b.vb from @ta a,@tb b
where a.id=b.id--内连接select a.id,a.va,b.id,b.vb from @ta a inner join @tb b
on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a join @tb b
on a.id=b.id--左连接(左外连接)
--返回left join 子句中指定的左表的所有行,以及右表所匹配的行。select a.id,a.va,b.id,b.vb from @ta a left join @tb b
on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a left outer join @tb b
on a.id=b.id--右连接(右外连接)
--返回right join 子句中指定的右表的所有行,以及左表所匹配的行。select a.id,a.va,b.id,b.vb from @ta a right join @tb b
on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a right outer join @tb b
on a.id=b.id--完整外连接
--等同左连接+右连接select a.id,a.va,b.id,b.vb from @ta a full join @tb b
on a.id=b.idselect a.id,a.va,b.id,b.vb from @ta a full outer join @tb b
on a.id=b.id
--交叉连接
--没有两个表之间关系的交叉连接,将产生连接所涉及的表的笛卡尔积。select a.id,a.va,b.id,b.vb from @ta a cross join @tb bselect a.id,a.va,b.id,b.vb from @ta a,@tb b--自连接
--一个表和其本身连接。select a.id,a.va,b.id,b.va from @ta a,@ta b where a.id=b.id+1
t1商品表
id size num date
1 l 12 2007-9-1
2 m 12 2007-9-1
t2 商品出货明晰表
id shangpin num date
1 1 12 2007-9-19
2 1 12 2007-9-20
3 1 2 2007-9-20
t3 商品入库明晰表
id shangpin num date
1 1 20 2007-9-20
2 1 20 2007-9-20
如何通过sql语句查询到
2007-9-20这一天1商品出货,和入库多少
也就是得出以下结果
id 商品 出货 入库 时间
1 1 14(12+2) 40(20+20) 2007-9-20
2 2 0 0 2007-9-20
-----------------------------------------------create table t1(id int, size varchar(10), num int, date datetime)
insert into t1 values(1, 'l ', 12, '2007-9-1 ')
insert into t1 values(2, 'm ', 12, '2007-9-1 ')
create table t2(id int, shangpin int, num int, date datetime)
insert into t2 values(1, 1, 12, '2007-9-19 ')
insert into t2 values(2, 1, 12, '2007-9-20 ')
insert into t2 values(3, 1, 2, '2007-9-20 ')
create table t3(id int, shangpin int, num int, date datetime)
insert into t3 values(1, 1, 20, '2007-9-20 ')
insert into t3 values(2, 1, 20, '2007-9-20 ')
go select t1.id,isnull(m.num,0) 出货,isnull(n.num,0) 入库 , 时间 = '2007-09-20 ' from t1
left join (select shangpin,sum(num) num from t2 where date = '2007-09-20 ' group by shangpin) m on t1.id = m.shangpin
left join (select shangpin,sum(num) num from t3 where date = '2007-09-20 ' group by shangpin) n on t1.id = n.shangpin drop table t1,t2,t3 /*
id 出货 入库 时间
----------- ----------- ----------- ----------
1 14 40 2007-09-20
2 0 0 2007-09-20 (所影响的行数为 2 行)