简单SQL语句 秒结贴

[昵称90天可改一次]

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

 */