34,593
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-24 12:45:57
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[物料] varchar(13)
)
insert [test]
select 'A001,102,499' union all
select 'A003,1023,39' union all
select 'A001-A,2.2,32'
go
;with t
as(
select
LEFT([物料],CHARINDEX(',',[物料])-1) as 品名,
cast(SUBSTRING([物料],CHARINDEX(',',[物料])+1,CHARINDEX(',',[物料],CHARINDEX(',',[物料])+1)-CHARINDEX(',',[物料])-1) as numeric(8,2))as 长度,
cast(RIGHT([物料],CHARINDEX(',',REVERSE([物料]))-1) as numeric(8,2)) as 宽度
from
test
)
select
品名,
长度,
宽度,
长度*宽度 as 面积
from
t
/*
品名 长度 宽度 面积
-------------------------------------------------------
A001 102.00 499.00 50898.0000
A003 1023.00 39.00 39897.0000
A001-A 2.20 32.00 70.4000
*/
--创建数据开始
if(object_id('a') is not null) drop table a
go
create table a
(
material varchar(4),
length decimal(5,1),
width decimal(5,1)
)
go
insert into a
select 'A001',102,499 union all
select 'A003',1023,39 union all
select 'A001-A',2.2,32
go
--创建数据结束
--语句
select material as '品名',length as'长度',width as '宽度'
,cast(length as varchar)+'*'+cast(width as varchar) as '面积'
,width*length as '面积值'
from a
--结果展示
/*
品名 长度 宽度 面积 面积值
---- --------------------------------------- --------------------------------------- ------------------------------------------------------------- ---------------------------------------
A001 102.0 499.0 102.0*499.0 50898.00
A003 1023.0 39.0 1023.0*39.0 39897.00
A001 2.2 32.0 2.2*32.0 70.40
(3 行受影响)
*/