英文:
Variables in where clause
问题
select id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year,
case when yid='7830' then '044'
when yid='7888' then '040'
end as course
from table
where year=@year and course=@course;
英文:
Is there any way to create where condition with the variable you created? Like;
select id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year,
case when yid='7830' then '044'
when yid='7888 then '040'
and as course
from table
where year=@year and course=@course;
So this is not right, but another way to accomplish i need
答案1
得分: 1
你可以嵌套表达式以避免重复：
SELECT *
FROM (
select id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year
from table
)
WHERE year=@year;
... 但是 ...
如果你努力将 @year 变量匹配到列，而不是反过来，你将获得更好的性能。根据要求，yid 列上的任何索引对于此查询都是无用的，而且你强制数据库计算每行的 case 表达式，即使你不需要这样做。
如果你能在查询之前添加一些代码来将传入的 2023-24 变异为已在 yid 中存储的 2024 值，你将能够与索引匹配并避免额外的逐行计算。查询可能快几个数量级。
你没有分享你正在使用的数据库，所以字符串操作可能会有所不同，具体取决于你的情况。但是对于 SQL Server，可以像这样进行字符串操作：
SET @year = LEFT(@year, 2) + RIGHT(@year, 2);
SELECT id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year
FROM table
WHERE yid=@year;
英文:
You can nest the expression to avoid repeating it:
SELECT *
FROM (
select id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year
from table
)
WHERE year=@year;
... BUT ...
You will get much better performance if you make the effort to match the @year variable to the column, rather than the other way around. As requested, any indexes on the yid column are worthless for this query, and you're forcing the database to compute the case expression for every row in the table, even those you don't need.
If you can add some code before the query to mutate an incoming 2023-24 in the @year variable to instead match 2024 value already stored in yid, you will be able to match with an index and avoid extra per-row computations. The query could be multiple orders of magnitude faster.
You didn't share which DB you're using, so the string manipulation might be a little different depending on what you have, but for SQL Server that could look like this:
SET @year = LEFT(@year, 2) + RIGHT(@year, 2);
SELECT id,
case when yid='2024' then '2023-24'
when yid='2023' then '2022-23'
end as year
FROM table
WHERE yid=@year;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论