SQL主从表显示。

[jeetliang]

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

[ws_hgo]

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

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 *,row_number() over(partition by [code] order by getdate()) rank from t2
) t2
join 
t1
on t1.[code]=t2.[code] 
) tt
group by s1,s2

code         name s1   s2
------------ ---- ---- -----------
1001         NV1  svv1 30
                  svv2 36
                  svv3 62
1002         NV2  t001 20
                  t002 50

(5 行受影响)

[ws_hgo]

感觉水哥这个整麻烦了点....

[jeetliang]

'row_number' 不是可以识别的 函数名。

[ws_hgo]

你的是sql2000的

[jeetliang]

是SQL2000的，报表是容易做的，就想做导出EXCEL的显示

[ws_hgo]

--先用临时表
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,s2

code         name s1   s2
------------ ---- ---- -----------
1001         NV1  svv1 30
                  svv2 36
                  svv3 62
1002         NV2  t001 20
                  t002 50

(5 行受影响)

[jeetliang]

1002 NV2 t001 20
1001 NV1 svv1 30
svv2 36
t002 50
svv3 62
这种显示

[ws_hgo]

楼上的已经写的很清楚啦

[jeetliang]

可惜我得不到想要的结果啊，各位高人帮帮忙

[guguda2008]

。。。。写的比你要的好看都不要

[jeetliang]

1001 NV1 svv1 30
svv2 36
svv3 62
1002 NV2 t001 20
t002 50

别误会啊，我要的是上面这个效果的，但结果不是啊

[jeetliang]

CSDN提交上来会变格式的

[ChinaJiaBing]

--try

declare @tb table (code int,name nvarchar(10),v1 int)
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 行受影响)


*/

[水族杰纶]

如果是报表
放前台吧

[SQL_Hhy]

[xyj052]

在SQL里做这个不太好吧，直接在控件哪里做很方便的

[lcw321321]

select a.name,b.s1,b.s2 from t1 a inner join t2 b on a.code=b.code order by b.code,s2

[水族杰纶]

--2005
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 個資料列受到影響)
*/