现有表如下
表a
create table a(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into a
select '1','5.12','2005-12-12','.1.2.3.'
union
select '1','6.3','2005-1-11','.4.'
union
select '2','11.02','2006-11-2','.5.'
union
select '2','235','2002-1-2','.6.'
union
select '3','1522','2006-8-24','.7.8.'
go
表b
create table b(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into b
select '1','222','2005-12-12','.1.'
union
select '1','22','2005-1-11','.2.'
union
select '2','2','2006-11-2','.3.'
go
表c
create table c(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into c
select '1','3','2005-12-12','.1.'
union
select '1','33','2005-1-11','.2.'
union
select '2','333','2006-11-2','.3.'
go
表a
create table a(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into a
select '1','5.12','2005-12-12','.1.2.3.'
union
select '1','6.3','2005-1-11','.4.'
union
select '2','11.02','2006-11-2','.5.'
union
select '2','235','2002-1-2','.6.'
union
select '3','1522','2006-8-24','.7.8.'
go
表b
create table b(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into b
select '1','222','2005-12-12','.1.'
union
select '1','22','2005-1-11','.2.'
union
select '2','2','2006-11-2','.3.'
go
表c
create table c(
id int not null,
price numeric(9,2) not null,
dt datetime not null,
zhs char(60)
)goinsert into c
select '1','3','2005-12-12','.1.'
union
select '1','33','2005-1-11','.2.'
union
select '2','333','2006-11-2','.3.'
go
id int not null,
dt datetime not null,
zhs int
)
go
insert into c
select '1','2005-12-12','1'
union
select '1','2005-1-11','2'
union
select '1','2005-1-11','8'
union
select '1','2005-1-11','3'go现需要求出
需要用main表的zhs于其他3个表的zhs关联
(如表a.zhs='.1.2.3.' 那么于表main关联后是3条记录)
关联后计算price 列的值
inner Join main as b on charindex('.'+b.zhs+'.',a.zhs)>0
inner Join main as b on charindex('.'+rtrim(b.zhs)+'.',rtrim(a.zhs))>0
Select sum(a.price) from a
inner Join main as b on charindex('.'+rtrim(b.zhs)+'.',a.zhs)>0
Select * from a
inner Join main as e on charindex('.'+rtrim(e.zhs)+'.',rtrim(a.zhs))>0
union
Select * from b
inner Join main as e on charindex('.'+rtrim(e.zhs)+'.',rtrim(b.zhs))>0
union
Select * from c
inner Join main as e on charindex('.'+rtrim(e.zhs)+'.',rtrim(c.zhs))>0
select * from b union all
select * from c
)d
inner Join main as b on charindex('.'+rtrim(b.zhs)+'.',d.zhs)>0
^_^
数据检索出来以后如果
ZHS的值有多个要根据时间来取最后的2个值的差如:zhs=3 那么取值为333-2=331谢谢大家了
drop table #T
Select tid=identity(int,1,1),d.* into #T from (select * from a union all
select * from b union all
select * from c
)d
inner Join main as b on charindex('.'+rtrim(b.zhs)+'.',d.zhs)>0
select * from #T
select (case when tt.value=1 then price
when tt.value=-1 then -price
end) as price
from
(select [value]=1,min(dt) as dt,zhs from #T group by zhs
union all
select [value]=-1,min(dt) as dt,zhs from #T where tid not in (select min(dt) from #T group by zhs)
group by zhs)tt left join #T on #T.dt=tt.dt and #T.zhs=tt.zhs数据有重复的可能会有问题,放弃了 呵呵