34,591
社区成员
发帖
与我相关
我的任务
分享
create table tableA (id int,name varchar(10))
insert tableA
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC'
create table tableB (name varchar(10),value varchar(10))
insert tableB
select 'AAA','123123' union all
select 'AAA','12312' union all
select 'CCC','1231' union all
select 'CCC','123' union all
select 'CCC','12' union all
select 'SSS','xxx'
需要结果:
1 AAA 123123
12312
2 BBB NULL
3 CCC 1231
123
12
--create table tableA (id int,name varchar(10))
--insert tableA
--select 1,'AAA' union all
--select 2,'BBB' union all
--select 3,'CCC'
--create table tableB (name varchar(10),value varchar(10))
--insert tableB
--select 'AAA','123123' union all
--select 'AAA','12312' union all
--select 'CCC','1231' union all
--select 'CCC','123' union all
--select 'CCC','12' union all
--select 'SSS','xxx'
-- GO
SELECT CASE WHEN id2<>1 THEN '' ELSE cast(id AS varchar) END id,CASE WHEN id2<>1 THEN '' ELSE NAME END NAME,value
FROM (
SELECT a.id,a.NAME,b.value,ROW_NUMBER()OVER(PARTITION BY a.id,a.NAME ORDER BY b.value DESC)id2
FROM tablea a LEFT JOIN tableB b ON a.NAME=b.name)a
/*
id NAME value
------------------------------ ---------- ----------
1 AAA 123123
12312
2 BBB NULL
3 CCC 1231
123
12
*/