在SQL中加入问题... [英] Join Problem in sql ...

问题描述

我有两张桌子，比如表A和表格B，

表A表B 
 
 PiD Val FID VAL2 
 1 A 2 E 
 2 B 3 M 
 3 C 6 L 
 4 D 7 P 
 5 E 

我想要一张桌子，他帮助 SQL 声明赞



< pre lang =text>表C

Pid Val Val2
1 A空
2 BE
3 CM
4 D空
5 E Null
6 Null L
7 Null P

请尽快回复...

解决方案

  SELECT   COALESCE （Pid， FID） as  Pid，Val，Val2 
  FROM  A 
  FULL   JOIN  B  ON  A.Pid = B.FID 

这将为您提供预期的结果，因为你需要两个表中的两行然后你需要使用完全加入而不是左（或右）外连接。



如需进一步参考请通过

http：/ /blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ [ ^ ]

  SELECT  Pid，Val，Val2 
  FROM  A 
  LEFT   OUTER   JOIN  B  ON  A.Pid = B.FID 

是非常简单的东西，所以你可能会觉得这个链接也很有用

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html [ ^ ]

只是要添加，内连接意味着'只返回与两个表匹配的值'。左外连接意味着'返回我左边的所有行，而任何在右边的表中没有值的行返回null'。完整连接返回两个表中的所有值，为缺少的行返回空值。



http://www.codeproject.com/Articles/700317/SQL-Wizardry-Episode-One-Joins [ ^ ]是关于SQL连接的文章。

i have two table Like table A and Table B As

  Table A                   Table B   
                      
PiD     Val               FID    VAL2
 1       A                 2      E
 2       B                 3      M 
 3       C                 6      L
 4       D                 7      P
 5       E

I want a table with he help of SQL statement Like

Table C

Pid   Val        Val2
 1     A          Null
 2     B          E
 3     C          M
 4     D          Null
 5     E          Null
 6     Null       L
 7     Null       P

Please reply soon...

解决方案

SELECT COALESCE(Pid, FID) as Pid, Val, Val2
FROM A
FULL JOIN B ON A.Pid=B.FID

This will give you the expected result, as you need both rows from two tables then you need to use Full Join instead of Left (or Right) Outer Joins.

For further reference please go through
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

SELECT Pid, Val, Val2
FROM A
LEFT OUTER JOIN B ON A.Pid=B.FID

This is very simple stuff so you might find this link useful too
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html[^]

Just to add, an inner join means 'only return values that match both tables'. A left outer join means 'return all the rows to the left of me, and any that don't have values in the table on the right, return null'. A full join returns all values from both tables, returning nulls for missing rows.

http://www.codeproject.com/Articles/700317/SQL-Wizardry-Episode-One-Joins[^] is my article on SQL joins.

这篇关于在SQL中加入问题...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！