用查询中的字符串替换整数 [英] replace integer with string in query

问题描述

我已经寻找了答案，但没有找到.

I have searched for an answer and have not been able to find one.

我有一个记录整数而不是文本的表.我正在尝试运行查询并用文本替换特定整数.

I have a table that records an integer instead of text. I am trying to run a query and substitute text for a specific integer.

这是 cal_referral_type 列(该列是 int)中的整数及其代表的相关文本:

here are the integers in column cal_referral_type (this column is int) with the correlating text it represents:

DO NOT TRACK|1
Carey Guide |2
Education   |3
Employment  |4
Housing     |5
Medical     |6

我需要生成一份报告以查看每个类别有多少推荐人，但工作人员对数字感到困惑.

I need to run a report to see how many referrals are going to each category but staff get confused with just the number.

如果我运行查询:

SELECT cal_owner,cal_title,cal_referral_type 
FROM egw_cal 
WHERE cal_referral_type = 3

它显示了对教育服务的所有推荐，但该列的结果是3"而不是描述性文本教育"

It shows all referrals to educational services but the column results in a "3" instead of descriptive text "education"

我已经读到我可以使用 CASE 语句，例如:

I have read that I can use a CASE Statement such as:

SELECT cal_owner,cal_title, CASE cal_referral_type
WHEN 1 THEN 'NA' ELSE 'other' FROM `egw_cal`

每次我以不同的方式扭曲查询时，它只是错误.

Each time I twist the query a different way, it just errors.

那么，有没有办法在每次识别出整数3"时让查询结果显示教育"?一旦我可以开始工作，我将扩展到包括所有类别.

So, is there a way I can have the results of the query show "education" each time the integer "3" is identified? I will expand to include all categories once I can get one to work.

推荐答案

CASE 语句应该可以工作，但更好/更灵活的方法是使用包含整数值和描述性文本的查找表，然后加入该表并将文本拉出.这使得更改更容易实现，因为您更新/添加到查找表而不是更改代码.如果您需要，它还有助于以更简洁的方式实现用户友好的界面.

A CASE statement should work, but a better/more flexible approach is to have a lookup table with the integer values and descriptive text, and then join to that table and pull the text out. This makes changes easier to implement, as you update/add to the lookup table instead of altering code. It also helps implement user-friendly interfaces in a cleaner way if you need to do that.

这篇关于用查询中的字符串替换整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！