在查询中使用人类可读的常量 [英] Using human-readable constants in queries

问题描述

这是一个示例查询:

SELECT thing_id
FROM thing
WHERE thing_type IN (3, 7)

我想将3和7变成易于理解的名称，以帮助理解查询的真正作用.像下面这样的东西会很棒:

I would like to turn the 3 and 7 into human-readable names to help understand what the query is truly doing. Something like the following would be great:

SELECT thing_id
FROM thing
WHERE thing_type_id IN (OPENED, ONHOLD)

知道OPENED和ONHOLD的实际值将在其他地方声明.

Knowing that OPENED and ONHOLD would have their actual values declared somewhere else.

我在想，也许还有一种方法可以通过事物类型表的JOIN来实现.

I'm thinking there may also be a way to do this with a JOIN of a thing_type table.

请注意，我被困在直接编码查询而不是使用抽象框架的环境中.

Note that I'm stuck in an environment where I'm coding queries directly rather than using an abstraction framework.

推荐答案

假设您有一个名为ThingNames的链接表，其中有两列，即id和ThingName，您可以这样做

Assuming you have a linked table called ThingNames where there are two columns, id and ThingName, you could do this

SELECT thing_id

FROM thing

LEFT JOIN ThingNames on thing.thing_type_id = ThingName.id

WHERE ThingNames.ThingName IN ('OPENED', 'ONHOLD')

(不要忘记括号内ThingNames周围的引号.

(Don't forget the quotes around the ThingNames in your in brackets.

这篇关于在查询中使用人类可读的常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！