主表 tbm(BillNo varchar(50),Re varchar(50),ImportFrom varchar(50),ImportDate varchar(50),LogBy varchar(50))
子表 tbs(BillNo varchar(50),ProdName varchar(50),ProdPrice int,ProdAmount int,Re varchar(50))
tbm 和 tbs 通过BillNo关联tbm中内容如：
BillNo Re ImportFrom ImportDate Logby
3001 abdd SH 2011-11-03 Zhang
3051 xxxx BJ 2011-01-25 Li
3089 dddd GZ 2011-09-30 Wangtbs中内容如：
BillNo ProdName ProdPrice ProdAmount Re
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3089 aa01 26 110 666
3089 bb35 65 99 sdf需要得到的效果（1）如下：
FD1 FD2 FD3 FD4 FD5
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf或者得到如下效果（2）：
BillNo Re ImportFrom ImportDate Logby BillNo ProdName ProdPrice ProdAmount Re
3001 abdd SH 2011-11-03 Zhang 3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf
子表 tbs(BillNo varchar(50),ProdName varchar(50),ProdPrice int,ProdAmount int,Re varchar(50))
tbm 和 tbs 通过BillNo关联tbm中内容如：
BillNo Re ImportFrom ImportDate Logby
3001 abdd SH 2011-11-03 Zhang
3051 xxxx BJ 2011-01-25 Li
3089 dddd GZ 2011-09-30 Wangtbs中内容如：
BillNo ProdName ProdPrice ProdAmount Re
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3089 aa01 26 110 666
3089 bb35 65 99 sdf需要得到的效果（1）如下：
FD1 FD2 FD3 FD4 FD5
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf或者得到如下效果（2）：
BillNo Re ImportFrom ImportDate Logby BillNo ProdName ProdPrice ProdAmount Re
3001 abdd SH 2011-11-03 Zhang 3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf
create table tbs(BillNo varchar(50),ProdName varchar(50),ProdPrice int,ProdAmount int,Re varchar(50))
--tbm 和 tbs 通过BillNo关联
insert into tbm select '3001','abdd','SH','2011-11-03','Zhang'
insert into tbm select '3051','xxxx','BJ','2011-01-25','Li'
insert into tbm select '3089','dddd','GZ','2011-09-30','Wang'
insert into tbs select '3051','aa01',25,100,'na'
insert into tbs select '3051','aa03',38,300,'dd'
insert into tbs select '3051','aa06',12,560,'sx'
insert into tbs select '3001','bb52',23,120,'rd'
insert into tbs select '3001','aa36',30,100,'ds'
insert into tbs select '3001','dd05',28,160,'dss'
insert into tbs select '3089','aa01',26,110,'666'
insert into tbs select '3089','bb35',65,99,' sdf'
go
select * from tbm
union all
select Billno,prodname,ltrim(ProdPrice),ltrim(prodamount),re from tbs order by billno
/*
BillNo Re ImportFrom ImportDate LogBy
----------- ----------- -------------- -------------- -------------------------------------------
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf(11 行受影响)*/
go
drop table tbm,tbs
if not object_id('tbm') is null
drop table tbm
Go
Create table tbm([BillNo] int,[Re] nvarchar(4),[ImportFrom] nvarchar(2),[ImportDate] Datetime,[Logby] nvarchar(5))
Insert tbm
select '3001',N'abdd',N'SH','2011-11-03',N'Zhang' union all
select '3051',N'xxxx',N'BJ','2011-01-25',N'Li' union all
select '3089',N'dddd',N'GZ','2011-09-30',N'Wang'
Go
--> --> (Roy)生成測試數據
if not object_id('tbs') is null
drop table tbs
Go
Create table tbs([BillNo] int,[ProdName] nvarchar(4),[ProdPrice] int,[ProdAmount] int,[Re] nvarchar(3))
Insert tbs
select '3051',N'aa01',25,100,N'na' union all
select '3051',N'aa03',38,300,N'dd' union all
select '3051',N'aa06',12,560,N'sx' union all
select '3001',N'bb52',23,120,N'rd' union all
select '3001',N'aa36',30,100,N'ds' union all
select '3001',N'dd05',28,160,N'dss' union all
select '3089',N'aa01',26,110,N'666' union all
select '3089',N'bb35',65,99,N'sdf'
Go
Select
[BillNo]=case when row=1 then rtrim(a.[BillNo]) else '' end ,
[Re]=case when row=1 then a.[Re] else '' end ,
[Logby]=case when row=1 then a.[Logby] else '' end ,
[ImportDate]=case when row=1 then a.[ImportDate] else '' end ,
b.*
from tbm as a
inner join
(Select *,ROW_NUMBER()over(partition by [BillNo] order by (select 1))as row from tbs) as b
on a.[BillNo]=b.[BillNo]/*
BillNo Re Logby ImportDate BillNo ProdName ProdPrice ProdAmount Re row
3001 abdd Zhang 2011-11-03 00:00:00.000 3001 bb52 23 120 rd 1
1900-01-01 00:00:00.000 3001 aa36 30 100 ds 2
1900-01-01 00:00:00.000 3001 dd05 28 160 dss 3
3051 xxxx Li 2011-01-25 00:00:00.000 3051 aa01 25 100 na 1
1900-01-01 00:00:00.000 3051 aa03 38 300 dd 2
1900-01-01 00:00:00.000 3051 aa06 12 560 sx 3
3089 dddd Wang 2011-09-30 00:00:00.000 3089 aa01 26 110 666 1
1900-01-01 00:00:00.000 3089 bb35 65 99 sdf 2
*/
如果子表的列数不同与主表，该怎么办？
如
create table tbs(BillNo varchar(50),ProdName varchar(50),Re varchar(50))
case px when 1 then rtrim(a.[BillNo]) else '' end as BillNo,
case px when 1 then a.[Re] else '' end as Re,
case px when 1 then a.[Logby] else '' end as Logby,
case px when 1 then a.[ImportDate] else '' end as ImportDate,
b.*
from
tbm as a
(Select *,px=ROW_NUMBER()over(partition by [BillNo] order by getdate()) from tbs) b
where
a.BillNo=b.BillNo
create table tbm(BillNo varchar(50),Re varchar(50),ImportFrom varchar(50),ImportDate varchar(50),LogBy varchar(50))
create table tbs(BillNo varchar(50),ProdName varchar(50),ProdPrice int,ProdAmount int,Re varchar(50))
--tbm 和 tbs 通过BillNo关联
insert into tbm select '3001','abdd','SH','2011-11-03','Zhang'
insert into tbm select '3051','xxxx','BJ','2011-01-25','Li'
insert into tbm select '3089','dddd','GZ','2011-09-30','Wang'
insert into tbs select '3051','aa01',25,100,'na'
insert into tbs select '3051','aa03',38,300,'dd'
insert into tbs select '3051','aa06',12,560,'sx'
insert into tbs select '3001','bb52',23,120,'rd'
insert into tbs select '3001','aa36',30,100,'ds'
insert into tbs select '3001','dd05',28,160,'dss'
insert into tbs select '3089','aa01',26,110,'666'
insert into tbs select '3089','bb35',65,99,' sdf'
go
select BillNo,Re,ImportFrom,ImportDate,LogBy,BillNo1,Prodname,ProdPrice,ProdAmount,Re1 from(
select billno b,*,''BillNo1,''Prodname,''ProdPrice,''ProdAmount,''Re1 from tbm
union all
select billno b,'','','','','',Billno,prodname,ltrim(ProdPrice),ltrim(prodamount),re from tbs
)t order by b
/*
BillNo Re ImportFrom ImportDate LogBy BillNo1 Prodname ProdPrice ProdAmount Re1
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------ ------------ --------------------------------------------------
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf(11 行受影响)*/
go
drop table tbm,tbs
--> --> (Roy)生成測試數據
if not object_id('tbm') is null
drop table tbm
Go
Create table tbm([BillNo] int,[Re] nvarchar(4),[ImportFrom] nvarchar(2),[ImportDate] Datetime,[Logby] nvarchar(5))
Insert tbm
select '3001',N'abdd',N'SH','2011-11-03',N'Zhang' union all
select '3051',N'xxxx',N'BJ','2011-01-25',N'Li' union all
select '3089',N'dddd',N'GZ','2011-09-30',N'Wang'
Go
--> --> (Roy)生成測試數據
if not object_id('tbs') is null
drop table tbs
Go
Create table tbs([BillNo] int,[ProdName] nvarchar(4),[ProdPrice] int,[ProdAmount] int,[Re] nvarchar(3))
Insert tbs
select '3051',N'aa01',25,100,N'na' union all
select '3051',N'aa03',38,300,N'dd' union all
select '3051',N'aa06',12,560,N'sx' union all
select '3001',N'bb52',23,120,N'rd' union all
select '3001',N'aa36',30,100,N'ds' union all
select '3001',N'dd05',28,160,N'dss' union all
select '3089',N'aa01',26,110,N'666' union all
select '3089',N'bb35',65,99,N'sdf'
Go
FD1 FD2 FD3 FD4 FD5
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 dsselect FD1, FD2,FD3, FD4,FD5
from
(
select FD1=[BillNo],FD2=[Re],FD3=[ImportFrom], FD4=CONVERT(varchar(10),[ImportDate],120),FD5=[Logby],ord=1 from tbm
union all
select FD1=[BillNo],FD2=[ProdName],FD3=rtrim([ProdPrice]), FD4=RTRIM([ProdAmount]),FD5=[Re],ord=2 from tbs
)t
order by FD1,ord
/*
FD1 FD2 FD3 FD4 FD5
3001 abdd SH 2011-11-03 Zhang
3001 bb52 23 120 rd
3001 aa36 30 100 ds
3001 dd05 28 160 dss
3051 xxxx BJ 2011-01-25 Li
3051 aa01 25 100 na
3051 aa03 38 300 dd
3051 aa06 12 560 sx
3089 dddd GZ 2011-09-30 Wang
3089 aa01 26 110 666
3089 bb35 65 99 sdf
*/
不改变表顺序 建议用 union all 定义排序列