用JavaScript通过循环插入行

Question

我有这样的查询:

SELECT kailiv, count(*) as PALETTES from FGE50NEUV1.gesupe 
where etasup='30' and cumcol>0 and kailiv>=1 
and kailiv<=66 
group by kailiv 
order by kailiv

返回如下内容:

[
{ KAILIV: 1, PALETTES: 15 },
{ KAILIV: 2, PALETTES: 7 },
{ KAILIV: 3, PALETTES: 2 },
{ KAILIV: 6, PALETTES: 4 },
{ KAILIV: 7, PALETTES: 1 },
{ KAILIV: 13, PALETTES: 10 },
{ KAILIV: 15, PALETTES: 3 },
{ KAILIV: 20, PALETTES: 8 },
{ KAILIV: 26, PALETTES: 2 },
{ KAILIV: 27, PALETTES: 1 },
{ KAILIV: 29, PALETTES: 10 },
{ KAILIV: 30, PALETTES: 10 },
{ KAILIV: 31, PALETTES: 4 },
{ KAILIV: 32, PALETTES: 10 },
{ KAILIV: 62, PALETTES: 7 },
{ KAILIV: 63, PALETTES: 6 },
{ KAILIV: 64, PALETTES: 4 }
]

和我想有一行每个"kailiv"在1到66之间，如果为空则为0

通过JS或直接在SQL中获取数据后更容易实现吗?我该如何做到这一点?

编辑:当我在查询后尝试此操作时:

for(let i=1; i<=66; i++) {
let quai = travees[i].KAILIV
if (quai != i) {
travees.slice(i-1,0,{KAILIV : i, PALETTES: 0})
}
}

我有一个错误'无法读取未定义的属性(读取'KAILIV')

Answer 1

这在javascript中很容易做到，一般的想法是将你的数组转换为查找关键字KAILIV然后从1到66循环取出值或0

const input = [
{ KAILIV: 1, PALETTES: 15 },
{ KAILIV: 2, PALETTES: 7 },
{ KAILIV: 3, PALETTES: 2 },
{ KAILIV: 6, PALETTES: 4 },
{ KAILIV: 7, PALETTES: 1 },
{ KAILIV: 13, PALETTES: 10 },
{ KAILIV: 15, PALETTES: 3 },
{ KAILIV: 20, PALETTES: 8 },
{ KAILIV: 26, PALETTES: 2 },
{ KAILIV: 27, PALETTES: 1 },
{ KAILIV: 29, PALETTES: 10 },
{ KAILIV: 30, PALETTES: 10 },
{ KAILIV: 31, PALETTES: 4 },
{ KAILIV: 32, PALETTES: 10 },
{ KAILIV: 62, PALETTES: 7 },
{ KAILIV: 63, PALETTES: 6 },
{ KAILIV: 64, PALETTES: 4 }
]
// make a map of the data
const indexed = Object.fromEntries(input.map(x => [x.KAILIV,x.PALETTES]));
// function to generate a range of numbers (1-66 in our case)
function range(size, startAt = 0) {
return [...Array(size).keys()].map(i => i + startAt);
}
// generate a new set of data
const newData = range(66,1).map(k=> ({
KAILIV:k,
PALETTES: indexed[k] ?? 0
}))
console.log(newData);