的"情况"中个性化值范围
在SQL中,是否有一种方法可以添加不存在的行(基于某些组合)。
所有没有值的行:
Col-a Col-a
Col-a Col-b
Col-a Col-c
...
数据库:
Col-a Col-a Val-a
Col-a Col-b Val-b
Col-b Col-a Val-c
Col-b Col-b Val-d
...
假设 col-a col-c 的行中不存在DB中,我想添加Col-a, Col-c, 0
,它只是插入零值的缺失行。
示例:
1,1,50
1,2,100
2,2,100
因此,在这里我们缺少2,1,因此加2,1,0。我将给出一组[1,2],其中两个都应存在值。
预期输出:
1,1,50
1,2,100
2,1,0
2,2,100
有一个简单的方法吗?
您应该放入"联合"。
select '' col-a, '' col-b, '' col-c, [null your_table.fields]
union
select '' col-a, '' col-b, '' col-c, your_table.*
from your_table
在评论和原始问题中的清除后进行更新:
您可以尝试此SQL语句..
select THeaders.col_a, THeaders.col_b, TSrcValues.col_c
from
(
select 1 as col_a, 1 as col_b, 0 as col_c from dual
union
select 1 as col_a, 2 as col_b, 0 as col_c from dual
union
select 1 as col_a, 3 as col_b, 0 as col_c from dual
union
select 2 as col_a, 1 as col_b, 0 as col_c from dual
union
select 2 as col_a, 2 as col_b, 0 as col_c from dual
union
select 2 as col_a, 3 as col_b, 0 as col_c from dual
)THeaders
left join
(
select TSrc.*
from
(
select
case when YourValue = 100 then 1 else case when YourValue = 200 then 2 else case when YourValue = 300 then 3 else 0 end end end col_a,
case when YourValue = 100 then 1 else case when YourValue = 200 then 2 else case when YourValue = 300 then 3 else 0 end end end col_b,
YourValue as col_c
from YourTable
) TSrc
where TSrc.col_a <> 0 or TSrc.col_b <> 0
) TSrcValues
on THeaders.col_a = TSrcValues.col_a and THeaders.col_b= TSrcValues.col_b
;
您应该在"案例"条款
您的示例需要更加清晰。但是,如果我正确理解您,则需要找到第一列的所有唯一值,并将其与第二列的所有唯一值结合在一起,如果该组合不存在,则插入行:
INSERT INTO foo (col1, col2, value)
SELECT a.col1, b.col2,0
FROM (SELECT DISTINCT col1 FROM foo) AS a
JOIN (SELECT DISTINCT col2 FROM foo) AS b
LEFT JOIN foo
ON a.col1 = foo.col1
AND b.col2 = foo.col2
WHERE foo.value IS NULL;
这是一个SQLFIDDLE,显示了工作的选择组件