表A有4列
id1 id2 id3 reason表B有2列
id name表A数据
a1 a2 a3 备注
a2 a3 a4 备注
a3 a3 a5 备注表B数据
a1 错误1
a2 错误2
a3 错误3
a4 错误4
a5 错误5最后要检索出来
错误1 错误2 错误3 备注
错误2 错误3 错误4 备注
错误3 错误3 错误5 备注
id1 id2 id3 reason表B有2列
id name表A数据
a1 a2 a3 备注
a2 a3 a4 备注
a3 a3 a5 备注表B数据
a1 错误1
a2 错误2
a3 错误3
a4 错误4
a5 错误5最后要检索出来
错误1 错误2 错误3 备注
错误2 错误3 错误4 备注
错误3 错误3 错误5 备注
解决方案 »
- 怎么进行select条件判断
- 如何用SQL 语句将UNICODE(如〹)代码转换成汉字?
- 一个事务的基础问题
- 请教如何在允许null值的字段创建唯一索引,但又要允许null值重复。
- SQL CTE递归问题
- 50分@@@搞了半天,无解,字符转换为日期 CONVERT( @date+' 23:59:59' , datetime))
- 高手请进,关于表插入问题!
- 在线等待....!2个表互相复制数据的问题?
- ====高分求助!!=====
- how to display the content of extended storeprocedure (XP)
- 两张表显示问题
- 书上看到一段代码求解
inner join b t1 on a.id1 = t1.id
inner join b t2 on a.id2 = t2.id
inner join b t3 on a.id3 = t3.id
select
(case when a.id1='a1' then '错误1'
when a.id1='a2' then '错误2'
when a.id1='a3' then '错误3'
when a.id1='a4' then '错误4'
when a.id1='a5' then '错误5' ) as id1, (case when a.id2='a1' then '错误1'
when a.id2='a2' then '错误2'
when a.id2='a3' then '错误3'
when a.id2='a4' then '错误4'
when a.id2='a5' then '错误5' ) as id2, (case when a.id3='a1' then '错误1'
when a.id3='a2' then '错误2'
when a.id3='a3' then '错误3'
when a.id3='a4' then '错误4'
when a.id3='a5' then '错误5' ) as id3,
reason
from A
right join 表B B ON A.ID1=B.ID
right join 表B C ON A.ID2=C.ID
right join 表B D ON A.ID3=D.ID
是不是这样就可以了
col2 = (select name from b where b.id = a.id2),
col3 = (select name from b where b.id = a.id3),
reason
from a
inner join b t1 on a.id1 = t1.id
inner join b t2 on a.id2 = t2.id
inner join b t3 on a.id3 = t3.id/*
name name name reason
---------- ---------- ---------- ----------
错误1 错误2 错误3 备注
错误2 错误3 错误4 备注
错误3 错误3 错误5 备注(所影响的行数为 3 行)*/
insert into tb_A select 'a1','a2','a3','备注'
union all select 'a2','a3','a4','备注'
union all select 'a3','a3','a5','备注'
create table tb_B (id varchar(5),name varchar(10))
insert into tb_B select 'a1','错误1'
union all select 'a2','错误2'
union all select 'a3','错误3'
union all select 'a4','错误4'
union all select 'a5','错误5'
---------------------------select
b1.name,
b2.name,
b3.name,
reason
from tb_A a
left join tb_B b1
on a.id1 = b1.id
left join tb_B b2
on a.id2 = b2.id
left join tb_B b3
on a.id3 = b3.id/*
name name name reason
---------- ---------- ---------- ----------
错误1 错误2 错误3 备注
错误2 错误3 错误4 备注
错误3 错误3 错误5 备注(3 row(s) affected)
*/
col2 = (select name from tb_B where tb_B.id = tb_A.id2),
col3 = (select name from tb_B where tb_B.id = tb_A.id3),
reason
from tb_A/*
col1 col2 col3 reason
---------- ---------- ---------- ----------
错误1 错误2 错误3 备注
错误2 错误3 错误4 备注
错误3 错误3 错误5 备注(3 行受影响)
*/