日期:2014-05-17 浏览次数:20623 次
---创建表
CREATE TABLE [tb] (
[voucher] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[item_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--插入数据
insert into tb values ('S01','011111211')
insert into tb values ('S01','021113331')
insert into tb values ('S01','03122111')
insert into tb values ('S01','0411121')
insert into tb values ('S01','0111211')
insert into tb values ('S02','0111123')
insert into tb values ('S02','031112')
insert into tb values ('S03','011112')
insert into tb values ('S03','011113')
insert into tb values ('S03','031113')
---创建表
CREATE TABLE [tb] (
[voucher] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[item_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--插入数据
insert into tb values ('S01','011111211')
insert into tb values ('S01','021113331')
insert into tb values ('S01','03122111')
insert into tb values ('S01','0411121')
insert into tb values ('S01','0111211')
insert into tb values ('S02','0111123')
insert into tb values ('S02','031112')
insert into tb values ('S03','011112')
insert into tb values ('S03','011113')
insert into tb values ('S03','031113')
;WITH cte AS
(
SELECT *, ch=(CASE LEFT(item_no,2) WHEN '01' THEN 'A' WHEN '02' THEN 'B' WHEN '03' THEN 'C' WHEN '04' THEN 'D' ELSE '' END) FROM tb