22,207
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-09 14:40:56
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([SEQ] varchar(3),[Meno] varchar(39))
insert [tb1]
select 'NO1','ITEMCT数据采集:WO;Item;ProcessID;Second' union all
select 'NO2','MachineCode数据采集:LineCode' union all
select 'NO3','文具领用数据采集:ItemBarCode;EmpNo'
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([datetime] datetime,[AuditDetails] varchar(40))
insert [tb2]
select '2009-12-9 8:01','ITEMCT数据采集:10422;042032;p12203-12;42' union all
select '2009-12-10 8:01','ITEMCT数据采集:10428;040233;p14024-14;22' union all
select '2009-12-11 8:02','ITEMCT数据采集:15022;051220;p15022-03;91' union all
select '2009-12-12 8:02','文具领用数据采集:104222233;10220' union all
select '2009-12-13 8:02','文具领用数据采集:104222233;10221' union all
select '2009-12-14 8:02','文具领用数据采集:104222233;10222' union all
select '2009-12-15 8:02','MachineCode数据采集:LN0021292' union all
select '2009-12-16 8:02','MachineCode数据采集:LN0021293' union all
select '2009-12-17 8:02','MachineCode数据采集:LN0021294' union all
select '2009-12-18 8:02','MachineCode数据采集:LN0021295' union all
select '2009-12-19 8:02','MachineCode数据采集:LN0021296' union all
select '2009-12-20 8:02','MachineCode数据采集:LN0021297' union all
select '2009-12-21 8:02','MachineCode数据采集:LN0021298' union all
select '2009-12-22 8:02','MachineCode数据采集:LN0021299' union all
select '2009-12-23 8:02','MachineCode数据采集:LN0021300' union all
select '2009-12-24 8:02','MachineCode数据采集:LN0021301' union all
select '2009-12-25 8:02','ITEMCT数据采集:10422;042032;p12203-15;30' union all
select '2009-12-26 8:02','ITEMCT数据采集:10428;040233;p14024-19;82' union all
select '2009-12-27 8:02','ITEMCT数据采集:15022;051220;p15022-09;22'
--------------开始查询--------------------------
SELECT
COL1 = PARSENAME(REPLACE(AuditDetails,';','.'),4), ----替换一下 '.' 因为 parsename 只认 '.'
COL2 = PARSENAME(REPLACE(AuditDetails,';','.'),3),
COL3 = PARSENAME(REPLACE(AuditDetails,';','.'),2),
COL4 = PARSENAME(REPLACE(AuditDetails,';','.'),1)
FROM
tb1 a,tb2 b
where
substring(a.Meno,1,charindex(':',a.Meno)-1)=substring(b.AuditDetails,1,charindex(':',b.AuditDetails)-1)
and
substring(a.Meno,1,charindex(':',a.Meno)-1)='ITEMCT数据采集'
----------------结果----------------------------
/* COL1 COL2 COL3 COL4
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ITEMCT数据采集:10422 042032 p12203-12 42
ITEMCT数据采集:10428 040233 p14024-14 22
ITEMCT数据采集:15022 051220 p15022-03 91
ITEMCT数据采集:10422 042032 p12203-15 30
ITEMCT数据采集:10428 040233 p14024-19 82
ITEMCT数据采集:15022 051220 p15022-09 22
(6 行受影响)
*/