英文:
provide field name dynamically in where clause in query
问题
我有一张表，读取 day_sunday、day_monday 等。我需要查找如果值为 true，则今天列存储的数据是什么。例如，5月17日是星期三，所以查询将是
例如，5月17日是星期三，所以查询将是
select * from table where day_wednesday
类似地，明天的查询不会改变，除非在 where 子句中使用 day_thursday 字段。
我已经编写了下面的查询，但它不起作用。
select restaurant_id from day_matrix where "day_"||lower(dayname(now())) = 1;
这些数据需要在另一个查询中连接。
英文:
I have a table that reads day_sunday, day_monday, etc. I need what data is stored for today's column if the value is true. For example 17 may is Wednesday so the query will be
For example 17 may is a Wednesday so the query will be
select * from table where day_wednesday
Similarly for tomorrow the query won't change except it should use field day_thursday in the where clause.
I have written the query below but it is not working.
select restaurant_id from day_matrix where "day_"||lower(dayname(now())) = 1;
This data needs to get joined in another query.
答案1
得分: 0
在 WHERE 子句中，列和表达式必须在准备查询时固定。无法将列或表达式参数化在 SQL 中。
大多数人解决这个问题的方法是编写应用程序代码，根据应用程序逻辑和变量向 WHERE 子句添加条件。换句话说，在代码结构中像 if() 这样的构造内将 SQL 语法片段附加到字符串中。执行此操作的具体方法取决于您使用的编程语言。
有时，为了帮助实现此技术，框架会提供查询构建器 API。
参考链接：https://stackoverflow.com/a/7476727/4476745
英文:
Columns and expressions in the WHERE clause must be fixed at the time you prepare the query. There's no way to parameterize columns or expressions in SQL.
The way most people solve this problem is by writing application code to add terms to your WHERE clause based on application logic and variables. In other words, append fragments of SQL syntax to a string inside code constructs like if (). The specific methods for doing this depend on the programming language you're using.
To help in this technique, sometimes frameworks provide a query builder API.
Reference: https://stackoverflow.com/a/7476727/4476745
答案2
得分: 0
修复你的数据模型！这些列应该作为单独表格中的行来表示，如下所示：
create table restaurant (
id int primary key
-- 更多用于其他餐厅属性的列
);
create table restaurant_schedule (
restaurant_id int not null references restaurant (id),
week_day int not null, -- 周日作为数字表示（例如：星期一=0，星期日=6）
is_active int not null, -- 0或1
primary key (restaurant_id, week_day)
);
现在，查找今天活跃的餐厅就像是一个join一样简单：
select r.*
from restaurant r
inner join restaurant_schedule rs on rs.restaurant_id = r.id
where rs.week_day = weekday(current_date) and is_active = 1
如果由于某种原因你被困在当前的模式中，那么一个简单的解决方案是使用布尔逻辑 - 但它需要在查询中列举所有列名：
select *
from mytable
where 1 = case dayname(current_date)
when 'Monday' then day_monday
when 'Tuesday' then day_tuesday
...
when 'Sunday' then day_sunday
end
英文:
Fix your data model! These columns should be represented as rows in a separate table, as in:
create table restaurant (
id int primary key
-- more columns for other restaurant attributes
);
create table restaurant_schedule (
restaurant_id int not null references restaurant (id),
week_day int not null, -- weekday as a number (eg : Monday=0 .. Sunday=6)
is_active int not null, -- 0 or 1
primary key (restaurant_id, week_day)
);
Now, looking up restaurants that are active today is as simple as a join:
select r.*
from restaurant r
inner join restaurant_schedule rs on rs.restaurant_id = r.id
where rs.week_day = weekday(current_date) and is_active = 1
If for some reason you are stuck with your current schema, then a simple solution is boolean logic - but it requires enumarating all column names in the query:
select *
from mytable
where 1 = case dayname(current_date)
when 'Monday' then day_monday
when 'Tuesday' then day_tuesday
...
when 'Sunday' then day_sunday
end
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论