CREATE TABLE [Mfg_TRANHIS] (
[THTRID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THOPID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THDATE] [datetime] NOT NULL ,
[THTIME] [datetime] NOT NULL ,
[THFLAG] [char] (1) COLLATE Latin1_General_BIN NOT NULL ,
[THBODY] [varchar] (250) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO
[THTRID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THOPID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THDATE] [datetime] NOT NULL ,
[THTIME] [datetime] NOT NULL ,
[THFLAG] [char] (1) COLLATE Latin1_General_BIN NOT NULL ,
[THBODY] [varchar] (250) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 16:00:02.000 0 ~C~M~I~MA01923 ~001~CAF94416~N~001 ~170~CAP96174~0~MT~MAT ~O~022803009 ~2400~559~~~~~120103~N~.8322~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:59:30.000 0 ~C~M~I~MA01923 ~001~CAF94416~N~001 ~170~CAP96174~0~RT~RET ~O~022803009 ~120~307~~~~~120103~N~.8322~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:57:21.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~130~CAP70196~409~MT~MAT ~O~082603014PA02092 ~8496~496~~~~~~~.690552~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:56:31.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~120~CAP70194~713~MT~MAT ~O~082003013PAE01826 ~3200~713~~~~~~~.732~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:55:36.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~110~CAP70193~213~MT~MAT ~O~111903015NA00398 ~6840~213~~~~~~~.47196~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:54:57.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~100~CAP70182~500~MT~MAT ~O~082003026PAE01826 ~500~500~~~~~~~5.394~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 15:52:13.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~080~CAP60224~297~MT~MAT ~O~030603042 ~408~320~~~~~~~2.774~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 13:51:27.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~216~CAR40249~0~RT~RET ~O~100903031PA01976 ~17000~17000~~~~~~~1.4076~FT~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 13:50:15.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~210~CAP34249~0~WC~BBK ~O~103102020 ~4500~4500~~~~~~~6.4584~EA~2~Y~I~~
PICK GZB 2003-12-10 00:00:00.000 1900-01-01 13:50:02.000 0 ~C~M~X~MA01923 ~001~CAF94416~N~001 ~212~CAP34253~0~WC~BBK ~O~111102018 ~4500~4500~~~~~~~6.4584~EA~2~Y~I~~
insert INTO Mfg_TRANHIS
VALUES
('PICK',
'GZB',
'2003-12-10 00:00:00.000',
'1900-01-01 13:50:02.000',
0, '~C~M~X~MA01923 ~001~CAF94416~N~001 ~212~CAP34253~0~WC~BBK ~O~111102018 ~4500~4500~~~~~~~6.4584~EA~2~Y~I~~ ')
ALTER Function GetHistoryStr(@Str Varchar(1000),@Pos Integer)
--@Str 包含有'~'的字符串 对应字段是THBODY
--@Pos '~'字符的位置
--举例:字符:~C~M~X~MA01923 ~ 如果我要取MA01923这个值,那应该在第4次出现~和第5现出现~中的字符串取出,所以@Pos是5
returns Varchar(100)
as
beginDECLARE @I INT ,@J INT,@K INTSELECT @I=1 --记录当前~的位置
SELECT @J=0 --记录@POS前一个~的位置
SELECT @K=0 --记录~是出现次数While (@K<@Pos) --当~出现次数小于指定的次数5
beginset @I=Charindex('~',@Str,@I) 找~if @I>0 set @K=@K+1 --如果找到~就加1if @J=0 and @Pos-@K=1 set @J=@I --如果找到~的提定位置@Pos 5 的前一次出现~的位置4则将~位置4的实际字符位置@I给@JSet @I=@I+1
endReturn(SubString(@Str,@J+1,@I-@J-2)) --返回字符串STR,~出现的第4次和第5个之间的字符串
end
如果不是必须使用一条sql语句来完成的话。建议使用存储过程。采用临时表变量来处理。declare @var_table table(MoNumber varchar(100),
ParentItem char(8),
Item varchar(8),
QTY float,
LineNumber char(3))
insert into @var_table(MoNumber, ParentItem,Item ,QTY, LineNumber)SELECT MoNumber, ParentItem,Item ,--AVG(QTY) as QTY,
QTY, LineNumber
FROM GetPickForMO
WHERE TransDate<='2003.12.31' And TransDate>='2003.12.1'select * from @var_tableselect MoNumber, ParentItem,Item ,AVG(QTY) as QTY,LineNumber
from @var_table
GROUP BY MoNumber, ParentItem,Item, LineNumber
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetHistoryStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetHistoryStr]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetPickForMO]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[GetPickForMO]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mfg_TRANHIS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Mfg_TRANHIS]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create Function GetHistoryStr(
@Str Varchar(1000),
@Pos Integer --~的个数
)
returns Varchar(100)
as
/*
功能:返回传入分隔符数目与前个分隔符之间的数据内容.
如:5:返回第5个分隔符与第4个分隔符之间的内容。
*/
beginDECLARE @I INT ,@J INT,@K INTSELECT @I=1--记录~在字符串中的位置
SELECT @J=0
SELECT @K=0 --记录~的个数While (@K<@Pos)
beginset @I=Charindex('~',@Str,@I)if @I>0 set @K=@K+1if @J=0 and @Pos-@K=1 set @J=@ISet @I=@I+1
endReturn(SubString(@Str,@J+1,@I-@J-2)) --=SubString(@Str,@J+1,((@I-1)-@J)-1)) 传入分隔符数目与前个分隔符之间的数据内容.
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE TABLE [dbo].[Mfg_TRANHIS] (
[THTRID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THOPID] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[THDATE] [datetime] NOT NULL ,
[THTIME] [datetime] NOT NULL ,
[THFLAG] [char] (1) COLLATE Latin1_General_BIN NOT NULL ,
[THBODY] [varchar] (250) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOcreate View GetPickForMO
asSelect TransDate=THDATE,
TransTime=THTIME,
dbo.GetHistoryStr(THBODY,5) as MoNumber,
dbo.GetHistoryStr(THBODY,11) as Item,
ParentItem=dbo.GetHistoryStr(THBODY,7),
QTY=CASE dbo.GetHistoryStr(THBODY,4)
WHEN 'X' then cast (dbo.GetHistoryStr(THBODY,18) as Float)*-1
else
cast (dbo.GetHistoryStr(THBODY,18) as Float)
end,
PickType=dbo.GetHistoryStr(THBODY,4),
LineNumber=dbo.GetHistoryStr(THBODY,6) ,
Lot=dbo.GetHistoryStr(THBODY,16) ,
Bin=dbo.GetHistoryStr(THBODY,20)
from Mfg_TRANHIS where
THTRID='PICK' and Substring(THBODY,4,1)='M' and substring(THBODY,2,1)='C'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SELECT
MoNumber=dbo.GetHistoryStr(THBODY,5)
,ParentItem=dbo.GetHistoryStr(THBODY,7)
FROM Mfg_TRANHIS
GROUP BY dbo.GetHistoryStr(THBODY,5) ,dbo.GetHistoryStr(THBODY,7)
FROM GetPickForMO
WHERE TransDate<='2003.12.31' And TransDate>='2003.12.1'
GROUP BY MoNumber, ParentItem,Item, LineNumber
结果就正确了,原因是以字段的一个部分分组,而不存在实际的分组对象,即无该实际字段单独运行会出错,但你是把数据放在视图里,表面上语法没有错,掩盖了问题,但结果有问题了下面的语句就会出错select a from x1
group by dbo.GetHistoryStr(a,1),dbo.GetHistoryStr(a,2)
as
select a1=left(a,3),
a2=substring(a,5,3) from x1select * from v2 group by a1,a2结果是对的换成
create view v1
as
select a1=pubs.dbo.GetHistoryStr(a,1),
a2=pubs.dbo.GetHistoryStr(a,2)
from x1结果就不对了
可能是BUG,用Group by就有问题
--一个简单的函数
create function f_str(@str sysname,@i int)
returns char(1)
as
begin
return(substring(@str,@i,1))
end
go--下面是自定义函数和直接用系统函数的测试
/*--用自定义函数--*/
--有group by
select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from sysobjects where xtype='U' and status<=0
group by dbo.f_str(name,1),dbo.f_str(name,2)--无group by
select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from sysobjects where xtype='U' and status<=0/*--直接用系统函数--*/
--有group by
select a=substring(name,1,1),b=substring(name,2,1)
from sysobjects where xtype='U' and status<=0
group by substring(name,1,1),substring(name,2,1)--无group by
select a=substring(name,1,1),b=substring(name,2,1)
from sysobjects where xtype='U' and status<=0
go--删除测试的自定义函数
drop function f_str/*--测试结果
a b
---- ----
d d(所影响的行数为 1 行)a b
---- ----
d t(所影响的行数为 1 行)a b
---- ----
d t(所影响的行数为 1 行)a b
---- ----
d t(所影响的行数为 1 行)
--*/
select a=dbo.f_str(name,1),b=dbo.f_s
tr(name,2)
from(select name='abcd') a
group by dbo.f_str(name,1),dbo.f_str(name,2)--如果是两行,就会错
select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from(select name='abcd'
union all select name='abcd') a
group by dbo.f_str(name,1),dbo.f_str(name,2)--如果是表,既然只有一条记录,也会错
declare @t table(name sysname)
insert into @t values('abcd')select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from @t a
group by dbo.f_str(name,1),dbo.f_str(name,2)
--所以我觉得,应该是SQL在处理这类语句时,某个机制有问题:BUG