如何改变一个表的结果,加多一个字段?

Question

表1结构如下:
grade_id      point
  1            10-100
  2            101-500
  3            501-1000现在我想得到,如下的表2:grade_id      point_start       point_end
  1            10                100
  2            101               500
  3            501               1000如何能实现?

Answer 1

select grade_id,
left(point,charindex('-',point)-1) as point_start,
substring(point,charindex('-',point),len(point)-charindex('-',point)) as point_end
from 表1

Answer 2

更正下
drop table 表1
go
create table 表1(grade_id int,point varchar(20))
insert into 表1
select 1,'10-100'
union all select 2,'101-500'
union all select 3,'501-1000'select grade_id,
left(point,charindex('-',point)-1) as point_start,
substring(point,charindex('-',point)+1,len(point)-charindex('-',point)) as point_end
from 表1
/*
grade_id    point_start          point_end            
----------- -------------------- -------------------- 
1           10                   100
2           101                  500
3           501                  1000（所影响的行数为 3 行）
*/

Answer 3

Select grade_id,point_start=Left(point,Charindex('-',point)-1),
                point_end =right(point,Charindex('-',REVERSE(point))-1) 
from 表

Answer 4

select grade_id,point_start=Left(point,charindex('-',point)-1),point_end =right(point,Charindex('-',REVERSE(point))-1) into table2 from table1