5种数据库sql语句大全

数据库sql语句大全

一、连接列值
db2/oracle/postgresql
select name(字段)||' Works AS a '（文字）||job as msg from emp where deptno=10;

mysql
select concat(name,' works as a',job) as msg from emp where deptno=10;

sql server
select name+' works as a '+job as msg from emp where deptno=10;

二、使用条件逻辑
select name,salary,
    case when salary<=2000 then 'low'
    case when salary>=4000 then 'over'
      else 'ok'
    end as status
from emp

三、限制返回的行数
db2
select * from emp fetch first 5 rows only

mysql/postgresql
select * from emp limit 5

oracle
select * from emp rownum<=5

sql server
select top 5 * from emp

四、随机返回记录
db2
select name,job from emp order by rahnd() fetch first 5 rows only

mysql
select name,job from emp order by rand() limit 5

postgresql
select * from emp order by random() limit 5

oracle
select * from (select name,job from emp order by dbms_random.value()) where rownum<=5

sql server
select top 5 name,job from emp order by newid()

五、将空值转换成实际值
select coalesce(comm,0) from emp
不为空则返回comm值，空则返回0，comm类型与0类型必须一致

六、按子串排序(取消后面2位)
db2/mysql/oracle/postgresql
select name,job from emp order by substr(job,length(job)-2)

sql server
select name,job from emp order by substring(job,len(job)-2,2)

七、对字母数字混合的数据排序
oracle/postgresql
/*order by deptno(数字)*/
select data from emp v order by replace (data,replace(translate(data,'0123456789','##########'),'#',''),'')

/*order by name(字母)*/
select data from emp order by replace(translate(data,'0123456789','##########'),'#','')

db2
/*order by deptno(数字)*/
select * from (select ename||' '||cast(deptno as char(2)) as data from emp ) v order by replace (data,replace(translate(data,'##########','0123456789'),'#',''),'')

/*order by name(字母)*/
select * from (select name||' '||cast(deptno as char(2)) as data from emp v order by replace(translate(data,'##########','0123456789'),'#','')

mysql/sqlserver
当前不支持translate函数，无解决方案

八、处理排序空值
db2/mysql/postgresql/sqlserver
select name,sal,comm from (select name,sal,comm case when comm is null then 0 else 1 end as is_null from emp) x order by is_null desc,comm

oracle
select name,sal,comm from emp order by comm nulls last//all nulls last
select name,sal,comm from emp order by comm nulls first//all nulls first

九、根据数据项的键排序
select name,sal,job,comm from emp order by case when job='salesman' then comm else sal end

十、记录集的叠加
//使用union子句相当于对使用union all子句的结果使用distinct
select ename as ename_and_dname,deptno from emp where deptno=10 union all select '----------',null from t1 union all select dname,deptno from dept

十一、从一个表红查找另一个表没有的值
db2/postgresql
select deptno from dept except select deptno from emp

oracle
select deptno from dept minus select deptno from emp

mysql/sqlserver
select deptno from dept where deptno not in (select deptno from emp)

十二、在一个表中查找与其他表不匹配的记录
db2/mysql/postgresql/sqlserver
select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null

oracle
select d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno is null

十三、向查询中增加联接而不影响其他联接
db2/mysql/postgresql/sqlserver
select e.ename,d.loc,eb.received from emp e join dept d on (e.deptno=d.deptno) left join emp_bonus eb on (e.empno=eb.empno) order by 2

oracle
select e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb where e.deptno=d.deptno and e.empno=eb.empno (+) order by 2
select e.ename,d.loc,(select eb.received from emp_bonus eb where eb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptno order by 2

十四、检测两个表中是否有相同的数据
解决原理：
1、首先，查找处表emp中存在而视图v中没有的行
2、然后合并（union all）在视图v中存在，而在表emp中没有的行

十五、识别和消除笛卡尔积
在from子句对表进行联接来返回正确的结果集：
select e.ename,d.loc from emp e,dept d where e.deptno=10 and d.deptno =e.deptno

十六、聚集与联接
/*只对不相同的工资求和*/
mysql/postgresql
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno=1) x group by deptno

db2/oracle/sqlserver
select distinct deptno,total_sal,total_bonus from (select e.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) as total_sal,e.deptno,sum(e.sal*case when eb.type=1 then .1 when eb.type=2 then .2 else .3 end) over (partition by deptno) as total_bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno=10) x

十七、聚集与外联接
/*只对部门10中不同的工资进行汇总*/
db2/mysql/postgresql/sqlserver
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) group by deptno

