create table zhu ( id char(3) , zhu1 int)
create table ru ( id char(3) , ru int)
go
insert into zhu
select 004,30
UNION all select 001 , 30
UNION all select 002 , 40
UNION all select 003 , 50
go
insert into ru
select 001 , 8
UNION all select 001 , 10
UNION all select 001 , 5
UNION all select 002 , 20
UNION all select 002 , 14
UNION all select 003 , 16
UNION all select 003 , 26
UNION all select 004 , 11
UNION all select 004 , 12
UNION all select 004 , 30
--又是如何通过视图实现表ru中的编号001,002,003所对应的ru 字段的总和小于表zhu中
--相同编号所对应的ru字段的值呢?
select a.*
from ru a where a.id in (
select b.id from (select id ,sum(ru)as iru from ru group by id) b ,
zhu c
where b.iru < c.zhu1 and b.id = c.id
)
go
drop table zhu
drop table ru
go -------------------------
(4 row(s) affected)
(10 row(s) affected)id ru
---- ----------- 结果
1 8
1 10
1 5
2 20
2 14
3 16
3 26(7 row(s) affected)
create table ru ( id char(3) , ru int)
go
insert into zhu
select 004,30
UNION all select 001 , 30
UNION all select 002 , 40
UNION all select 003 , 50
go
insert into ru
select 001 , 8
UNION all select 001 , 10
UNION all select 001 , 5
UNION all select 002 , 20
UNION all select 002 , 14
UNION all select 003 , 16
UNION all select 003 , 26
UNION all select 004 , 11
UNION all select 004 , 12
UNION all select 004 , 30
--又是如何通过视图实现表ru中的编号001,002,003所对应的ru 字段的总和小于表zhu中
--相同编号所对应的ru字段的值呢?
select a.*
from ru a where a.id in (
select b.id from (select id ,sum(ru)as iru from ru group by id) b ,
zhu c
where b.iru < c.zhu1 and b.id = c.id
)
go
drop table zhu
drop table ru
go -------------------------
(4 row(s) affected)
(10 row(s) affected)id ru
---- ----------- 结果
1 8
1 10
1 5
2 20
2 14
3 16
3 26(7 row(s) affected)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货