非常复杂的查询，请高手进来帮忙！感谢！

Renco

对VBA来说呢记录多了也会存在慢的问题,如果只对于一行数据的话,就简单的多了

Scarlett_88

N1两个参数用逗号分开，
N2、N5两个参数用分号分开，
N3、N4、N6 为一个参数，
找出各参数首、尾4位数值并且唯一的查询结果
select * from
(
select iif(isnumeric(left(N1,4)),left(N1,4),null)  as N from 表1
union
select iif(isnumeric(right(left(N1,instr(N1,",")-1),4)),right(left(N1,instr(N1,",")-1),4),null) from 表1
union
select iif(isnumeric(left(right(N1,len(N1)-instr(N1,",")),4)),left(right(N1,len(N1)-instr(N1,",")),4),null) from 表1
union
select iif(isnumeric(right(right(N1,len(N1)-instr(N1,",")),4)),right(right(N1,len(N1)-instr(N1,",")),4),null) from 表1
union
select iif(isnumeric(left(N2,4)),left(N2,4),null) from 表1
union
select iif(isnumeric(right(left(N2,instr(N2,";")-1),4)),right(left(N2,instr(N2,";")-1),4),null) from 表1
union
select iif(isnumeric(left(right(N2,len(N2)-instr(N2,";")),4)),left(right(N2,len(N2)-instr(N2,";")),4),null) from 表1
union
select iif(isnumeric(right(right(N2,len(N2)-instr(N2,";")),4)),right(right(N2,len(N2)-instr(N2,";")),4),null) from 表1
union
select iif(isnumeric(left(N3,4)),left(N3,4),null) from 表1
union
select iif(isnumeric(right(N3,4)),right(N3,4),null) from 表1
union
select iif(isnumeric(left(N4,4)),left(N4,4),null) from 表1
union
select iif(isnumeric(right(N4,4)),right(N3,4),null) from 表1
union
select iif(isnumeric(left(N5,4)),left(N5,4),null) from 表1
union
select iif(isnumeric(right(left(N5,instr(N5,";")-1),4)),right(left(N5,instr(N5,";")-1),4),null) from 表1
union
select iif(isnumeric(left(right(N5,len(N5)-instr(N5,";")),4)),left(right(N5,len(N5)-instr(N5,";")),4),null) from 表1
union
select iif(isnumeric(right(right(N5,len(N5)-instr(N5,";")),4)),right(right(N5,len(N5)-instr(N5,";")),4),null) from 表1
union
select iif(isnumeric(left(N6,4)),left(N6,4),null) from 表1
union
select iif(isnumeric(right(N6,4)),right(N3,4),null) from 表1
)
where N<>null

Scarlett_88

简化代码：
select * from
(
select left(N1,4)  as N from 表1
union
select right(left(N1,instr(N1,",")-1),4) from 表1
union
select left(right(N1,len(N1)-instr(N1,",")),4) from 表1
union
select right(right(N1,len(N1)-instr(N1,",")),4) from 表1
union
select left(N2,4) from 表1
union
select right(left(N2,instr(N2,";")-1),4) from 表1
union
select left(right(N2,len(N2)-instr(N2,";")),4) from 表1
union
select right(right(N2,len(N2)-instr(N2,";")),4) from 表1
union
select left(N3,4)from 表1
union
select right(N3,4) from 表1
union
select left(N4,4)from 表1
union
select right(N4,4) from 表1
union
select left(N5,4) from 表1
union
select right(left(N5,instr(N5,";")-1),4) from 表1
union
select left(right(N5,len(N5)-instr(N5,";")),4) from 表1
union
select right(right(N5,len(N5)-instr(N5,";")),4) from 表1
union
select left(N6,4) from 表1
union
select right(N6,4) from 表1
)
where N<>null and isnumeric(N)

[ 本帖最后由 Scarlett_88 于 2009-7-17 13:25 编辑 ]

samichang

Renco,Scarlett
感谢你们的热心相助，让我克服了这个难题，谢谢~~

Scarlett_88

不超过两个参数的情况通用语句：
select * from
(
select left(N1,4)  as N from 表1
union
select iif(instr(N1,";")>0,right(left(N1,instr(N1,";")-1),4),iif(instr(N1,",")>0,right(left(N1,instr(N1,",")-1),4),null)) from 表1
union
select iif(instr(N1,";")>0,left(right(N1,len(N1)-instr(N1,";")),4),iif(instr(N1,",")>0,left(right(N1,len(N1)-instr(N1,",")),4),null)) from 表1
union
select right(N1,4) from 表1
union
select left(N2,4) from 表1
union
select iif(instr(N2,";")>0,right(left(N2,instr(N2,";")-1),4),iif(instr(N2,",")>0,right(left(N2,instr(N2,",")-1),4),null)) from 表1
union
select iif(instr(N2,";")>0,left(right(N2,len(N2)-instr(N2,";")),4),iif(instr(N2,",")>0,left(right(N2,len(N2)-instr(N2,",")),4),null)) from 表1
union
select right(N2,4) from 表1
union
select left(N3,4) from 表1
union
select iif(instr(N3,";")>0,right(left(N3,instr(N3,";")-1),4),iif(instr(N3,",")>0,right(left(N3,instr(N3,",")-1),4),null)) from 表1
union
select iif(instr(N3,";")>0,left(right(N3,len(N3)-instr(N3,";")),4),iif(instr(N3,",")>0,left(right(N3,len(N3)-instr(N3,",")),4),null)) from 表1
union
select right(N3,4) from 表1
union
select left(N4,4) from 表1
union
select iif(instr(N4,";")>0,right(left(N4,instr(N4,";")-1),4),iif(instr(N4,",")>0,right(left(N4,instr(N4,",")-1),4),null)) from 表1
union
select iif(instr(N4,";")>0,left(right(N4,len(N4)-instr(N4,";")),4),iif(instr(N4,",")>0,left(right(N4,len(N4)-instr(N4,",")),4),null)) from 表1
union
select right(N4,4) from 表1
union
select left(N5,4) from 表1
union
select iif(instr(N5,";")>0,right(left(N5,instr(N5,";")-1),4),iif(instr(N5,",")>0,right(left(N5,instr(N5,",")-1),4),null)) from 表1
union
select iif(instr(N5,";")>0,left(right(N5,len(N5)-instr(N5,";")),4),iif(instr(N5,",")>0,left(right(N5,len(N5)-instr(N5,",")),4),null)) from 表1
union
select right(N5,4) from 表1
union
select left(N6,4) from 表1
union
select iif(instr(N6,";")>0,right(left(N6,instr(N6,";")-1),4),iif(instr(N6,",")>0,right(left(N6,instr(N6,",")-1),4),null)) from 表1
union
select iif(instr(N6,";")>0,left(right(N6,len(N6)-instr(N6,";")),4),iif(instr(N6,",")>0,left(right(N6,len(N6)-instr(N6,",")),4),null)) from 表1
union
select right(N6,4) from 表1
)
where N<>null and isnumeric(N)

附上最后的结果的附件

[ 本帖最后由 Scarlett_88 于 2009-7-17 13:44 编辑 ]

samichang

谢谢！！等我调好了，我把最后的贴出来~~