select distinct deptno,total_sal,total_bonus from (select e.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) as total_sal,e.deptno,sum(e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type =2 then .2 else .3 end) over (partition by deptno) as total_bonus from emp e left outer join emp_bonus eb on (e.empno=eb.empno) where e.deptno=10) x

oracle
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonus eb where e.empno=eb.empno (+) and e.deptno=10) group by deptno

十八、从多个表中返回丢失的数据
db2/mysql/postgresql/sqlserver
select d.deptno,d.dname,e.ename from dept d full outer join emp e on (d.deptno=e.deptno)

select d.deptno,d.dname,e.ename from dept d right outer join emp e on(d.deptno=e.deptno) union select d.deptno,d.dname,e.ename from dept d left outer join emp e on (d.deptno=e.deptno)

oracle
select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno(+)=e.deptno

十九、在运算和比较时使用null值
select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename='WARD')

二十、从一个表向另外的表中复制行
insert into dept_east (deptno,dname,loc) select deptno,dname,loc from dept where loc in('NEW YORK','BOSTON')

二十一、复制表定义
db2
create table dept_2 like dept

oracle/mysql/postgresql
create table dept_2 as select * from dept where 1=0

sqlserver
select * into dept_2 from dept where 1=0

二十二、一次向多个表中插入记录
oracle
insert all when loc in('NEW YORK','BOSTON') then into dept_east (deptno,dname,loc) values(deptno,dname,loc)
when loc='CHICAGO' then into dept_mid (deptno,dname,loc) values(deptno,dname,loc)
else into dept_west (deptno,dname,loc) values(deptno,dname,loc)
select deptno,dname,loc from dept

db2
insert into (select * from dept_west union all select * from dept_east union all select * from dept_mid) select * from dept

mysql/postgresql/sqlserver
不支持多表插入操作

二十三、阻止对某几列插入
在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行
create view new_emps as select empno,ename,job from emp
二十四、用其他表中的值更新
db2/mysql
update emp e set(e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal ns where ns.deptno=e.deptno) where exists(select nul from new_sa ns where ns.deptno=e.deptno)

oracle
update(select e.sal as emp_sal,e.comm as emp_comm,ns.sal as ns_sal,ns.sal/2 as ns_comm frm emp e,new_sal ns where e.deptno=ns.deptno) set emp_sal=ns_sal,emp_comm=ns_comm

postgresql
update emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns where ns.deptno=emp.deptno

sqlserver
update e set e.sal=ns.sal,e.comm=ns.sal/2 from emp e,new_sal ns where ns.deptno=emp.deptno

二十五、合并记录
oracle
merge into emp_commission ec using(select * from emp) emp on(ec.empno=emp.empno) when matched then update set ec.comm=1000 delete where (sal<2000) when not matched then insert (ec.empno,ec.ename,ec.deptno,ec.comm) values (emp.empno,emp.ename,emp.deptno,emp.comm)

二十六、删除违反参照完整性的记录
delete from emp where not exists(select * from dept where dept.deptno=emp.deptno)

delete from emp where deptno not in(select deptno from dept)

二十七、删除重复记录
delete from dupes where id not in (select min(id) from dupes group by name(需要判断重复的字段))

二十八、删除从其他表引用的记录
delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*)>=3)

（以下模式名schema为smeagol）
二十九、列出模式中的表
db2
select tabname from syscat.table where tabschema=''smeagol''

oracle
select table_name from all_tables where owner=''smeagol''

postgresql/mysql/sqlserver
select tablename from information_schema.tables where table_schema=''smeagol''

三十、列出表的列
db2
select colname,typename,colno from syscat.columns where tablename=''emp'' and tabschema=''smeagol''

oracle
select column_name,data_type,column_id from all_tab_columns where owner=''smeagol'' and table_name=''emp''

postgresql/mysql/sqlserver
select column_name,data_type,ordinal_position from information_schema=''smeagol'' and table_name=''emp''

三十一、列出表的索引列
db2
select a.tabname,b.indname,b.colname,b.colseq from syscat.indexes a,syscat.indexcoluse b where a.tabname=''emp'' and a.tabschema=''smeagol'' and a.indschema=b.indschema and a.indname=b.indname

oracle
select table_name,index_name,column_name,column_position from sys.all_ind_columns where table_name=''emp'' and table_owner=''smeagol''

postgresql
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname=''smeagol'' and a.tablename=b.table_name

mysql
show index from emp

sqlserver
select a.name table_name,b.name index_name,d.name column_name,c.index_column_id from sys.tables a,sys.indexes b,sys.index_columns c,sys.columns d where a.object_id=b.object_id and b.object_id=c.object_id and b.index_id=c.index_id and c.object_id=d.object_id and c.column_id=d.column_id and a.name=''emp''

