如何在两个表之间编写连接查询 [英] how to write a join query between two tables

问题描述

您好我有这样的数据



表1包含以下数据

 sno name 
  1  a 
  2  b 
  3  c 
  4  d 
  5  e 

表2有以下数据

 sno name 
  1  b 
  2  a 
  3  c 
  4  d 
  6  f 

现在如何编写一个查询，如果两个表中的名称相等，则必须得到table2的sno，否则得到table1的sno，并且连接查询应该返回所有行在表1和表2中

解决方案

检查此

SQL Joins - W3Schools [

 选择 isnull（b.sno，a ，sno），a。*，b。* 
 来自 table1 a 完整 外 加入 table2   a.name = b.name 

Ť他的查询应返回所有具有相同名称的记录：

  SELECT   COALESCE （t2.sno，t1.sno） AS  NewSno，t2.name 
  FROM  table1  AS  t1  INNER   JOIN  table2  as  t2  ON  t1.name = t2.name 

有关不同类型连接的更多信息，请阅读： SQL连接的可视化表示 [ ^ ]

COALESCE [ ^ ]

看到这个..

 选择 
（ case  当 a.name = b.name 然后 isnull（b.sno，a.sno） else  isnull（a.sno，b.sno） end ） as  sno，
 isnull （a.name，b.name）来自 [dbo]。[Table_1] a 
 
 完整 加入 
 
 [dbo]。[表_2] b  on  a.sno = b.sno 

这里 http://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull [<一个HREF =http://stackoverflow.com/questions/18828 641 / sql-difference-between-coalesce-and-isnulltarget =_ blanktitle =New Window> ^ ]

Hi I have data like this

Table 1 has following data

sno name 
1    a
2    b 
3    c
4    d
5    e

Table 2 has following data

sno name
1   b
2   a
3   c
4   d
6   f

now how can i write a query that if name in both the tables are equal the it has to get sno of table2 else get sno of table1 and along with this the join query should return all the rows in both Table 1 and Table2

解决方案

Check this
SQL Joins - W3Schools[^]

Also this may help you

select isnull(b.sno,a,sno),a.*,b.* 
from table1 a Full Outer Join table2 on a.name=b.name

This query should return all records with equal names:

SELECT COALESCE(t2.sno, t1.sno) AS NewSno, t2.name
FROM table1 AS t1 INNER JOIN table2 as t2 ON t1.name = t2.name

For further information about different types of joins, please read this: Visual Representation of SQL Joins[^]
COALESCE[^]

see this..

select
(case when a.name=b.name then isnull(b.sno,a.sno) else isnull(a.sno,b.sno) end) as sno ,
isnull(a.name,b.name) from [dbo].[Table_1] a

full join

[dbo].[Table_2] b on a.sno=b.sno

Here http://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull[^]

这篇关于如何在两个表之间编写连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！