三个数据表aa(含字段bh ,mc,sl,rksj)、bb(含字段 bh ,mc,sl,rksj,cksj)cc(含字段 bh ,mc,sl,rksj,cksj),想实现以下功能
1、insert into cc(bh ,mc,sl,rksj)select * from aa where rksj between '2009-02-01' and '2009-02-28';
2、insert into cc select * from bb a where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
3、对cc表中的相同编号进行整合,同时所有记录的sl项进行sum(sl)以上这几个功能能否用一条sql语句实现
1、insert into cc(bh ,mc,sl,rksj)select * from aa where rksj between '2009-02-01' and '2009-02-28';
2、insert into cc select * from bb a where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
3、对cc表中的相同编号进行整合,同时所有记录的sl项进行sum(sl)以上这几个功能能否用一条sql语句实现
解决方案 »
- win7 sql server 2005 安装 及存储过程与视图的辩证
- exec('create table '+'#tt varchar(20)')非常奇怪???
- [攒分贴] 根据CID号取得户籍所在地第二版
- 如何给存储过程传递参数?
- =====非常奇怪的sql select语句问题====
- 请教高手数据转移问题
- 请教一个Tree结构查询的问题~
- 记事本文件批量导入到SQL 2000数据库的办法?
- 急死我了!!!!!!!
- 在sqlserver中怎么设置sql mail,使它可以用来发信?
- 给一个子查询出来的列 取别名的语法
- sql server 2000与access数据之间的快速导入问题,请各位高手指点,在线等,急,急,,急,,,
select * from bb a
where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
union all
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
(
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31')
)
group by bh,mc,rksj,cksj
(
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31')
)
group by bh,mc,rksj,cksj 楼上的高手们,怎么老提示“第 6 行: ')' 附近有语法错误”
(
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31')
)AS T
group by bh,mc,rksj,cksj加个别名
(
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where
exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
)AS T
group by bh,mc,rksj,cksj楼主仔细检查一下呀,你后面多了一个括号了,
insert into cc select bh,mc,sum(sl),rksj,cksj from
(
select *,null from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where
exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
)AS T
group by bh,mc,rksj,cksj
没有为第 5 列(属于 'T')指定列,去掉as T后提示“在关键字 'group' 附近有语法错误”
(
select *,null AS cksj from aa where rksj between '2009-02-01' and '2009-02-28'
union all
select * from bb a where
exists(select 1 from aa b where a.bh=b.bh and a.rksj=b.rksj and b.rksj between '2009-02-01' and '2009-02-28') and a.cksj between '2009-02-01' and '2009-05-31'
)AS T
group by bh,mc,rksj,cksj没测试,好多小问题看不出来,哎,功力不够啊我
你谦虚了,在我们心目中就是高高人,因本人初学,斗胆再问一句,如果sum(sl)改为两个表查询的结果记录中sl字段相减又应如何改
UNION ALL
SELECT -A,B FROM B
在最后SUM的时候就变成减了呀,