17,382
社区成员




--他的tb1数据:
aaa 001
bbb 003
ccc 001 002
ddd 002
eee 001
fff 003
--他的tb2数据:
001 A
002 B
003 C
--其实他想要的数据结果应该是这样的
001 A aaa
001 A ccc
001 A eee
002 B ccc
002 B ddd
003 C fff
--那么要显示上面的结果SQL 如下:
(select b.*,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo1
union
select b.costNo,b.costName,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo2 and b.costName in('A','B')
)
intersect
(
select b.costNo,b.costName,a.userID from tb11 a ,tb22 b where a.costNo1=b.costNo
union
select b.costNo,b.costName,a.userID from tb11 a ,tb22 b where a.costNo2=b.costNo
)
--或者
(select b.*,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo1
union
select b.costNo,b.costName,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo2 and b.costName in('A','B')
)
intersect
(select b.*,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo1
union
select b.costNo,b.costName,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo2
)
001 A aaa
001 A ccc
001 A eee
002 B ccc
002 B ddd
003 C bbb
003 C fff
--那么
(select b.*,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo1
union
select b.costNo,b.costName,a.userID from tb22 as b left join tb11 as a on b.costNo=a.costNo2
);
--All Above都对的~~~
select b.costNo,b.costName,a.userID from tb1 a ,tb2 b where a.costNo1=b.costNo or a.costNo2=b.costNo
select b.costNo,b.costName,a.userID from tb1 a ,tb2 b where a.costNo1=b.costNo
union
select b.costNo,b.costName,a.userID from tb1 a ,tb2 b where a.costNo2=b.costNo
select t2.costno,t2.costname,t1.userid
from tb1 t1
left join tb2 t2 on (t2.costno=t1.costno);
有点乱,没大看明白,试试,这是不是你想要的