一个初学者实践JavaScript的第二坑—sequelize带有参数的自定义查询

最新推荐文章于 2024-05-11 10:05:08 发布

weixin_40541945

最新推荐文章于 2024-05-11 10:05:08 发布

阅读量1k

点赞数 1

文章标签： javascript 开发语言 ecmascript

本文链接：https://blog.csdn.net/weixin_40541945/article/details/123228285

版权

在后端想要通过sequelize连接数据库中得到如下结果:

sql语句如下:

WITH A AS (
	SELECT
		企业名称,
		ifnull( sum( CASE WHEN 年份 = 2021 THEN 本月销量 END ), 0 ) '2021年销量',
		ifnull( sum( CASE WHEN 年份 = 2022 THEN 本月销量 END ), 0 ) '2020年销量',
		rank() over ( ORDER BY ifnull( sum( CASE WHEN 年份 = 2022 THEN 本月销量 END ), 0 ) DESC ) 排名 
	FROM
		销量数据库 
	WHERE
		月份 = 1 
	GROUP BY
		企业名称 
	),
	B AS (
	SELECT
		企业名称,
		ifnull( sum(本月销量), 0 ) 历史销量,
		rank() over ( ORDER BY ifnull( sum(本月销量), 0 ) DESC ) 历史排名 
	FROM
		销量数据库 
	WHERE
		年份 = 2021 
		AND 月份 = 1 
	GROUP BY
		企业名称 
	) SELECT
	A.企业名称,
	2021年销量,
	2020年销量,排名,
	IFNULL( B.历史销量, 0 ) 历史销量,
	IFnull(
		B.历史排名,(
		SELECT
			max(历史排名) 
		FROM
			B 
		)) 历史排名 
FROM
	A
	LEFT JOIN B ON A.企业名称 = B.企业名称

对于这种复杂的SQL查询，在这里就不再使用sequelize的模型匹配，而使用其原始查询，代码如下：

const [results, metadata] = await sequelize.query("With A as (select 企业名称, ifnull(sum(case when 年份=2021 then 本月销量 end),0) '2021年销量',ifnull(sum(case when 年份=2022 then 本月销量 end),0) '2020年销量',rank() over(order by ifnull(sum(case when 年份=2022 then 本月销量 end),0) DESC ) 排名 from 销量数据库 where 月份=1 group by 企业名称), B as (select 企业名称 ,ifnull(sum(本月销量),0) 历史销量,rank() over(order by ifnull(sum(本月销量),0) DESC ) 历史排名 from 销量数据库  where 年份=2021 and 月份=1 group by 企业名称) select A.企业名称,2021年销量,2020年销量 ,排名,IFNULL(B.历史销量,0) 历史销量,IFnull(B.历史排名,(select max(历史排名) from B)) 历史排名 from A LEFT JOIN B on A.企业名称=B.企业名称")

但在实际应用的过程中，年份、月份等参数并非是固定的，由前端申请携带而来，如year1=2021，year2=2020，month=1，然后引用入sql语句，这就用到了sequelize.query()的第二个参数，第二个参数是一个对象，其中有一个参数叫做replacements，使用方法如下（这里就直接引用了官网的用法）:

await sequelize.query(
  'SELECT * FROM projects WHERE status =:status',
  {
    replacements: { status: 'active' },
  }
);//将语句中的:status在运行时替换为‘active’

这样的话，就可以在我们的语句中2021写成:year1，2020写成:year2，月份的1写成:month，而后在sequelize.query（）中引入第二个参数{replacements:{year1,year2,month}}，但结果并不令人满意，主要问题是出在了，2021年销量和2020年销量两个字段名称上边，通过replacements引入的变量在sql语句中默认为字符串，运行时自行加上了引号，这就导致这两个字段名称变成了‘2021’年销量和‘2020’年销量，2021与2020都多加了引号，你可能会说了，这也无关大碍，但是，如果在以下语句（select '列名1' from 数据库）中，列名1也使用变量，有可能报错，而无法获取数据结果。

得不到想要的结果，我便考虑使用js自己的参数引用，尝试代码如下：

const sql =`With A as (select 企业名称, ifnull(sum(case when 年份=${year1} then 本月销量 end),0) ${year1}年销量,ifnull(sum(case when 年份=${year} then 本月销量 end),0) ${year}年销量,rank() over(order by ifnull(sum(case when 年份=${year} then 本月销量 end),0) DESC ) 排名 from 销量数据库 where 月份=${month} group by 企业名称, B as (select 企业名称 ,ifnull(sum(本月销量),0) 历史销量,rank() over(order by ifnull(sum(本月销量),0) DESC ) 历史排名 from 销量数据库  where 年份=${year1} and 月份=1 group by 企业名称) select A.企业名称,year1,year ,排名,IFNULL(B.历史销量,0) 历史销量,IFnull(B.历史排名,(select max(历史排名) from B)) 历史排名 from A LEFT JOIN B on A.企业名称=B.企业名称`
const [results1, metadata1] = await sequelize.query(sql)

我首先定义了一个字符串变量sql，用js的 ${ }将变量year1,year2,month引入，然后将变量slq作为 sequelize.query（）的第一个参数，得到了我想要的结果,更重要的是在更为复杂的语句中引用参数也避免了语句错误。