1.TraceRecord表結構:
CREATE TABLE [dbo].[TraceRecord] (
[RcdID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[JobNo] [varchar] (15) ,
[TraceRefNo] [varchar] (50) ,
[MachineNo] [nvarchar] (50) ,
[Department] [nvarchar] (100) ,
[UserID] [varchar] (25) ,
[Event] [nvarchar] (50) ,
[TheTime] [datetime] ,
[MeMo] [nvarchar] (200) ,
[ProcessQty] [decimal](18, 5) ,
[ProcessUnit] [nvarchar] (25) ,
[status] [nvarchar] (10) ,
[CtatusCorol] [nvarchar] (100) ,
[status2] [nvarchar] (50) ,
[made] [nvarchar] (50)
) ON [PRIMARY]
2.TraceLimitRights 表結構
CREATE TABLE [dbo].[TraceLimitRights] (
[UserID] [nvarchar] (50) ,
[Department] [nvarchar] (50) ,
[OperationChina] [nvarchar] (100) ,
[OperationEng] [varchar] (200) ,
[UsrEnable] [varchar] (10) ,
[DepEnable] [varchar] (10) ,
[Temp1] [nvarchar] (100) ,
[Temp2] [nvarchar] (100) ,
[Temp3] [decimal](18, 5)
) ON [PRIMARY]
CREATE TABLE [dbo].[TraceRecord] (
[RcdID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[JobNo] [varchar] (15) ,
[TraceRefNo] [varchar] (50) ,
[MachineNo] [nvarchar] (50) ,
[Department] [nvarchar] (100) ,
[UserID] [varchar] (25) ,
[Event] [nvarchar] (50) ,
[TheTime] [datetime] ,
[MeMo] [nvarchar] (200) ,
[ProcessQty] [decimal](18, 5) ,
[ProcessUnit] [nvarchar] (25) ,
[status] [nvarchar] (10) ,
[CtatusCorol] [nvarchar] (100) ,
[status2] [nvarchar] (50) ,
[made] [nvarchar] (50)
) ON [PRIMARY]
2.TraceLimitRights 表結構
CREATE TABLE [dbo].[TraceLimitRights] (
[UserID] [nvarchar] (50) ,
[Department] [nvarchar] (50) ,
[OperationChina] [nvarchar] (100) ,
[OperationEng] [varchar] (200) ,
[UsrEnable] [varchar] (10) ,
[DepEnable] [varchar] (10) ,
[Temp1] [nvarchar] (100) ,
[Temp2] [nvarchar] (100) ,
[Temp3] [decimal](18, 5)
) ON [PRIMARY]
字段类型用:Nchar,Nvarchar,Ntext,而且插入数据时用:insert 表 (字段)values(N'中方') 一定要用N''的形式.改库
ALTER DATABASE database
COLLATE ....
改字段
alter table 表名 alter column 字段名 类型 collate ....
或设计表中修改.
@jobno nvarchar(50)
as
declare @sql nvarchar (4000)
set @sql=N'select JobNo,TraceRefNo'
select @sql=@sql+N',max(case when Department ='''+Department+''' and Event='''+operationeng+''' then theTime end) ['+Department+operationChina+']' from (select distinct department,operationeng,operationChina from tracelimitrights) a
set @sql=@sql+N'from TraceRecord where jobno='''+@jobno+''' group by JobNo,TraceRefNo'exec sp_executesql @sql
go
把所有过程中的字符常量前面加N 如: '大力' ==> N'大力'
修改后的存儲過程我試了,字段的中文名字可以看到了,但是Department為中文時,查不出時間執行的語句打印如下:
select JobNo,TraceRefNo,max(case when Department ='MIS' and Event='ToEnter' then theTime end) [MIS入單],max(case when Department ='MIS' and Event='ToExit' then theTime end) [MIS出單],max(case when Department ='MIS' and Event='ToPrint' then theTime end) [MIS安排印刷機],max(case when Department ='MIS' and Event='ToTypesetting' then theTime end) [MIS印刷排版],max(case when Department ='印前CTP' and Event='ToEnter' then theTime end) [印前CTP入單],max(case when Department ='印前CTP' and Event='ToExit' then theTime end) [印前CTP出單],max(case when Department ='印前產品管理科' and Event='ToEnter' then theTime end) [印前產品管理科入單],max(case when Department ='印前產品管理科' and Event='ToExit' then theTime end) [印前產品管理科出單],max(case when Department ='客務部' and Event='ToEnter' then theTime end) [客務部入單],max(case when Department ='客務部' and Event='ToExit' then theTime end) [客務部出單],max(case when Department ='程控部' and Event='ToEnter' then theTime end) [程控部入單],max(case when Department ='程控部' and Event='ToExit' then theTime end) [程控部出單]from TraceRecord where jobno='dpn03080008' group by JobNo,TraceRefNo
不知道怎樣可以得到如下語句:select JobNo,TraceRefNo,max(case when Department =N'MIS' and Event='ToEnter' then theTime end) [MIS入單],max(case when Department =N'MIS' and Event='ToExit' then theTime end) [MIS出單],max(case when Department =N'MIS' and Event='ToPrint' then theTime end) [MIS安排印刷機],max(case when Department =N'MIS' and Event=N'ToTypesetting' then theTime end) [MIS印刷排版],max(case when Department =N'印前CTP' and Event='ToEnter' then theTime end) [印前CTP入單],max(case when Department =N'印前CTP' and Event='ToExit' then theTime end) [印前CTP出單],max(case when Department =N'印前產品管理科' and Event='ToEnter' then theTime end) [印前產品管理科入單],max(case when Department =N'印前產品管理科' and Event='ToExit' then theTime end) [印前產品管理科出單],max(case when Department =N'客務部' and Event='ToEnter' then theTime end) [客務部入單],max(case when Department =N'客務部' and Event='ToExit' then theTime end) [客務部出單],max(case when Department =N'程控部' and Event='ToEnter' then theTime end) [程控部入單],max(case when Department =N'程控部' and Event='ToExit' then theTime end) [程控部出單]from TraceRecord where jobno='dpn03080008' group by JobNo,TraceRefNo請指教...
查出來啦!!!!
如下代碼:
CREATE PROCEDURE TranceRpt01
@jobno nvarchar(50)
as
declare @sql nvarchar (4000)
set @sql=N'select JobNo,TraceRefNo'
select @sql=@sql+N',max(case when Department ='+char(78)+''''+Department+''' and Event='''+operationeng+''' then theTime end) ['+Department+operationChina+']'
from (select distinct department,operationeng,operationChina from tracelimitrights) a
set @sql=@sql+N'from TraceRecord where jobno='''+@jobno+''' group by JobNo,TraceRefNo'
print @sql
exec sp_executesql @sql
GO多謝!!
@jobno nvarchar(50)
as
declare @sql nvarchar (4000)
set @sql=N'select JobNo,TraceRefNo'
select @sql=@sql+N',max(case when Department =N'''+Department+''' and Event=N'''+operationeng+''' then theTime end) ['+Department+operationChina+']' from (select distinct department,operationeng,operationChina from tracelimitrights) a
set @sql=@sql+N'from TraceRecord where jobno='''+@jobno+''' group by JobNo,TraceRefNo'exec sp_executesql @sql
go