例如查询1返回的结果是
ID NAME
1 a
2 b
查询2的结果是
ID NO
1 1
3 2
我想得到的结果是
ID NAME NO
1 a 1
2 b
3 2
怎样才能实现这种效果??
------解决方案--------------------
select table1.ID,table1.Name,table2.NO from table1 inner join table2 on table1.ID=table2.ID
------解决方案--------------------
declare @table1 table(ID CHAR(2),NAME CHAR(3))
declare @table2 table(ID CHAR(2),NO CHAR(2))
INSERT INTO @table1 VALUES('1','AA')
INSERT INTO @table1 VALUES('2','BB')
INSERT INTO @table1 VALUES('3','CC')
INSERT INTO @table2 VALUES(1,1)
INSERT INTO @table2 VALUES(4,4)
INSERT INTO @table2 VALUES(5,5)
SELECT * FROM @table2
SELECT * FROM @table1
select ISNULL(T1.ID,T2.ID)AS ID ,ISNULL(T1.NAME,'') NAME ,ISNULL(T2.NO,'') NO from @table1 T1
full join @table2 T2 ON
(T1.ID=T2.ID
)
------解决方案--------------------
declare @table1 table(ID CHAR(2),NAME CHAR(3))
declare @table2 table(ID CHAR(2),NO CHAR(2))
INSERT INTO @table1 VALUES('1','AA')
INSERT INTO @table1 VALUES('2','BB')
INSERT INTO @table1 VALUES('3','CC')
INSERT INTO @table2 VALUES(1,1)
INSERT INTO @table2 VALUES(4,4)
INSERT INTO @table2 VALUES(5,5)
SELECT * FROM @table2
SELECT * FROM @table1
select ISNULL(T1.ID,T2.ID)AS ID ,ISNULL(T1.NAME,'') NAME ,ISNULL(T2.NO,'') NO from @table1 T1
full join @table2 T2 ON
(T1.ID=T2.ID
)
------解决方案--------------------
select distinct * from
(
select table1.ID,table1.Name,table2.NO from table1 left join table2 on table1.ID=table2.ID
union all
select table1.ID,table1.Name,table2.NO from table1 right join table2 on table1.ID=table2.ID
)
------解决方案--------------------
按照楼主的Case
select table1.ID,table1.Name,table2.NO
from table1
right outer join table2 on
table1.ID=table2.ID
------解决方案--------------------
先用all join 查得兩個表的所有數據,
再外查詢用case 去掉null
select id,(case where name=null then ''else name end) as name,
(case where no=null then ''else no end) as no
from (select id,name,on from table1 all join table2) as aa
(select id,name,on from table1 all join table2) 此語句還有錯語,請樓主參考其它人的回答修正此語句.
------解决方案--------------------
如果是一个表里的
ID NAME VALUE
1 MA a
2 MA b
1 SU 1
3 SU 2
如果查询得到
ID MA SU
1 a 1
2 b
3 2
------解决方案--------------------
实现多表联合查询使用union
在两条查询语句之间使用union
例如：
select name from Suser where ID=‘’
union
select sex from Suser where ID=‘’