三十二、列出表约束
db2
select a.tabname,a.constname,b.colname,a.type from syscat.tabconst a,syscat.columns b where a.tabname=''emp'' and a.tabschema=''smeagol'' and a.tabname=b.tabname and a.tabschema=b.tabschema

oracle
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from all_constraints a,all_cons_columns b where a.table_name=''emp'' and a.owner=''smeagol'' and a.table_name=b.table_name and a.owner=b.owner and a.constraint_name=b.constraint_name

postgresql/mysql/sqlserver
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from information_schema.table_constraints a,information_schema.key_column_usage b where a.table_name=''emp'' and a.table_schema=''smeagol'' and a.table_name=b.table_name and a.table_schema=b.table_schema and a.constraint_name=b.constraint_name

三十三、列出没有相应索引的外键
db2
select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indname from (select a.tabschema,a.tabname,a.constname,b.colname from syscat.tabconst a,syscat.keycoluse b where a.tabname=''emp'' and a.tabschema=''smeagol'' and a.type=''f'' and a.tabname=b.tabname and a.tabschema=b.tabschema) fkeys left join (select a.tabschema,a.tabname,a.indname,b.colname from syscat.indexes a,syscat.indexcoluse b where a.indschema=b.indschema and a.indname=b.indname) ind_cols on(fkeys.tabschema=ind_cols.tabschema and fkeys.tabname=ind_cols.tabname and fkeys.colname=ind_cols.colname)where ind_cols.indname is null

oracle
select a.table_name,a.constraint_name,a.column_name,c.index_name from all_cons_columns a,all_constraints b,all_ind_columns c where a.table_name=''emp'' and a.owner=''smeagol'' and b.constraint_type=''r'' and a.owner=b.owner and a.table_name=b.table_name and a.constraint_name=b.constraint_name and a.owner=c.table_owner(+) and a.table_name=c.table_name(+) and a.column_name=c.column_name(+) and c.index_name is null

postgresql
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.constraint_schema,a.table_name,a.constraint_name,a.column_name from information_schema.key_column_usage a,information_schema.referential_constraints b where a.table_name=''emp'' and a.constraint_schema=''smeagol''  and a.constraint_name=b.constraint_name and a.constraint_schema=b.constraint_schema) fkeys left join (select a.schemaname,a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname=b.table_schema and a.tablename=b.table_name) ind_cols on(fkeys.constraint_schema=ind_cols.schemaname and fkeys.table_name=ind_cols.tablename and fkeys.column_name=ind_cols.column_name)where ind_cols.indexname is null

mysql
使用show index来检索索引信息，查询information——schema.key_column_usage列出指定表的外键

sqlserver
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.object_id,d.column_id,a.name table_name,b.name constraint_name,d.name column_name from sys.tables a join sys.foreign_keys b on (a.name=''emp'' and a.object_id=b.parent_object_id) join sys.foreign_key_columns c on(b.object_id=c.constraint_object_id) join sys.columns d on (c.constraint_column_id=d.column_id and a.object_id=d.object_id) fkeys left join (select a.name index_name,b.object_id,b.column_id from sys.indexes a,sys.index_columns b where a.index_id=b.index_id) ind_cols on (fkeys.object_id=ind_cols.object_id and fkeys.column_i=ind_cols.column_id) where ind_cols.index_name is null

三十四、使用sql来生成sql
oracle
select ''select count(*) from ''||table_name||'';'' cnts from user_tables;

三十五、在oracle中描述数据字典视图
select table_name,comments from dictionary order by table_name

select column_name,comments from dict_columns where table_name=''all_tab_columns''

三十六、遍历字符串
select substr(e.ename,iter.post,1) as c from (select ename from emp where ename=''king'') e,(select id as pos from t10) iter where iter.pos<=length(e.ename)

