按照两个字段分组，获取各个分组的其他字段不为空的最新数据作为一条记录，怎么实现呢？

按照两个字段分组，获取各个分组的其他字段不为空的最新数据作为一条记录，怎么实现呢？

ID areaID distID  f1  f2  ...
 1    11          2     a
 2    11           2        b   
 3     11          2         c 
 4     12         2     d
5      12         2     e

获取的结果应该是
areaID distID f1  f2...
   11          2    a   c
    12         2    e

10 个解决方案

#1

--测试数据
create table #tab
(
id int,
areaID int ,
distID int ,
f1 varchar(4), 
f2 varchar(4)
)
insert into #tab
select 1,    11,          2,     'a','' union all
select 2,    11,          2,     '','b'   union all
select 3,    11,          2,     '','c' union all
select 4,    12,          2,     'd',''union all
select 5,    12,          2,     'e',''

--查询
select a.areaID,a.distID,isnull(b.f1,'') as f1,isnull(c.f2,'')as f2 
from (select areaID,distID from #tab group by areaID,distID) a 
left join 
(
select x.areaID,x.distID,x.f1 from #tab x,
(select areaID,distID,MAX(id) as id from #tab where isnull(f1,'')<>'' group by areaID,distID) y
where x.areaID=y.areaID and x.distID=y.distID and x.id=y.id
)b on a.areaID=b.areaID and a.distID=b.distID
left join
(
select x.areaID,x.distID,x.f2 from #tab x,
(select areaID,distID,MAX(id) as id from #tab where isnull(f2,'')<>'' group by areaID,distID) y
where x.areaID=y.areaID and x.distID=y.distID and x.id=y.id
)c on a.areaID=c.areaID and a.distID=c.distID

--结果
/*
areaID      distID      f1   f2
----------- ----------- ---- ----
11          2           a    c
12          2           e    

(2 行受影响)
*/

#2

/* 测试数据
WITH table1(ID,areaID,distID,f1,f2) AS (
    SELECT 1,11,2,'a',NULL UNION ALL
    SELECT 2,11,2,NULL,'b' UNION ALL
    SELECT 3,11,2,NULL,'c' UNION ALL
    SELECT 4,12,2,'d',NULL UNION ALL
    SELECT 5,12,2,'e',NULL
)*/
        SELECT a.areaID,
               a.distID,
               b1.f1,
               b2.f2
          FROM (
                SELECT DISTINCT
                       areaID,
                       distID
                  FROM table1
               ) a
   OUTER APPLY (
                    SELECT TOP 1 f1
                      FROM table1
                     WHERE table1.areaID = a.areaID
                       AND table1.distID = a.distID
                       AND table1.f1 IS NOT NULL
                  ORDER BY table1.ID DESC
               ) b1
   OUTER APPLY (
                    SELECT TOP 1 f2
                      FROM table1
                     WHERE table1.areaID = a.areaID
                       AND table1.distID = a.distID
                       AND table1.f2 IS NOT NULL
                  ORDER BY table1.ID DESC
               ) b2

     areaID      distID f1   f2
----------- ----------- ---- ----
         11           2 a    c
         12           2 e    NULL

没个“其他字段”都要单独 OUTER APPLY

#3

更正： 每个“其他字段”都要单独 OUTER APPLY 

#4

select a.*
,(select top1 f1 from table where  areaID=a.areaID,distID=b.distID and f1!='' order by id desc) 
,(select top1 f2 from table where  areaID=a.areaID,distID=b.distID and f2!='' order by id desc) 
from (select distinct areaID,distID) a

#5

IF OBJECT_ID('tb') IS NOT NULL
    DROP TABLE dbo.tb
go

CREATE TABLE tb
    (
      ID INT NOT NULL ,
      areaID INT NOT NULL ,
      distID INT NOT NULL ,
      f1 VARCHAR(50) NULL ,
      f2 VARCHAR(50) NULL
    )
   go
   
INSERT  tb
        SELECT  1 ,
                11 ,
                2 ,
                'a' ,
                ''
        UNION ALL
        SELECT  2 ,
                11 ,
                2 ,
                '' ,
                'b'
        UNION ALL
        SELECT  3 ,
                11 ,
                2 ,
                '' ,
                'c'
        UNION ALL
        SELECT  4 ,
                12 ,
                2 ,
                'd' ,
                ''
        UNION ALL
        SELECT  5 ,
                12 ,
                2 ,
                'e' ,
                ''
   
   
SELECT  *
FROM    tb

SELECT areaID,distID
,MAX(CASE WHEN f1_xh=1 THEN f1 ELSE '' end) f1
,MAX(CASE WHEN f1_xh=2 THEN f2 ELSE '' end) f2
 FROM
(
SELECT  *
,ROW_NUMBER() OVER (PARTITION BY areaID,distID ORDER BY CASE WHEN f1<>'' THEN 0 ELSE 1 END,id desc) f1_xh
,ROW_NUMBER() OVER (PARTITION BY areaID,distID ORDER BY CASE WHEN f2<>'' THEN 0 ELSE 1 END,id desc) f2_xh
FROM    tb 
) a WHERE f1_xh=1 OR f2_xh=1
GROUP BY areaID,distID

#6

感觉楼主每天出一道题考我们

#7

--如果有很多f,是否可以先列转行，再行专列呢
create table #T
(
id int,
areaID int ,
distID int ,
f1 varchar(4), 
f2 varchar(4)
)
insert into #T
select 1,    11,          2,     'a','' union all
select 2,    11,          2,     '','b'   union all
select 3,    11,          2,     '','c' union all
select 4,    12,          2,     'd',''union all
select 5,    12,          2,     'e',''
 
select ID,areaid,distid,[F],Val into #T1 from #T 
unpivot(val for [F] in ([f1],[f2]))a 

select * from #T1
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'MAX(case when [F]='''+F+''' then Val else '''' end) as ['+F+']' from #T1 group by F
set @s='select areaid,distid,'+@s+ ' from #T1 group by areaid,distid'
print @s
exec(@s)
drop table #T1,#T

#8

引用 6 楼 yooq_csdn 的回复:

感觉楼主每天出一道题考我们

惭愧，实际工作中sql用的很多，但总是到不了随心所欲的程度

#9

这么多种方法，哪种比较好啊

#10

引用 9 楼 ahking 的回复:

这么多种方法，哪种比较好啊

喜欢哪种用哪种，方法没有好坏，只有适不适合。
也许您哪种都不用，用自己的方式或者习惯去写sql呢~~