full join 怎么不行啊???

[江南野鹤]

select * from UT_PROGRAM where userid=4 有142条数据
select * from UT_PROGRAM where userid=50437有165条数据

以上两个结果中 ProgramID 相同的只有1条

其中 UT_PROGRAM 表有4个字段 [UserID],[ProgramID],[Weight] ,[Type]

select * from  UT_PROGRAM u1
full  join (select * from  UT_PROGRAM where userid=4 ) u2 on u2.ProgramID=u1.ProgramID

 where u1.userid =50437

这样操作却只出165条记录 .

为什么呢 ???

[江南野鹤]

用full的话应该是142+165-1条记录啊,求大神指教

[dd_2012]

where u1.userid =50437 不等于 on u1.userid =50437

[开启时代]

试试，楼主写的有问题

select *
from (select * from  UT_PROGRAM u1 where userid =50437) as u1
full  join (select * from  UT_PROGRAM where userid=4 ) u2 on u2.ProgramID=u1.ProgramID  

[qmz2007]

full是全连 应该用内连把full去掉

[發糞塗牆]

用inner join

SELECT  *
FROM    ( SELECT    *
          FROM      UT_PROGRAM
          WHERE     userid = 50437
        ) u1
        INNER   JOIN ( SELECT   *
                       FROM     UT_PROGRAM
                       WHERE    userid = 4
                     ) u2 ON u2.ProgramID = u1.ProgramID

[qmz2007]

说错了 理解错你的意思了 select * from UT_PROGRAM u1 full join (select * from UT_PROGRAM where userid=4 ) u2 on u2.ProgramID=u1.ProgramID 这句话结果是 UT_PROGRAM 所有数据 where u1.userid =50437 筛选出来是select * from UT_PROGRAM where userid=50437的数据

[快溜]

因为你加了u1.userid =50437

[qmz2007]

你想要的写法应该是 SELECT * FROM ( SELECT * FROM UT_PROGRAM WHERE userid = 50437 ) u1 full JOIN ( SELECT * FROM UT_PROGRAM WHERE userid = 4 ) u2 ON u2.ProgramID = u1.ProgramID 结果应该是142+165-1条