删除空值并合并 sql server 2008 r2 [英] remove null values and merge sql server 2008 r2

问题描述

我有一个表(TestTable)如下

I have a table (TestTable) as follows

PK  |  COL1  |  COL2  |  COL3  
1   |  3    |  NULL  |  NULL    
2   |  3    |  43    |  1.5     
3   |  4    |  NULL  |  NULL    
4   |  4    |  NULL  |  NULL    
5   |  4    |  48    |  10.5    
6   | NULL  |  NULL  |  NULL  
7   | NULL  |  NULL  |  NULL  
8   | NULL  |  NULL  |  NULL  
9   | 5     |  NULL  |  NULL  
10  | 5     |  NULL  |  NULL  
11  | 5     |  55    |  95 

我想要的结果如下

PK  |  COL1  |  COL2  |  COL3  
1   |  3     |  43    |  1.5    
2   |  4     |  48    |  10.5    
3   |  5     |  55    |  95  

推荐答案

你可以这样做，但它不会给你PK的序列号:

You can do this, But it won't give you a serial number for the PK:

SELECT 
    PK,
    MAX(Col1) AS Col1,
    MAX(Col2) AS Col2,
    MAX(Col3) AS Col3
  FROM TestTable
  WHERE Col1 IS NOT NULL 
    AND Col2 IS NOT NULL 
    AND COL3 IS NOT NULL
  GROUP BY PK;


| PK | COL1 | COL2 | COL3 |
|----|------|------|------|
|  2 |    3 |   43 |  1.5 |
|  5 |    4 |   48 | 10.5 |
| 11 |    5 |   55 |   95 |

如果你想为列pk生成一个rownumber，你可以这样做:

If you want to generate a rownumber for the column pk, you can do this:

WITH CTE
AS
(
  SELECT 
    PK,
    MAX(Col1) AS Col1,
    MAX(Col2) AS Col2,
    MAX(Col3) AS Col3
  FROM TestTable
  WHERE Col1 IS NOT NULL 
    AND Col2 IS NOT NULL 
    AND COL3 IS NOT NULL
  GROUP BY PK
), Ranked 
AS
(
  SELECT *, ROW_NUMBER() OVER(ORDER BY PK) AS RN
  FROM CTE;
 )
SELECT RN AS PK, Col1, COL2, COL3 FROM Ranked

• SQL Fiddle 演示

这会给你:

| PK | COL1 | COL2 | COL3 |
|----|------|------|------|
|  1 |    3 |   43 |  1.5 |
|  2 |    4 |   48 | 10.5 |
|  3 |    5 |   55 |   95 |

这篇关于删除空值并合并 sql server 2008 r2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！