create table baoguan(外销发票号 varchar(20),核销单号 varchar(20),报关金额 decimal(18,2))
insert into baoguan values('126HFGY10001', '715890166', 17228.16)
insert into baoguan values('126HFGY10002', '715911629', 6372)
create table shoukuan(核销单号 varchar(20),收汇日 datetime,收汇金额 decimal(18,2),汇率 decimal(18,4),结汇 decimal(18,2))
insert into shoukuan values('715890166','2007-02-22', 10879.38 ,6.8132 ,74123.39)
insert into shoukuan values('715890166','2010-01-22', 6323 ,6.8258 ,43159.53)
insert into shoukuan values('715911629','2010-02-11', 1901.6 ,6.8121 ,12955.98)
insert into shoukuan values('715911629','2010-04-02', 4450.4 ,6.8121 ,30316.57)
insert into shoukuan values('715911629','2010-06-25', 10000 ,6.8121 ,68258)
create table fukuan(核销单号 varchar(20),付款日 datetime,已付金额 decimal(18,2))
insert into fukuan values('715890166','2010-05-22',10000)
insert into fukuan values('715890166','2010-06-22',879.38)
insert into fukuan values('715890166','2010-07-22',6323)
insert into fukuan values('715911629','2010-05-25',110955)
insert into fukuan values('715911629','2010-06-25',258.55)以上是测试数据，查询代码如下：select case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b1 where b1.核销单号 = a1.核销单号 and b1.收汇日<>'' order by b1.收汇日) then a1.外销发票号
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c1 where c1.核销单号 = a1.核销单号 and c1.付款日<>'' order by c1.付款日) then a1.外销发票号 else '' end 外销发票号,
case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b2 where b2.核销单号 = a1.核销单号 and b2.收汇日<>'' and b2.收汇日 is not null order by b2.收汇日) then a1.核销单号
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c2 where c2.核销单号 = a1.核销单号 and c2.付款日<>'' order by c2.付款日) then a1.核销单号 else '' end 核销单号,
case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b3 where b3.核销单号 = a1.核销单号 and b3.收汇日<>'' and b3.收汇日 is not null order by b3.收汇日) then cast(a1.报关金额 as varchar)
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c3 where c3.核销单号 = a1.核销单号 and c3.付款日<>'' order by c3.付款日) then cast(a1.报关金额 as varchar) else '' end 报关金额,
收汇日,收汇金额,汇率,结汇,isnull(convert(varchar(10),付款日,120),'') 付款日,已付金额
from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) a1
order by a1.收汇日,a1.付款日 drop table baoguan,shoukuan,fukuan想得到如下结果：外销发票号 核销单号 报关金额 收汇日 收汇金额 汇率 结汇 付款日 已付金额
126HFGY10001 715890166 17228.16 2007-02-22 10879.38 6.8132 74123.39 2010-05-22 10000.00
2010-01-22 6323.00 6.8258 43159.53 2010-06-22 879.38
2010-07-22 6323.00
小结 17202.38 117282.92 17202.38
126HFGY10002 715911629 6372.00 2010-02-11 1901.60 6.8121 12955.98 2010-05-25 110955.00
2010-04-02 4450.40 6.8121 30316.57 2010-06-25 258.55
2010-06-25 10000.00 6.8121 68258.00
小结 16352.00 111530.55 111213.55
insert into baoguan values('126HFGY10001', '715890166', 17228.16)
insert into baoguan values('126HFGY10002', '715911629', 6372)
create table shoukuan(核销单号 varchar(20),收汇日 datetime,收汇金额 decimal(18,2),汇率 decimal(18,4),结汇 decimal(18,2))
insert into shoukuan values('715890166','2007-02-22', 10879.38 ,6.8132 ,74123.39)
insert into shoukuan values('715890166','2010-01-22', 6323 ,6.8258 ,43159.53)
insert into shoukuan values('715911629','2010-02-11', 1901.6 ,6.8121 ,12955.98)
insert into shoukuan values('715911629','2010-04-02', 4450.4 ,6.8121 ,30316.57)
insert into shoukuan values('715911629','2010-06-25', 10000 ,6.8121 ,68258)
create table fukuan(核销单号 varchar(20),付款日 datetime,已付金额 decimal(18,2))
insert into fukuan values('715890166','2010-05-22',10000)
insert into fukuan values('715890166','2010-06-22',879.38)
insert into fukuan values('715890166','2010-07-22',6323)
insert into fukuan values('715911629','2010-05-25',110955)
insert into fukuan values('715911629','2010-06-25',258.55)以上是测试数据，查询代码如下：select case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b1 where b1.核销单号 = a1.核销单号 and b1.收汇日<>'' order by b1.收汇日) then a1.外销发票号
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c1 where c1.核销单号 = a1.核销单号 and c1.付款日<>'' order by c1.付款日) then a1.外销发票号 else '' end 外销发票号,
case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b2 where b2.核销单号 = a1.核销单号 and b2.收汇日<>'' and b2.收汇日 is not null order by b2.收汇日) then a1.核销单号
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c2 where c2.核销单号 = a1.核销单号 and c2.付款日<>'' order by c2.付款日) then a1.核销单号 else '' end 核销单号,
case when 收汇日 = (select top 1 收汇日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) b3 where b3.核销单号 = a1.核销单号 and b3.收汇日<>'' and b3.收汇日 is not null order by b3.收汇日) then cast(a1.报关金额 as varchar)
when 付款日 = (select top 1 付款日 from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) c3 where c3.核销单号 = a1.核销单号 and c3.付款日<>'' order by c3.付款日) then cast(a1.报关金额 as varchar) else '' end 报关金额,
收汇日,收汇金额,汇率,结汇,isnull(convert(varchar(10),付款日,120),'') 付款日,已付金额
from
(
select t1.*,t2.收汇日,t2.收汇金额,t2.汇率,t2.结汇,t3.付款日,t3.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
union
select t1.*,t3.收汇日,t3.收汇金额,t3.汇率,t3.结汇,t2.付款日,t2.已付金额 from baoguan t1 left join
(select t.* , px = (select count(1) from fukuan where 核销单号 = t.核销单号 and 付款日 < t.付款日) + 1 from fukuan t) t2 on t1.核销单号 = t2.核销单号 left join
(select t.* , px = (select count(1) from shoukuan where 核销单号 = t.核销单号 and 收汇日 < t.收汇日) + 1 from shoukuan t) t3 on t1.核销单号 = t3.核销单号 and t2.px = t3.px
) a1
order by a1.收汇日,a1.付款日 drop table baoguan,shoukuan,fukuan想得到如下结果：外销发票号 核销单号 报关金额 收汇日 收汇金额 汇率 结汇 付款日 已付金额
126HFGY10001 715890166 17228.16 2007-02-22 10879.38 6.8132 74123.39 2010-05-22 10000.00
2010-01-22 6323.00 6.8258 43159.53 2010-06-22 879.38
2010-07-22 6323.00
小结 17202.38 117282.92 17202.38
126HFGY10002 715911629 6372.00 2010-02-11 1901.60 6.8121 12955.98 2010-05-25 110955.00
2010-04-02 4450.40 6.8121 30316.57 2010-06-25 258.55
2010-06-25 10000.00 6.8121 68258.00
小结 16352.00 111530.55 111213.55
(select row_number() over (ORDER BY a.核销单号) as id,a.外销发票号
from baoguan a,shoukuan b,fukuan c
where a.核销单号=b.核销单号
and a.核销单号=c.核销单号) z
where z.id = x.id - 1) then '' else 外销发票号 end) as 外销发票号,
收汇金额
from (select row_number() over (ORDER BY a.核销单号) as id,a.外销发票号,b.收汇金额
from baoguan a,shoukuan b,fukuan c
where a.核销单号=b.核销单号
and a.核销单号=c.核销单号) x--提供一段SQL，算是个思路吧，给楼主参考
--全部都写，实在太费时间了
--------------------------------------------
1 126HFGY10001 10879.38
2 6323.00
3 10879.38
4 6323.00
5 10879.38
6 6323.00
7 126HFGY10002 1901.60
8 4450.40
9 10000.00
10 1901.60
11 4450.40
12 10000.00