我有一張如下表
| 第一列 | 第 2 列 |
|---|---|
| 一個 | 200 |
| 一個 | 200 |
| 一個 | 0 |
| 乙 | 300 |
| 乙 | 200 |
| C | 100 |
我想將此表轉換為下表
帶計算：對于column1的每個元素，SUM（column2）/count of（非零column2）
| 第一列 | 第 2 列 |
|---|---|
| 一個 | ((200 200 0) / 2) = 200 |
| 乙 | ((300 200) / 2) = 250 |
| C | 100 / 1 = 100 |
我唯一能想到的是回圈遍歷不同的元素Column1并運行：
SELECT SUM(Column2)
FROM Table
WHERE Column1 = i / (SELECT COUNT(Column2)
FROM Table
WHERE Column1 = i AND Column2 <> 0)
并生成一個表。
有沒有更好的方法來做到這一點？
uj5u.com熱心網友回復：
使用聚合：
SELECT Column1,
SUM(Column2) / COUNT(CASE WHEN Column2 <> 0 THEN 1 END) AS Column2
FROM yourTable
GROUP BY Column1
HAVING COUNT(CASE WHEN Column2 <> 0 THEN 1 END) > 0;
uj5u.com熱心網友回復：
您可以使用 where 子句洗掉 column2 中帶有 0 的行，然后使用聚合來獲得所需的結果。但它會洗掉那些在所有 columnd2 中都為 0 的 column1 值。
但是 Query2 將回傳 column2 中具有零值的行，而不是洗掉洗掉行。
架構和插入陳述句：
create table testTable (Column1 varchar(50), Column2 int);
insert into testTable values('A', 200);
insert into testTable values('A', 200);
insert into testTable values('A', 0);
insert into testTable values('B', 300);
insert into testTable values('B', 200);
insert into testTable values('C', 100);
insert into testTable values('D', 0);
查詢 1：
SELECT Column1,
SUM(Column2) / COUNT(*) AS Column2
FROM testTable where column2<>0
GROUP BY Column1;
輸出：
| 第一列 | 第 2 列 |
|---|---|
| 一個 | 200 |
| 乙 | 250 |
| C | 100 |
查詢2：
SELECT Column1,
Coalesce(SUM(Column2) / nullif(COUNT(CASE WHEN Column2 <> 0 THEN 1 END),0),0) AS Column2
FROM testTable
GROUP BY Column1;
輸出：
| 第一列 | 第 2 列 |
|---|---|
| 一個 | 200 |
| 乙 | 250 |
| C | 100 |
| D | 0 |
db<>在這里擺弄
uj5u.com熱心網友回復：
您可以使用派生表過濾掉 0 column2 行。然后，您可以應用 GROUP BY。
declare @table table (Column1 char(1), Column2 int)
insert into @table values
('A',200),
('A',200),
('A',0 ),
('B',300),
('B',200),
('C',100);
SELECT Column1, (sum(column2) / count(column2) ) as column2
from
(
SELECT * FROM @TABLE where Column2 <> 0) as t
group by Column1
| 第一列 | 列2 |
|---|---|
| 一個 | 200 |
| 乙 | 250 |
| C | 100 |
uj5u.com熱心網友回復：
檢索 column1 的不同值并忽略 column2 的零值，同時除以 column2 的總和。并且還考慮這里除以零誤差。
-- SQL Server
SELECT t.column1
, t.column2 / (CASE WHEN (t.total - t.total_zero) = 0 THEN 1 ELSE (t.total - t.total_zero) END)
FROM (SELECT column1
, SUM(column2) column2
, COUNT(CASE WHEN column2 = 0 THEN 1 END) total_zero
, COUNT(1) total
FROM test
GROUP BY column1) t
請檢查這個網址https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3853456941909ffffb8792415adc1f6f
uj5u.com熱心網友回復：
使用AVG()視窗函式是因為您想要 的平均值Column2。
如果您想要結果中的所有值，Column1即使它們只有0s in Column2：
SELECT DISTINCT Column1,
AVG(CASE WHEN Column2 <> 0 THEN Column2 END) OVER (PARTITION BY Column1) Column2
FROM tablename;
如果您想要的值的結果Column1至少為 1 Column2not 0：
SELECT DISTINCT Column1,
AVG(Column2) OVER (PARTITION BY Column1) Column2
FROM tablename
WHERE Column2 <> 0;
請參閱演示。
請注意，SQL Server 將整數的平均值截斷為整數，因此如果您希望結果為浮點數，則應乘以Column21.0，例如：
AVG(CASE WHEN Column2 <> 0 THEN 1.0 * Column2 END)
或者：
AVG(1.0 * Column2)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/401907.html
標籤：sql sql-server