Question

用一条SQL语句查询出所有员工姓名的字母前三位

用一条SQL语句查询出所有员工姓名的字母前三位

Answer 1

sql中按姓名拼音排序

select
isnull(b.py,upper(left(a.username,1)))
as
py
,a.username
from
(
select
'a3'
as
username
union
select
username
from
usertable
--修改这里的姓名列~
)
a
left
outer
join
(
select
'a'
as
py,n'骜'
as
word,
n'啊'
as
sword
union
select
'b',n'簿',n'骜'
union
select
'c',n'错',n'簿'
union
select
'd',n'鵽',n'错'
union
select
'e',n'樲',n'鵽'
union
select
'f',n'鳆',n'樲'
union
select
'g',n'腂',n'鳆'
union
select
'h',n'夻',n'腂'
union
select
'j',n'攈',n'夻'
union
select
'k',n'穒',n'攈'
union
select
'l',n'鱳',n'穒'
union
select
'm',n'旀',n'鱳'
union
select
'n',n'桛',n'旀'
union
select
'o',n'沤',n'桛'
union
select
'p',n'曝',n'沤'
union
select
'q',n'囕',n'曝'
union
select
'r',n'鶸',n'囕'
union
select
's',n'蜶',n'鶸'
union
select
't',n'箨',n'蜶'
union
select
'w',n'鹜',n'箨'
union
select
'x',n'鑂',n'鹜'
union
select
'y',n'韵',n'鑂'
union
select
'z',n'咗',n'韵'
)
b
on
left(username,1)
between
b.sword
and
b.word
where
username<>'a3'
and
py='a'
--这里的and
py='a'是查询以a拼音开头的姓名，你可以
改成其他的，或者直接去掉就是查询所有的~
order
by
py

Answer 2

方法一. left从左往右数
select left(员工姓名,3),员工姓名 from 员工
方法二. 截取字符
select substring(员工姓名,0,4),员工姓名from 员工

Answer 3

楼上2位已经综合了 ORACLE 和 SQL SERVER的 答案了，呵呵。

Answer 4

oracle环境

select substr(员工姓名,1,3) from 员工;

Answer 5

员工姓名长啥样儿啊，是字母么？