Delphi實現：SQL交叉表復合查詢問題？
SQL表：Workplan,Function,Repaircode,RepairStation,ErrorCode,RootCause
工單表[Workplan]：
WorkID WorkOrder SN Model F1 F1Alias F2 F2Alias F3 F3Alias ...
1 123456 73E0001 N12 1 SMT 1 PTH 1 DIP ...
.... ...... ..... ... .. ... .. ... ... ... ...
說明：WorkID為自動增加,WorkOrder為工單號碼,SN為流水號碼,Model為機種名稱,
F1為測試站(預設值為0),F1Alias為測試站別名.....依次類推.
測試站表[Function]:
workorder functioncode scanno counts
123456 F1 73E0001 1
123456 F1 73E0002 0
123456 F2 73E0001 1
.... ... ..... .....
說明:WorkOrder為工單號,functioncode為測試站別,scanno為序列號,counts為不良次數,這是測試站，如有不良就會送到維修站去維修.
維修站表[RepaiStation]:
workorder functioncode scanno errorcode repaircode location
123456 F1 73E0001 1 1 R1
123456 F2 73E0002 4 2 C1
123456 F3 73E0001 2 1 R2
.... .... ..... .... ..... ....
說明:此為維修站,errorcode是不良現象代碼,repaircode是不良原因代碼,location是不良位置,維修時會記錄送來維修的序列號,對應表1的scanno,errorcode對應表3的errorid,repaircode對應表4的.
現象表[ErrorCode]:
ErrorID symptom
1 材料不良
2 作業不良
3 不工作
4 良品
.... .....
說明:errorid是int自動增加,symptom是不良現象.
原因表[RootCause]:
RepairID cause
1 短路
2 斷列
3 破損
... .....
說明:repairid是int自動增加,cause是不良原因.
用語句實現如下結果:
workorder ScanNO F1 F1Symptom F1Cause F1location F2 ..
123456 73E0001 NG 材料不良 短路 R1 OK
123456 73E0002 OK
123456 73E0003 NG 作業不良 開路 C1 OK ..
123456 73E0005 NG 設計不良 刮傷 B3 OK ..
存儲過程如下：
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[ '+Functioncode+ ']=
case (sum(case shopfloor_function.Functioncode when ' ' '+t.Functioncode+ ' ' ' then Counts else 0 end))
when 0 then '+ ' ' ' '+ 'OK '+ ' ' ' '+ ' else '+ ' ' ' '+ 'NG '+ ' ' ' '+ ' end '
from (select distinct Functioncode from shopfloor_function) t order by t.functioncode
set @sql= 'select shopfloor_function.workorder,shopfloor_function.scanno,shopfloor_repairstation.location '+@sql+ ' from
shopfloor_function,shopfloor_repairstation
group by shopfloor_function.workorder,shopfloor_function.scanno,shopfloor_repairstation.location '
print @sql
exec(@sql)
go
只能查詢出:
Workorder ScanNO F1 F2 F3 ...
123456 73E0001 NG NG OK ...
123456 73E0002 OK OK NG ...
之後更新存儲過程為以下：
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[ '+Functioncode+ ']= case (sum(case shopfloor_function.Functioncode
when ' ' '+t.Functioncode+ ' ' ' then Counts else 0 end))
when 0 then '+ ' ' ' '+ 'OK '+ ' ' ' '+ ' else '+ ' ' ' '+ 'NG '+ ' ' ' '+ ' end '
from (select distinct Functioncode from shopfloor_function) t order by t.functioncode
set @sql= 'select shopfloor_function.workorder,shopfloor_function.scanno,
shopfloor_repairstation.location+@sql+ ' from
shopfloor_function,shopfloor_repairstation
group by shopfloor_function.workorder,shopfloor_function.scanno,
shopfloor_repairstation.location '
print @sql
exec(@sql)
go
結果如下：
WorkOrder ScanNo Location F1 F2 F3 F4
11111173H00005 C1 NGOKOKOK
11111173H00004 R1NGOKOKOK
11111173J00001 R1NGOKOKOK
... .... ... ... ... ... ...
請教各位高手如何去實現幾個表關聯起來的交叉表查詢!!!如上面想要實現的結果!
我在這謝謝大家了,分不夠可再加!!!
MSN:kye_jufei@hotmail.com
[解决办法]
--已改，楼主测试。已用到表1，开始我以为是用表2的errorcode判断NG还是OK
--创建测试环境
create table 表1(workorder int,functioncode varchar(10),scanno varchar(20),counts int)
create table 表2(workorder int,functioncode varchar(10),scanno varchar(20)
,errorcode int,repaircode int,location varchar(10))
create table 表3(errorid int,symptom varchar(10))
create table 表4(repairid int,cause varchar(10))
--插入测试数据
insert 表1(workorder,functioncode,scanno,counts)
select '123456 ', 'F1 ', '73E0001 ', '1 ' union all
select '123456 ', 'F1 ', '73E0002 ', '0 ' union all
select '123456 ', 'F2 ', '73E0001 ', '1 '
insert 表2(workorder,functioncode,scanno,errorcode,repaircode,location)
select '123456 ', 'F1 ', '73E0001 ', '1 ', '1 ', 'R1 ' union all
select '123456 ', 'F2 ', '73E0002 ', '4 ',null,null union all
select '123456 ', 'F3 ', '73E0001 ', '2 ', '1 ', 'R2 '
insert 表3(errorid,symptom)
select '1 ', '材料不良 ' union all
select '2 ', '操作不良 ' union all
select '3 ', '不工作 ' union all
select '4 ', '良品 '
insert 表4(repairid,cause)
select '1 ', '短路 ' union all
select '2 ', '斷列 ' union all
select '3 ', '破損 '
--求解过程
declare @sql varchar(8000) set @sql = 'select workorder,ScanNO '
select @sql = @sql + ',min(case when counts > 0 and functioncode = ' ' ' + functioncode
+ ' ' ' then ' 'NG ' ' when counts = 0 and functioncode = ' ' ' + functioncode
+ ' ' ' then ' 'OK ' ' else null end) as '+functioncode
+ ',max(case when functioncode = ' ' ' + functioncode
+ ' ' ' then symptom else null end) as '+functioncode+ 'symptom '
+ ',max(case when functioncode = ' ' ' + functioncode
+ ' ' ' then cause else null end) as '+functioncode+ 'cause '
+ ',max(case when functioncode = ' ' ' + functioncode
+ ' ' ' then location else null end) as '+functioncode+ 'location '
from (select distinct functioncode from 表2) x
order by functioncode
select @sql = @sql
+ ' from(
select _2.workorder,_2.functioncode,_2.scanno,_3.symptom,_4.cause
,_2.location,isnull(_1.counts,0) as counts
from 表2 _2
join 表3 _3 on _2.errorcode = _3.errorid
left join 表1 _1
on _1.workorder = _2.workorder
and _1.functioncode = _2.functioncode
and _1.scanno = _2.scanno
left join 表4 _4 on _4.repairid = _2.repaircode) x
group by workorder,ScanNO
order by workorder,ScanNO '
exec(@sql)
--删除测试环境
drop table 表1,表2,表3,表4
/*--测试结果
workorder ScanNO F1 F1symptom F1cause F1location F2 F2symptom F2cause F2location F3 F3symptom F3cause F3location
123456 73E0001 NG 材料不良 短路 R1 NULL NULL NULL NULL OK 操作不良 短路 R2
123456 73E0002 NULL NULL NULL NULL OK 良品 NULL NULL NULL NULL NULL NULL
*/
