如何在多个where子句中为sql查询赋值，并在单个命令中运行查询

Question

我有values数组为:

const params = [
['2022-12-10', 'aaaaa', '2022-12-01', 'xhxha', '2022-12-10'],
['2022-12-11', 'ababa', '2022-12-01', 'xhxha', '2022-12-11'],
['2022-12-12', 'acaca', '2022-12-01', 'xhxha', '2022-12-12'],
['2022-12-13', 'adada', '2022-12-01', 'xhxha', '2022-12-13'],
];
const data = await db.query(`select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date ABS(DATEDIFF(?, end_date))+1 as delay from chart 
where uid = ?
and date = ?
and project_uid = ?
and end_date = ?
and completed is true;
`, [params]);

我想运行这个查询，但是所有的值都被添加到第一个?(问号)。我想让它们的值不同?并得到结果。

我也不想在for循环中运行查询。我怎么得到结果呢?

Answer 1

SELECT id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date, ABS(DATEDIFF(?, end_date))+1 as delay
FROM (
values
row(date '2022-12-10', 'aaaaa', date '2022-12-01', 'xhxha', '2022-12-10'),
row(date '2022-12-11', 'ababa', date '2022-12-01', 'xhxha', '2022-12-11'),
row(date '2022-12-12', 'acaca', date '2022-12-01', 'xhxha', '2022-12-12'),
row(date '2022-12-13', 'adada', date '2022-12-01', 'xhxha', '2022-12-13')
) params(param1, param2, param3, param4, param5)
JOIN CHART
on CHART.uid = params.param1
AND CHART.date = params.param2
AND CHART.project_uid = params.param3
AND CHART.end_date = params.param4
AND CHART.completed is true;

Answer 2

可以加入参数子查询。从8.0开始，您可以使用VALUES表构造函数。或者使用SELECT ..params子查询

中的UNION ALL

select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date ABS(DATEDIFF(?, end_date))+1 as delay
from (
values
row(date '2022-12-10', 'aaaaa', date '2022-12-01', 'xhxha', '2022-12-10'),
row(date '2022-12-11', 'ababa', date '2022-12-01', 'xhxha', '2022-12-11'),
row(date '2022-12-12', 'acaca', date '2022-12-01', 'xhxha', '2022-12-12'),
row(date '2022-12-13', 'adada', date '2022-12-01', 'xhxha', '2022-12-13')
) params(p1, p2, p3, p4 ,p5)
join chart c
on c.uid = params.p1
and c.date = params.p2
and c.project_uid = params.p3
and c.end_date = params.p4
and c.completed is true