select aid=case when bid=(select min(bid) from 表B where fid=b.fid) then aid else null end, acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end, fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end, bid,binfo,b.fid from 表B b left join 表A a on a.fid=b.fid
create table 表A(aid int, acontent varchar(10), fid int) insert 表A select 1 ,'bbb', 1 union select 2 ,'ccc', 2 go create table 表B(bid int, binfo varchar(10), fid int) insert 表B select 5, 'b111', 1 union select 6 ,'b222', 1 union select 7 ,'b333', 2 union select 8 ,'b444', 2 union select 9 ,'b555', 2 go select aid=case when bid=(select min(bid) from 表B where fid=b.fid) then aid else null end, acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end, fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end, bid,binfo,b.fid from 表B b left join 表A a on a.fid=b.fid godrop table 表A,表B
create table 表A(aid int, acontent varchar(10), fid int) insert 表A select 1 ,'bbb', 1 union select 2 ,'ccc', 2 go create table 表B(bid int, binfo varchar(10), fid int) insert 表B select 5, 'b111', 1 union select 6 ,'b222', 1 union select 7 ,'b333', 2 union select 8 ,'b444', 2 union select 9 ,'b555', 2 go ---方法一 select aid=case when bid=(select min(bid) from 表B where fid=b.fid) then aid else null end, acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end, fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end, bid,binfo,b.fid from 表B b left join 表A a on a.fid=b.fid go ---方法二 select a.aid,a.acontent,a.fid,b.bid,b.binfo,b.fid from 表B b left join ( select a.aid,a.acontent,a.fid,t.bid from (select bid=min(bid),fid from 表B group by fid) t inner join 表A a on a.fid=t.fid ) a on b.fid=a.fid and b.bid=a.bidgodrop table 表A,表B
acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end,
fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end,
bid,binfo,b.fid
from 表B b left join 表A a on a.fid=b.fid
insert 表A
select 1 ,'bbb', 1
union select 2 ,'ccc', 2
go
create table 表B(bid int, binfo varchar(10), fid int)
insert 表B
select 5, 'b111', 1
union select 6 ,'b222', 1
union select 7 ,'b333', 2
union select 8 ,'b444', 2
union select 9 ,'b555', 2 go
select aid=case when bid=(select min(bid) from 表B where fid=b.fid) then aid else null end,
acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end,
fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end,
bid,binfo,b.fid
from 表B b left join 表A a on a.fid=b.fid
godrop table 表A,表B
insert 表A
select 1 ,'bbb', 1
union select 2 ,'ccc', 2
go
create table 表B(bid int, binfo varchar(10), fid int)
insert 表B
select 5, 'b111', 1
union select 6 ,'b222', 1
union select 7 ,'b333', 2
union select 8 ,'b444', 2
union select 9 ,'b555', 2
go
---方法一
select aid=case when bid=(select min(bid) from 表B where fid=b.fid) then aid else null end,
acontent=case when bid=(select min(bid) from 表B where fid=b.fid) then acontent else null end,
fid=case when bid=(select min(bid) from 表B where fid=b.fid) then a.fid else null end,
bid,binfo,b.fid
from 表B b left join 表A a on a.fid=b.fid
go
---方法二
select a.aid,a.acontent,a.fid,b.bid,b.binfo,b.fid
from 表B b left join (
select a.aid,a.acontent,a.fid,t.bid
from
(select bid=min(bid),fid from 表B group by fid) t
inner join 表A a on a.fid=t.fid ) a on b.fid=a.fid and b.bid=a.bidgodrop table 表A,表B
/* 结果
aid acontent fid bid binfo fid
----------- ---------- ----------- ----------- ---------- -----------
1 bbb 1 5 b111 1
NULL NULL NULL 6 b222 1
2 ccc 2 7 b333 2
NULL NULL NULL 8 b444 2
NULL NULL NULL 9 b555 2(所影响的行数为 5 行)
*/
'应付表
CREATE TABLE [dbo].[TBYF] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[BillNo] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[YDH] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[FYItem] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[FYMemo] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[DWID] [bigint] NULL ,
[FKDate] [datetime] NULL ,
[CostNums] [float] NULL
) ON [PRIMARY]
GO
'应收表
CREATE TABLE [dbo].[TBYS] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[SYDJ] [float] NULL ,
[BXF] [float] NULL ,
[SZF] [float] NULL ,
[SBF] [float] NULL ,
[SBFS] [float] NULL ,
[DXF] [float] NULL ,
[DBF] [float] NULL ,
[DBFS] [float] NULL ,
[KHID] [bigint] NULL ,
[SKDate] [datetime] NULL ,
[YDH] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
YDH 为运单号 两表关联的。应收只有一笔。 而应收有很多笔。想形成上面问题的一个结构。不过。要加入对float 型的汇总。再麻烦一下