任何人都可以帮助我获得此输出。 [英] Can anyone help me to get this output.?

问题描述

大家好，



我有两张桌子如下图所示，



表1：

   -    ---------- ---------------------------  
 SNO |名称| ID | 
   -    -------------------- ------------  
  1  | Aj Ali | A001 | 
  2  | DB Kola | A002 | 
  3  | Raj Sir | A003 | 
   -    -------------------- ------------  

表2：

< pre lang =sql> - ----------------- ---------------
SNO |名称|
- -------------------- ------------
10 | Ali Aj |
20 | DB Kola Mj |
30 |拉吉爵士
- -------------------- ------------

现在我需要这样的输出，

   -    ---- ----------------------------  
 SNO |名称| ID | 
   -    -------------------- ------------  
  10  | Ali Aj | A001 | 
  20  | DB Kola | A002 | 
  30  |拉吉爵士A003 | 
   -    -------------------- ------------  

这里的问题是名称列值是不一样的顺序（名字和SurName）。



请帮我这个。



我尝试过：

 选择 Table2.SNO，Table2.Name，Table1.ID  from  Table1  inner  加入 Tabl2   Table1.Name = Table2.Name 

--------------------

我的输出只有这个，

   -    ------------------- -------------  
 SNO |名称| ID | 
   -    -------------------- ------------  
  20  | DB Kola | A002 | 
   -    -------------------- ------------  

解决方案

您需要重新设计表格以进行拆分将名称放入相关部分，因此有一个名字，姓氏，标题等字段。其次加入像name这样的文本字段很容易出错，所以不应该在Table2中存储名称，而应该存储表1的ID < br $>

   -     - -------------------------------  
 SNO | SNO_ID | 
   -    -------------------- ------------  
  10  |  1  | 
  20  |  2  | 
  30  |  3  | 
   -    -------------------- ------------  

这会让你进行查询以获得你需要的数据

  SELECT  t2.SNO，t1.Name， T1.ID 来自表1 t1  join 表1 t2   t2.SNO_ID = t1.SNO 

实际上，你不能确定这两个表之间的正确联系，尽管它似乎暗示了这个名字各表中的字段。但名称值并不相同。这是数据库设计不佳的结果。名称不能是唯一的，想象一下有多少人可以拥有相同的名字和不同的名字写法。理所当然，它们应该通过ID字段链接如下：

表1表2 
 SNO SNO 
 ID -------- -  ID 
名称

这只是一个例子，实际的表格设计将取决于要求。开始学习数据库设计简介 [ ^ ]和数据规范化中的1NF，2NF，3NF和BCNF DBMS教程| Studytonight [ ^ ]并重新设计你的数据库。

没有自动的方法来确保 DB Kola 是否 DB Kola Mj ，就像2个人一样可以有相同的名字。

这就是为什么没有人使用名字作为表之间的关键，永远不会。

作为一名数据库设计师，你的工作是确保这种情况永远不会发生。

Hi All,

I have two tables as shown below,

Table1:

---------------------------------------
SNO| Name  | ID| 
----------------------------------
1  | Aj Ali  | A001 |
2  | DB Kola | A002 |  
3  | Raj Sir | A003 |
----------------------------------

Table2 :

----------------------------------
SNO| Name  | 
----------------------------------
10  | Ali Aj  | 
20  | DB Kola Mj|   
30  | Sir Raj | 
----------------------------------

Now i need output like this,

----------------------------------
SNO| Name  | ID |
----------------------------------
10  | Ali Aj  |  A001 |
20  | DB Kola |  A002 |     
30  | Sir Raj |  A003 |  
----------------------------------

Here the problem is the "name" column values are not in the same order (First Name and SurName).

Please help me on this.

What I have tried:

select Table2.SNO,Table2.Name,Table1.ID from Table1 inner join Tabl2 on Table1.Name=Table2.Name

--------------------
And my output is only this,

----------------------------------
SNO| Name  | ID |
----------------------------------
20  | DB Kola |  A002 |     
----------------------------------

解决方案

You need to re-design your tables to split the name into the relevant parts, so have a field for first name, surname, title etc. Secondly joining on text fields like name is very prone to error, so instead of storing the name in Table2 you should store the ID of table 1

----------------------------------
SNO| SNO_ID  | 
----------------------------------
10  | 1  | 
20  | 2 |   
30  | 3 | 
----------------------------------

That will let you do a query to get you the data you need

SELECT t2.SNO, t1.Name, T1.ID from Table1 t1 join Table1 t2 on t2.SNO_ID = t1.SNO

As it is, you cannot be certain of the correct linkage between these two table though it seems to suggest the name fields in the respective tables. But the name values are not identical. That is the result of poor database design. Name cannot be unique, just imagine how many people can have the same names and the different ways of writing a name. Rightfully, they should be linked by the ID fields like this:

table 1        table 2
SNO            SNO
ID  ---------- ID
Name

This is just one example, the actual table design will depend on the requirements. Start learning Introduction to database design[^] and 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^] and re-design your database.

There is no automatic way to ensure that DB Kola is DB Kola Mj or not, just like 2 people can have the same name.
That is why no one use a name as a key between tables, never.
As a database designer, it is your job to ensure that such situation never happen.

这篇关于任何人都可以帮助我获得此输出。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！