### 进行条件汇总

370 观看

1回复

1522 作者的声誉

```----------------------------
| NAME | TYPE | 值|
----------------------------
| N1 | T1 | V1 |
| N1 | T2 | V2 |
| N1 | NULL | V3 |
| N2 | T2 | V4 |
| N2 | NULL | V5 |
| N3 | NULL | V6 |
-----------------------------
```

• 第一层分组将按名称进行。
• 在第二层
• 当可用的类型为T1，T2和NULL时，将T1和NULL组合在一起，并将T2单独分组。
• 当可用类型为T2和NULL时，将NULL与T2分组。
• 当NULL是唯一可用的类型时，只需保持原样即可。

```----------------------------
| N1 | T1 | V1 + V3 |
| N1 | T2 | V2 |
| N2 | T2 | V4 + V5 |
| N3 | NULL | V6 |
-----------------------------
```

### 回应 (1)

3

63187 作者的声誉

``````SELECT t1.NAME, COALESCE(TYPE, MIN_TYPE), SUM(VALUE)
FROM mytable AS t1
JOIN (
SELECT NAME, MIN(TYPE) AS MIN_TYPE
FROM mytable
GROUP BY NAME
) AS t2 ON t1.NAME = t2.NAME
GROUP BY t1.NAME, COALESCE(TYPE, MIN_TYPE)
``````

``````SELECT NAME,
CASE
WHEN T1SUM IS NULL THEN 0
ELSE COALESCE(T1SUM, 0) + COALESCE(NULLSUM,0)
END AS T1SUM,
CASE
WHEN T1SUM IS NULL AND T2SUM IS NOT NULL
THEN COALESCE(T2SUM, 0) + COALESCE(NULLSUM,0)
ELSE COALESCE(T2SUM, 0)
END AS T2SUM,
CASE
WHEN T1SUM IS NULL AND T2SUM IS NULL THEN COALESCE(NULLSUM,0)
ELSE 0
END AS NULLSUM
FROM (
SELECT NAME,
SUM(CASE WHEN TYPE = 'T1' THEN VALUE END) AS T1SUM,
SUM(CASE WHEN TYPE = 'T2' THEN VALUE END) AS T2SUM,
SUM(CASE WHEN TYPE IS NULL THEN VALUE END) AS NULLSUM
FROM mytable
GROUP BY NAME) AS t
``````