SQL语句视图的创建问题
创建一视图,能够显示所有货物的gno(编号)、name(名称)、unit(单位)、现有数量。createviewkanasselectgno,name,unit,(sel...
创建一视图,能够显示所有货物的gno(编号)、name(名称)、unit(单位)、现有数量。
create view kan
as
select gno,name,unit,(select sum(amount)
from goods_in
where goods_in.gno=goods.gno)-(select sum(amount)
from goods_out
where goods_out.gno=goods.gno) total
from goods
create view kan
as
select gno,name,unit,(select sum(amount) from goods_in group by gno having goods_in.gno=goods.gno)-(select sum(amount) from goods_out group by gno having goods_out.gno=goods.gno) total from goods;
你们看看这两个语句一个意思吗? 展开
create view kan
as
select gno,name,unit,(select sum(amount)
from goods_in
where goods_in.gno=goods.gno)-(select sum(amount)
from goods_out
where goods_out.gno=goods.gno) total
from goods
create view kan
as
select gno,name,unit,(select sum(amount) from goods_in group by gno having goods_in.gno=goods.gno)-(select sum(amount) from goods_out group by gno having goods_out.gno=goods.gno) total from goods;
你们看看这两个语句一个意思吗? 展开
展开全部
手工把你的2个SQL语句格式化一下以后
create view kan
as
select
gno,
name,
unit,
( select sum(amount)
from goods_in
where goods_in.gno=goods.gno
)-
( select sum(amount)
from goods_out
where goods_out.gno=goods.gno
) total
from goods
create view kan
as
select
gno,
name,
unit,
( select sum(amount)
from goods_in
group by gno
having goods_in.gno=goods.gno
)-
( select sum(amount)
from goods_out
group by gno
having goods_out.gno=goods.gno
) total
from goods;
发现差异在于,后面一个的 子查询里面 having 使用的不正确。
select sum(amount)
from goods_in
group by gno
having goods_in.gno=goods.gno
一般 having 是用于针对 分组统计后 的数据,进行进一步筛选的处理。
例如
having sum(amount) > 100
这种,在分组之前,无法用 WHERE 语句来筛选的。
而你这个SQL
having goods_in.gno=goods.gno
这个 goods_in.gno=goods.gno 是不需要分组以后,才能作筛选处理的。
分组处理以前的 where 就可以处理了。
create view kan
as
select
gno,
name,
unit,
( select sum(amount)
from goods_in
where goods_in.gno=goods.gno
)-
( select sum(amount)
from goods_out
where goods_out.gno=goods.gno
) total
from goods
create view kan
as
select
gno,
name,
unit,
( select sum(amount)
from goods_in
group by gno
having goods_in.gno=goods.gno
)-
( select sum(amount)
from goods_out
group by gno
having goods_out.gno=goods.gno
) total
from goods;
发现差异在于,后面一个的 子查询里面 having 使用的不正确。
select sum(amount)
from goods_in
group by gno
having goods_in.gno=goods.gno
一般 having 是用于针对 分组统计后 的数据,进行进一步筛选的处理。
例如
having sum(amount) > 100
这种,在分组之前,无法用 WHERE 语句来筛选的。
而你这个SQL
having goods_in.gno=goods.gno
这个 goods_in.gno=goods.gno 是不需要分组以后,才能作筛选处理的。
分组处理以前的 where 就可以处理了。
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询