SQL主从表显示。表1--t1 查询结果如下:code name v1
1001 NV1 100
1002 NV2 200表2--t2 查询结果如下:code s1 s2
1001 svv1 30
1001 svv2 36
1001 svv3 62
1002 t001 20
1002 t002 50想让两个表关联后如下显示:code name s1 s2
1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50
1001 NV1 100
1002 NV2 200表2--t2 查询结果如下:code s1 s2
1001 svv1 30
1001 svv2 36
1001 svv3 62
1002 t001 20
1002 t002 50想让两个表关联后如下显示:code name s1 s2
1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50
if not object_id('t1') is null
drop table t1
Go
Create table t1([code] int,[name] nvarchar(3),[v1] int)
Insert t1
select 1001,N'NV1',100 union all
select 1002,N'NV2',200
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([code] int,[s1] nvarchar(4),[s2] int)
Insert t2
select 1001,N'svv1',30 union all
select 1001,N'svv2',36 union all
select 1001,N'svv3',62 union all
select 1002,N't001',20 union all
select 1002,N't002',50
Go
;with wufeng
as(
select px=row_number()over(partition by a.[code] order by getdate()),
a.[code],
a.[name],
b.[s1],
b.[s2]
from t1 a,t2 b
where a.[code]=b.[code]
)
select code=case when not exists(select 1
from wufeng
where code=t.code and px<t.px) then ltrim(code) else '' end,
[name]=case when not exists(select 1
from wufeng
where [name]=t.[name] and px<t.px) then [name] else '' end,
[s1],
[s2]
from wufeng t
/*
code name s1 s2
------------ ---- ---- -----------
1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50(5 個資料列受到影響)
*/
drop table t1
Go
Create table t1([code] int,[name] nvarchar(3),[v1] int)
Insert t1
select 1001,N'NV1',100 union all
select 1002,N'NV2',200
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([code] int,[s1] nvarchar(4),[s2] int)
Insert t2
select 1001,N'svv1',30 union all
select 1001,N'svv2',36 union all
select 1001,N'svv3',62 union all
select 1002,N't001',20 union all
select 1002,N't002',50
Goselect
max(case when rank=1 then ltrim(code) else '' end) [code],
max(case when rank=1 then [name] else '' end) [name],
s1,s2
from
(
select t1.[code],t1.[name],t2.[s1],t2.[s2],t2.rank
from
(
select *,row_number() over(partition by [code] order by getdate()) rank from t2
) t2
join
t1
on t1.[code]=t2.[code]
) tt
group by s1,s2code name s1 s2
------------ ---- ---- -----------
1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50(5 行受影响)
--先用临时表
select *,identity(int,1,1) row into # from t2
--然后查询
select
max(case when rank=1 then ltrim(code) else '' end) [code],
max(case when rank=1 then [name] else '' end) [name],
s1,s2
from
(
select t1.[code],t1.[name],t2.[s1],t2.[s2],t2.rank
from
(
select *,
(select count(*)+1 from # where row<t.row and [code]=t.[code]) as rank
from # t
) t2
join
t1
on t1.[code]=t2.[code]
) tt
group by s1,s2code name s1 s2
------------ ---- ---- -----------
1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50(5 行受影响)
1001 NV1 svv1 30
svv2 36
t002 50
svv3 62
这种显示
svv2 36
svv3 62
1002 NV2 t001 20
t002 50别误会啊,我要的是上面这个效果的,但结果不是啊
insert into @tb select 1001,'NV1',100
union all select 1002,'NV2',200
declare @ta table (code int, s1 nvarchar(10),s2 nvarchar(10))
insert into @ta select '1001','svv1',30
union all select '1001','svv2',36
union all select '1001','svv3',62
union all select '1002','t001',20
union all select '1002','t002',50
;with china as
(
select b.*,a.s1,a.s2,px=row_number()over(partition by a.code order by getdate())
from @ta a ,@tb b
where a.code=b.code
)
select code,name = case when not exists(select 1 from china where a.code=code and px<a.px)
then name else null end,
s1,
s2
from china a /*code name s1 s2
----------- ---------- ---------- ----------
1001 NV1 svv1 30
1001 NULL svv2 36
1001 NULL svv3 62
1002 NV2 t001 20
1002 NULL t002 50(5 行受影响)
*/