SQL: return string describing user category based on a field
The idea is to get in the result a new column describing the user "category". This category could for example be "under18","normal","over65" based on user age.
That is, for each user in my table I want to get as result its ID and this description in a column called "category".
How would you proceed? I need this because the data wil开发者_运维百科l later go to a JSON object and to a ExtJS grid for visualization.
As your category is data derived from existing data, the use of a new column in the database for it should be discouraged. Instead, you could just add a case statement to your database query to work out the category as required or put it into a view in the database.
The query would look something like this:
select
...
case when Age < 18 then 'under18'
when Age > 65 then 'over65'
else 'normal' end AS Age_Category
from ...
At query level this can be done with CASE
expression:
select
user_id,
case when age < 18 then 'under18'
when age <=65 then 'normal'
when age > 65 then 'over65'
else null end as category
from
Users
However i would recommend to move this from query level to application level. You already have application logic to construct JSON from the dataset, this would be a more appropriate place to generate category name from age value.
If your DBMS supports it, I would create a UDF (user defined function) that passes in the age from your table and returns category. This has the advantage of being reusable and a little cleaner. If this is for one query only just use the CASE
statement.
精彩评论