三十七、计算字符在字符串中出现的次数
select (length(''10,clark,manager'')-length(replace(''10,clark,manager'','','','''')))/length('','') as cnt from t1

三十八、从字符串中删除不需要的字符
db2
select ename,replace(translate(ename,''aaaaa'',''AEIOU''),''a'','''') stripped1,sal,replace(cast(sal as char(4)),''0'','''') stripped2 from emp

mysql/sqlserver
select ename,replace(replace(replace(replace(replace(ename,''A'',''''),''E'',''''),''I'',''''),''O'',''''),''U'','''') as stripped1,sal,replace(sal,0,'''') stripped2 from emp

oracle/postgresql
select ename,replace(translate(ename,''AEIOU'',''aaaa''),''a'') as stripped1,sal,replace(sal,0,'''') as stripped2 from emp

三十九、将字符和数字数据分离
db2
select replace(translate(data,''0000000000'',''0123456789''),''0'','''') ename,cast(replace(translate(lower(data),repeat(''z'',26),''abcdefghijklmnopqrstuvwxyz''),''z'','''') as integer) sal from (select ename||cast(sal as char(4)) data from emp) x

oracle
select replace(translate(data,''0123456789'',''0000000000''),''0'') ename,to_number(replace(translate(lower(data),''abcdefghijklmnopqrstuvwxyz'',rpad(''z'',26,''z'')),''z'')) sal from (select ename||sal data from emp)

postgresql
select replace(translate(data,''0123456789'',''0000000000''),''0'','''') as ename,cast(replace(translate(lower(data),''abcdefghijklmnopqrstuvwxyz'',rpad(''z'',26,''z'')),''z'','''') as integer) as sal from (select ename||sal as data from emp) x

四十、判断字符串是不是字母数字型的
db2
select data from view where translate(lower(data),repeat(''a'',36),''0123456789abcdefghijklmnopqrstuvwxyz'')=repeat(''a'',length(data))

mysql
create view V as select ename as data from emp where deptno=10 union all select concat(ename,'',$'',sal,''.00'') as data from emp where deptno=20 union all select concat(ename,deptno) as data from emp where deptno=30
select data from V where data regexp ''[^0-9a-zA-Z]''=0

oracle/postgresql
select data from V where translate(lower(data),''0123456789abcdefghijklmnopqrstuvwxyz'',rpad(''a'',36,''a''))=rpad(''a'',length(data),''a'')

sqlserver
select data from (select v.data,iter.pos,substring(v.data,iter.pos,1) c,ascii(substring(v.data,iter.pos,1)) val from v,(select id as pos from t100) iter where iter.pos<=len(v.data)) x group by data having min(val) between 48 and 122

四十一、提取姓名的大写首字母缩写
db2
select replace(replace(translate(replace(''Stewie Griffin'',''.'',''''),repeat(''#'',26),''abcdefghijklmnopqrstuvwxyz''),''#'',''''),'' '',''.'')||''.'' from dual

mysql
select case when cnt=2 then trim(trailing ''.'' from concat_ws(''.'',substr(substring_index(name,'' '',1),1,1),substr(name,length(substring_index(name,'' '',1))+2,1),substr(substring_index(name,'' '',-1),1,1),''.'')) else trim(trailing ''.'' from concat_ws(''.'',substr(substring_index(name,'' '',1),1,1),substr(substring_index(name,'' '',-1),1,1))) end as initials from (select name,length(name)-length(replace(name,'' '','''')) as cnt from (select replace(''Stewie Griffin'',''.'','''') as name from dual ) y ) x

oracle/postgresql
select replace(replace(translate(replace(''Stewie Griffin'',''.'',''''),''abcdefghijklmnopqrstuvwxyz'',rpad(''#'',26,''#'')),''#'',''''),'' '',''.'')||''.'' from dual

四十二、按字符串中的部分内容排序
db2/oracle/mysql/postgresql
select ename from emp order by substr(ename,length(ename)-1,2)

sqlserver
select ename from emp order by substring(ename,len(ename)-1,2)

四十三、按字符串中的数值排序
db2
select data from V order by cast(replace(translate(data,repeat(''#'',length(data)),replace(translate(data,''##########'',''0123456789''),''#'','''')),''#'','''') as integer)

oracle
select data from V order by to_number(replace(translate(data,replace(translate(data,''0123456789'',''##########''),''#''),rpad(''#'',20,''#'')),''#''))

postgresql
select data from V order by cast(replace(translate(data,replace(translate(data,''0123456789'',''##########''),''#'',''''),rpad(''#'',20,''#'')),''#'','''') as integer)

四十四、根据表中的行创建一个分割列表
db2
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,x.list||'',''||e.name,e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt

mysql
select deptno,group_concat(ename order by empno separator,'','') as emps from emp group by deptno

oracle
select deptno,ltrim(sys_connect_by_path(ename,'',''),'','') emps from (select deptno,ename,row_number() over (partition by deptno order by empno) rn,count(*) over (partition by deptno) cnt from emp) where level=cnt start with rn=1 connect by prior deptno=deptno and prior rn=rn-1

postgresql
select deptno,rtrim(max(case when pos=1 then emps else '''' end)||max(case when pos=2 then emps else '''' end)||max(case when pos=3 then emps else '''' end)||max(case when pos=4 then emps else '''' end)||max(case when pos=5 then emps else '''' end)||max(case when pos=6 then emps else '''' end),'','') as emps from (select a.deptno,a.ename||'','' as emps,d.cnt,(select count(*) from emp b where a.deptno=b.deptno and b.empno<=empno ) as pos from emp a,(select deptno,count(ename) as cnt from emp group by deptno) d where d.deptno=a.deptno) x group by deptno order by 1

sqlserver
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,cast(x.list+'',''+e.name as varchar(100)),e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt order by 1

四十五、将分隔数据转换为多值IN列表
db2
select empno,ename,sal,deptno from emp where empno in (select cast(substr(c,2,locate('','',c,2)-2) as integer) empno from (select substr(csv.emps,cast(iter.pos as integer)) as c from (select '',''||''7654,7698,7782,7788''||'','' emps from t1 ) csv,(select id as pos from t100) iter where iter.pos<=length(csv.emps)) x where length(c)>1 and substr(c,1,1)='','' ) y

mysql
select empno,ename,sal,deptno from emp where empno in (select substring_index(substring_index(list.vals,'','',iter.pos),'','',-1) empno from (select id pos from t10) as iter,(select ''7654,7698,7782,7788'' as vals from t1) list where iter.pos<=(length(list.vals)-length(replace(list.vals,'','','''')))+1) x

oracle
select empno,ename,sal,deptno from emp where empno in (select to_number(rtrim(substr(emps,instr(emps,'','',1,iter.pos)+1,instr(emps,'','',1,iter.pos+1)-instr(emps,'','',1,iter.pos)),'','')) emps from (select '',''||''7654,7698,7782,7788''||'','' emps from t1) csv,(select rownum pos from emp) iter where iter.pos<=((length(csv.emps)-length(replace(csv.emps,'','')))/length('',''))-1

postgresql
select empno,ename,sal,deptno from emp where empno in (select cast (empno as integer) as empno from (select split_part(list.vals,'','',iter.pos) as empno from (select id as pos from t10) iter,(select '',''||''7654,7698,7782,7788''||'','' as vals from t1) list where iter.pos<=length(list.vals)-length(replace(ist.vals,'','',''''))) z where length(empno)>0) x

sqlserver
select empno,ename,sal,deptno from emp where empno in (select substring(c,2,charindex('','',c,2)-2) as empno from (select substring(csv.emps,iter.pos,len(csv.emps)) as c from (select '',''+''7654,7698,7782,7788''+'','' as emps from t1) csv,(select id as pos from t100) iter where iter.pos<=len(csv.emps)) x where len(c)>1 and substring(c,1,1)='','') y

四十六、按字母顺序排列字符串
db2
select ename,max(case when pos=1 then c else '''' end)||max(case when pos=2 then c else '''' end)||max(case when pos=3 then c else '''' end)||max(case when pos=4 then c else '''' end)||max(case when pos=5 then c else '''' end)||max(case when pos=6 then c else '''' end) from (select e.ename,cast(substr(e.ename,iter.pos,1) as varchar(100)) c,cast(row_number() over (partition by e.ename order by substr(e.ename,iter.pos,1)) as integer) pos from emp e,(select cast(row_number() over() as integer) pos from emp) iter where iter.pos<=length(e.ename)) x group by ename

mysql
select ename,group_concat(c order by c separator '''') from ( select ename,substr(a.ename,iter.pos,1) c from emp a,(select id pos from t10) iter where iter.pos<=length(a.ename)) x group by ename

oracle
select old_name,new_name from (select old_name,replace(sys_connect-by_path(c,'' ''),'' '') new_name from (select e.ename old_name,row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1)) rn,substr(e.ename,iter.pos,1) c from emp e,(select rownum pos from emp) iter where iter.pos<=length(e.ename) order by 1) x start with rn=1 connect by prior rn=rn-1 and prior old_name=old_name) where length(old_name)=length(new_name)

postgresql
必须先遍历每个字符串，先要知道姓名的最大长度

sqlserver
select ename,max(case when pos=1 then c else '''' end)+max(case when pos=2 then c else '''' end)+max(case when pos=3 then c else '''' end)+max(case when pos=4 then c else '''' end)+max(case when pos=5 then c else '''' end)+max(case when pos=6 then c else '''' end) from (select e.ename,substring(e.ename,iter.pos,1) as c,row_number() over (partition by e.ename order by substring(e.ename,iter.pos,1)) as pos from emp e,(select row_number() over(order by ename) as pos from emp) iter where iter.pos<=len(e.ename)) x group by ename