英文:
Value in column to equal other column with conditions
问题
在SQL Server中，你可以使用以下代码来实现你想要的输出：
WITH CTE AS (
SELECT
ID1,
MIN(Value) OVER (PARTITION BY ID1) AS MinValue
FROM Table1
WHERE Value IS NOT NULL
)
SELECT
t.ID1,
t.ID2,
COALESCE(t.Value, c.MinValue) AS Value
FROM Table1 t
LEFT JOIN CTE c ON t.ID1 = c.ID1;
这段代码将创建一个公共表达式（Common Table Expression，CTE），其中计算了每个ID1分组的最小非空值（MinValue）。然后，它将原始表格（Table1）与CTE连接，以将空值替换为相应ID1分组的最小值，最终得到了你想要的输出。
英文:
Using SQL Server
The output I want: For the same values in column ID1, if there is a “null” in column ‘Value’ than make it equal to minimum(value) of that ID1.
I previously had a question relating to the following, although have still been unable to solve this!
Table 1
| ID1 | ID2 | Value |
|---|---|---|
| 1 | 1 | 0.1 |
| 1 | 2 | 2 |
| 1 | 3 | null |
| 2 | 5 | 0.2 |
| 2 | 5 | null |
| 3 | 7 | 6 |
Output I want
| ID1 | ID2 | Value |
|---|---|---|
| 1 | 1 | 0.1 |
| 1 | 2 | 2 |
| 1 | 3 | 0.1 |
| 2 | 5 | 0.2 |
| 2 | 5 | 0.2 |
| 3 | 7 | 6 |
答案1
得分: 3
你可以使用窗口函数和 min() 函数。
select ID1, ID2,
[Value] = coalesce(Value,
min(Value) over (partition by ID1))
from yourTable
英文:
you can use min() with window function
select ID1, ID2,
[Value] = coalesce(Value,
min(Value) over (partition by ID1))
from yourTable
答案2
得分: 0
WITH minimumValues AS (SELECT ID1, MIN(value) as value FROM table GROUP BY ID1)
SELECT
ID1,
ID2,
COALESCE(table1.Value, minimumValues.value) AS value
FROM table INNER JOIN
MinimumValues ON MinimumValues.ID1 = table.ID1
英文:
WITH minimumValues AS (SELECT ID1, MIN(value) as value FROM table GROUP BY ID1)
SELECT
ID1,
ID2,
COALESCE( table1.Value, minimumValues.value) AS value
FROM table INNER JOIN
MinimumValues ON MinimumValues.ID1 = table.ID1
答案3
得分: 0
你可以尝试以下内容以获得所需的输出 -
示例表格：
CREATE TABLE SampleData (
ID1 INT,
ID2 INT,
Value FLOAT
);
示例数据：
INSERT INTO SampleData
(ID1, ID2, Value)
VALUES
(1, 1, 0.1),
(1, 2, 2),
(1, 3, NULL),
(2, 5, 0.2),
(2, 5, NULL),
(3, 7, 6);
查询：
WITH cte AS (
SELECT ID1, MIN(Value) AS MinValue
FROM SampleData
WHERE Value IS NOT NULL
GROUP BY ID1
)
SELECT t.ID1, t.ID2, COALESCE(t.Value, cte.MinValue) AS Value
FROM SampleData t
JOIN cte ON t.ID1 = cte.ID1
英文:
You can try the below to get the desired output -
Sample Table:
CREATE TABLE SampleData (
ID1 INT,
ID2 INT,
Value FLOAT
);
Sample Data:
INSERT INTO SampleData
(ID1, ID2, Value)
VALUES
(1, 1, 0.1),
(1, 2, 2),
(1, 3, NULL),
(2, 5, 0.2),
(2, 5, NULL),
(3, 7, 6);
Query:
WITH cte AS (
SELECT ID1, MIN(Value) AS MinValue
FROM SampleData
WHERE Value IS NOT NULL
GROUP BY ID1
)
SELECT t.ID1, t.ID2, COALESCE(t.Value, cte.MinValue) AS Value
FROM SampleData t
JOIN cte ON t.ID1 = cte.ID1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论