sqlserver 与 oracle实现同样功能

一、源表数据如下：

 

实现功能：实现name字段以逗号结尾

oracle: select id,case substr(name,-1) when ',' then name else name || ',' end from test;或select id,decode(substr(name,-1),',',name,name||',') from test;

sqlserver: select id,case right(name,1) when ',' then name else name + ',' end from test;

 

2. 修改表的字段类型: alter table teacher modify column wage float(6,2);

3.通过双表连接查询，找出某个ID最大的age,如

1--29

3--31

SQL> select * from t;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> select * from t2;

        ID        AGE
---------- ----------
         1         28
         1         29
         3         30
         3         31

 

答案：select p.*,q.mage from t p left join (select id,max(age) as mage from t2 group by id) q on p.id = q.id