22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
--测试数据
;WITH Product(FGUID,SPDM)AS(
select 'a','A1' union all
select 'a','A2' union all
select 'a','A3' union all
select 'b','B1' union all
select 'b','B2'
),LS([GUID],WLFY) AS (
select 'a',10 union all
select 'b',20
)
--测试数据结束
--查询语句
SELECT c.* ,
[GUID] ,
CASE WHEN ROW_NUMBER() OVER ( PARTITION BY c.FGUID ORDER BY SPDM ) = 1
THEN WLFY
ELSE 0
END AS WLFY
FROM LS b ,
Product c
WHERE b.GUID = c.FGUID
create table a(fguid int, anum int);
create table b(fguid int, bnum int);
insert into a values(1,11),(1,22),(1,33),(2,2),(2,3),(2,4)
insert into b values(1,30),(2,40)
go
with aa as(
select row_number() over(partition by fguid order by (select 1)) rn, * from a
),
bb as (
select row_number() over(partition by fguid order by (select 1)) rn, * from b
)
select aa.*, isnull(bb.bnum,0) bnum
from aa
left join bb on aa.fguid = bb.fguid and aa.rn = bb.rn
go
drop table a,b
go
rn fguid anum bnum
-------------------- ----------- ----------- -----------
1 1 11 30
2 1 22 0
3 1 33 0
1 2 2 40
2 2 3 0
3 2 4 0
(6 行受影响)