以c表,c_nbr为关连查询:
币别、单价、最后日期,先取B表对应的记录,以交易日期最后的一条为准,如果没有记录,取A表中对应记录。不知怎样写SQL好点的,谢谢
币别、单价、最后日期,先取B表对应的记录,以交易日期最后的一条为准,如果没有记录,取A表中对应记录。不知怎样写SQL好点的,谢谢
解决方案 »
- 求修改先进先出的分配sql语句
- 谁能解析一下这条语句?ROW_NUMBER()
- 如何查询表中与某一值相匹配的记录
- MSSQL 存储过程 转向DB2 存储过程 工具
- 求教ms_sql ServerManagement Studio新建查询失败咋办?
- 我的触发器总是过不去,请帮帮忙了~~多谢多谢(解决了就给分!)
- 很急,在线等待!!谁能帮我写一段:table1 关键字t, table2关键字t。当table1插入记录到table2中时,如果table2中关键字值有重复则覆盖
- sql server6.5的触发器问题
- sql语句:怎么备份数据库中的一个或几个表?
- ACCESS表的合并(VC)
- 这种语法是正确的吗?
- 请问怎么计算字段内数值的总和?
where A.C_nbr=(select max(b_nbr) from #tmp_asset2)
union
select A.*,'A表数据' as col from #tmp_asset3 A inner join #tmp_asset1 B on A.c_nbr=B.a_nbr
where A.C_nbr=(select max(a_nbr) from #tmp_asset1)--再取结果集的第一列,忘了咋写取中间结果集的数据了
select A.*,'B表数据' as col from #tmp_asset3 A inner join #tmp_asset2 B on A.c_nbr=B.b_nbr
where A.C_nbr=(select max(b_nbr) from #tmp_asset2)
union
select A.*,'A表数据' as col from #tmp_asset3 A inner join #tmp_asset1 B on A.c_nbr=B.a_nbr
where A.C_nbr=(select max(a_nbr) from #tmp_asset1)
) result
ALTER function Fitm_untprc (@c_nbr varchar(25))
returns @mytable table (itmnbr varchar(25),frmdt datetime, cur varchar(5),MQTY3 numeric(15,4))
as
begin
if exists( select top 1 b_nbr,b_dt,b_cur,b_untprc from b where b_nbr=@mitmnbr order by b_dt desc)
begin
insert into @mytable select top 1 b_nbr,b_dt,b_cur,b_untprc from b where b_nbr=@mitmnbr order by b_dt desc
end
else
begin
insert into @mytable select top 1 a_nbr,a_dt,a_cur,a_untprc from a where a_nbr=@mitmnbr
end
return
end
查询时:
select * from c Fitm_untprc(c_nbr)或 select *,(select * from Fitm_untprc(c_nbr)) from c
出错:
'c_nbr' is not a recognized OPTIMIZER LOCK HINTS option.
if (object_id('c')is not null) drop table c
create table c
(
c_nbr varchar(50),
c_cur varchar(50),
c_untprc varchar(20),
c_dt datetime
)
insert into c
select 'aa','rmb','1.3','2011-5-6'
union all
select 'bb','usd','1.4','2012-10-1'
union all
select 'cc','cc','usd','2012-10-1'
union all
select 'dd','rmb','22','2012-6-9'/*
(所影响的行数为 4 行)
*/
if(object_id('b')is not null) drop table b
create table b
(
b_nbr varchar(20),
b_cur varchar(20),
b_untprc decimal(18,1),
b_dt datetime
)
insert into b
select 'aa','rmb',1.3,'2011-5-6'
union all
select 'bb','usd',1.5,'2012-5-9'
union all
select 'bb','usd',1.4,'2012-10-1'
union all
select 'dd','rmb',21,'2011-12-12'
union all
select 'ee','rmb',24,'2012-4-9'
union all
select 'dd','rmb',22,'2012-6-9'if(object_id('a') is not null) drop table a
create table a
(
a_nbr varchar(20),
a_cur varchar(20) null,
a_untprc decimal(18,1) null,
a_dt datetime
)
insert into a
select 'aa','rmb',1.2,'2012-10-1'
union all
select 'bb','',0,'2012-10-1'
union all
select 'cc','usd',0.5,'2012-10-1'
union all
select 'dd','',0,'2012-10-1'
union all
select 'ee','',0,'2012-10-1'
union all
select 'ff','',0,'2012-10-1'alter table a
add id int identity(1,1)
alter table b
add id int identity(1,1)
alter table c
add id int identity(1,1)
select * from a
/*
aa rmb 1.2 2012-10-01 00:00:00.000 1
bb .0 2012-10-01 00:00:00.000 2
cc usd .5 2012-10-01 00:00:00.000 3
dd .0 2012-10-01 00:00:00.000 4
ee .0 2012-10-01 00:00:00.000 5
ff .0 2012-10-01 00:00:00.000 6
*/
select * from b
/*
aa rmb 1.3 2011-05-06 00:00:00.000 1
bb usd 1.5 2012-05-09 00:00:00.000 2
bb usd 1.4 2012-10-01 00:00:00.000 3
dd rmb 21.0 2011-12-12 00:00:00.000 4
ee rmb 24.0 2012-04-09 00:00:00.000 5
dd rmb 22.0 2012-06-09 00:00:00.000 6*/
select * from c
/*
aa rmb 1.3 2011-05-06 00:00:00.000 1
bb usd 1.4 2012-10-01 00:00:00.000 2
cc cc usd 2012-10-01 00:00:00.000 3
dd rmb 22 2012-06-09 00:00:00.000 4
*/select d.c_cur,
case when b.b_untprc is null then a.a_untprc else b.b_untprc end as untprc,
case when b.b_dt is null then a.a_dt else b.b_dt end as dt from
(
select c_nbr,c_cur,max(b.id) as id from c left join b on c.c_nbr=b.b_nbr
group by c_cur,c_nbr)as d left join b on d.c_nbr = b.b_nbr and d.id = b.id
inner join a on a.a_nbr = d.c_nbr
/*
rmb 1.3 2011-05-06 00:00:00.000
usd 1.4 2012-10-01 00:00:00.000
cc .5 2012-10-01 00:00:00.000
rmb 22.0 2012-06-09 00:00:00.000
*/
--第二种方法
select d.c_cur,
case when b.b_untprc is null then a.a_untprc else b.b_untprc end as untprc,
case when b.b_dt is null then a.a_dt else b.b_dt end as dt from
(
select c_nbr,c_cur,max(b.b_dt) as dt from c left join b on c.c_nbr=b.b_nbr
group by c_cur,c_nbr)as d left join b on d.c_nbr = b.b_nbr and d.dt = b.b_dt
inner join a on a.a_nbr = d.c_nbr
/*
rmb 1.3 2011-05-06 00:00:00.000
usd 1.4 2012-10-01 00:00:00.000
cc .5 2012-10-01 00:00:00.000
rmb 22.0 2012-06-09 00:00:00.000
*/