select table1的字段,table2.姓名(将table1.id替换为table2.姓名) from table1,table2 where table1.id=table2.id
你可以这样写: select b.name name1,a.id2 name2 from table1 a,table2 b where a.id1=b.id union select a.id1 name1,b.name name2 from table1 a,table2 b where a.id2=b.id
或不用union用intersect也可以,看你的需要了!! :)
sun_lz(sun_xp):我觉得他的表的结构不是这样的,有可能table1.id1和table2.id根本就没有对应关系,只是table1.id2和table2.id有对应关系。如果是这样的话: select b.name as name,b.id,a.id1,a.id2 from table2 b,table1 a where a.id1=b.id and a.id2=b.id ...
select b.name name1,'' name2 from table1 a,table2 b where a.id1=b.id union select '' name1,b.name name2 from table1 a,table2 b where a.id2=b.id 但是这样是有两条记录! 你要是要一条,我劝你还是把ID,领料,制单,改为char类型吧! 为什么要用int类型呢??????不灵活!!!!!!
可能你是这个意思,下面这SQL语句有点乱,不过,我想你会看得懂我所写的:select table1.name_id,table2.name,table3.领料_desc,table4.制单_desc, from table1 left join table2 on table1.name_id=table2.name_id left join table3 on table1.领料_id=table3.领料_id left join table4 on table1.制单_id=table4.制单_id
select table1的字段,table2.姓名(将table1.id替换为table2.姓名) from table1,table2 where table1.id=table2.id
select table1.id, a.姓名, b.姓名 from table inner join table2 as a on table1.领料 = table2.id inner join table2 as b on table1.制单 = table2.idIs That OK?
sorry, 应该改成select table1.id, a.姓名, b.姓名 from table inner join table2 as a on table1.领料 = a.id inner join table2 as b on table1.制单 = b.id
对不起,再改一点,这次应该全对了...sorry againselect table1.id, a.姓名, b.姓名 from table1 inner join table2 as a on table1.领料 = a.id inner join table2 as b on table1.制单 = b.id
试试我的: select id, (select 姓名 from table2 where id=table1.领料) as 领料, (select 姓名 from table2 where id=table1.制单) as 制单 from table1 order by id
from table1,table2
where table1.id=table2.id
select b.name name1,a.id2 name2 from table1 a,table2 b where a.id1=b.id
union
select a.id1 name1,b.name name2 from table1 a,table2 b where a.id2=b.id
:)
select b.name as name,b.id,a.id1,a.id2 from table2 b,table1 a where a.id1=b.id and a.id2=b.id
...
sun_lz说的办法因为那两个select 的name1,和name2列数据类型不匹配(因为b.name为char类型),好像不能那么用,有什么办法吗?
union
select '' name1,b.name name2 from table1 a,table2 b where a.id2=b.id
但是这样是有两条记录!
你要是要一条,我劝你还是把ID,领料,制单,改为char类型吧!
为什么要用int类型呢??????不灵活!!!!!!
可能你是这个意思,下面这SQL语句有点乱,不过,我想你会看得懂我所写的:select table1.name_id,table2.name,table3.领料_desc,table4.制单_desc, from table1 left join table2 on table1.name_id=table2.name_id left join table3 on table1.领料_id=table3.领料_id left join table4 on table1.制单_id=table4.制单_id
from table1,table2
where table1.id=table2.id
table1
id 领料 制单 ……
1 2 1 ……
2 3 4 ……
……table2
id 姓名 ……
1 name1 ……
2 name2 ……
3 name3 ……
4 name4 ……
……
然后我希望的查询结果是
id(table1的) 领料 制单 ……
1 name2 name1 ……
2 name3 name4 ……
……
我应该怎么写查询语句?
table1.id,
a.姓名,
b.姓名
from table
inner join table2 as a on table1.领料 = table2.id
inner join table2 as b on table1.制单 = table2.idIs That OK?
table1.id,
a.姓名,
b.姓名
from table
inner join table2 as a on table1.领料 = a.id
inner join table2 as b on table1.制单 = b.id
table1.id,
a.姓名,
b.姓名
from table1
inner join table2 as a on table1.领料 = a.id
inner join table2 as b on table1.制单 = b.id
select id,
(select 姓名 from table2 where id=table1.领料) as 领料,
(select 姓名 from table2 where id=table1.制单) as 制单
from table1
order by id