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
*/