tempTable中只有产品代号字段。
p1,p2,p3为产品代号
要求在显示的时候在显示出产品的名称。如:p1,p1name,p2,p2name,p3,p3name这样。如何写??
-----------
产品表代号名称对照表prdt结构如下:
代号,名称
prdno,prdname
p1,p2,p3为产品代号
要求在显示的时候在显示出产品的名称。如:p1,p1name,p2,p2name,p3,p3name这样。如何写??
-----------
产品表代号名称对照表prdt结构如下:
代号,名称
prdno,prdname
p1 p1name
p2 p2name
p3 p3name
dim con as Connection
dim rs as Recordset
dim sql as string
dim str as string
..................数据库基础`!!不写了sql="select distinct prdno,prdname from tempTable "
rs.open sql,con,,,
str="select "
if rs.RecordCount>0 then
for i= to rs.RecordCount-1
str=str+ rs.Fields(0)+","+rs.Fields(1)+","
rs.movenext
next
str=mid(str,1,len(str)-1)
str=str+" from tempTable"
set rs=new Recordset
rs.open str,con,,,
Set MSHFlexGrid1.DataSource =rs endif 未测试!!
可能有bug 现写的例子
insert into tempTable(p1,p2,p3) values ('100','200','600')drop table prdt
create table prdt(prdno varchar(10),prdname varchar(20))
insert into prdt(prdno,prdname) values ('100','A')
insert into prdt(prdno,prdname) values ('200','B')
insert into prdt(prdno,prdname) values ('300','C')
insert into prdt(prdno,prdname) values ('400','D')
insert into prdt(prdno,prdname) values ('500','E')
insert into prdt(prdno,prdname) values ('600','F')
查询tempTable
得到
100,A,200,B,300,C
200,B,400,D,500,E
100,A,500,E,600,F
100,A,200,B,600,F
不是象上面说的那样,那是那样我也用不着问了!!
select t.p2,(select p.prdname from prdt AS p where p.prdno=t.p2) AS p2Name,
select t.p3,(select p.prdname from prdt AS p where p.prdno=t.p3) AS p3Name,
from tempTable AS t
t.p2,(select p.prdname from prdt AS p where p.prdno=t.p2) AS p2Name,
t.p3,(select p.prdname from prdt AS p where p.prdno=t.p3) AS p3Name
from tempTable AS t