如何根据两个字段之间的值比较生成结果集?

问题描述

我有下表

id  full_name   nickname
1   Jennifer    Jen
2   Jerome
3   James
4   Jeremiah    J
5   Geoffrey    Jeff

并且我正在尝试制作一个名册，以便如果学生有昵称，那么他们只会像这样使用该昵称.

and am trying to make a roster so that if a student has a nickname, then they will only go by that nickname like so.

id  new_name
1   Jen
2   Jerome
3   James
4   J
5   Jeff

我可以在原始表上使用什么查询来生成结果集，该结果集将 new_name 作为学生的昵称，如果他们有一个，如果有 new_name作为他们的全名?

What's a query I can use on the orginal table to produce a result set that has the new_name as the student's nickname if they have one, and if has the new_name as their full name?

推荐答案

如果没有昵称"意味着 nickname 为 NULL 那么你可以使用 COALESCE:

If "no nickname" means that nickname is NULL then you can use COALESCE:

select id, coalesce(nickname, full_name) as new_name
from your_table

如果没有昵称"表示为空字符串，则:

And if "no nickname" is represented as an empty string then:

select id,
       case nickname when '' then full_name else nickname end
from your_table

如果它可以是空字符串或 NULL:

And if you it could be either an empty string or a NULL:

select id,
       case coalesce(nickname, '') when '' then full_name else nickname end
from your_table

这篇关于如何根据两个字段之间的值比较生成结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！