如何从一个表中获取数据。 [英] How to get data from only one table.

问题描述

大家好，



我有两张名为'X'和'Y'的桌子。 'Y'表包含两列'代码'（非唯一）和'名称'。 'X'表包含'ID'（唯一），'Code'（非唯一）等列。我想从表'Y'中取'Name'列，并根据'Code'列将其推入表'X' 。我的'X'表包含行100，'Y'表包含行'18'。我只想要100行作为我的最终结果。我做了离开加入，我使用了子查询，但我没有得到所需的o / p。列''代码'在两个表中都不是唯一的。



表'X'------------------- ----------------------------------表'Y'

ID ... 。代码----------------------------------------------代码。 ...姓名

1 ...... abc ------------------------------ ----------------- abc .....布朗

2 ...... abc ---------- -------------------------------------- abc .....布朗

3 ...... cdv --------------------------------------- --------- cdc ..... ZXCVB

4 ...... cbc ------------------ ------------------------------ onm ..... lkmjnk

5 .... .. onm



我写下来查询以获取数据：



选择Y .Name，X.Code

来自X

左加入Y

on X.Code = E.Code

WHERE X.Code in（SELECT distinct Code FROM Y）



我认为通过使用ROW_NUMBER（）函数我们可以得到它。请帮帮我。

谢谢。

解决方案

如果表'Y'中有不同的名称针对单个代码，那么这将不会工作

   -     -  Query1  
 选择  distinct  x.id，x.code，y.name 来自 X  left  外部 加入 Y   x.code = y.code 

你必须改变一些东西像这样每次获得100行：

   -     -  Query2（已更新） 
 选择 x.code，MAX（y.name） as 名称
 来自 X 
  left   outer   join  Y  on  x.code = y.code 
  Group   by  x.code 

如果您确认表格'Y'中的名称不会被更改，我怀疑你那么Query1否则使用Query2。



希望它有所帮助

你好，

我认为你需要一个简单的左外连接来获得期望的。根据你的要求，你需要X的所有行。



试试这个

选择不同的x.id，x .code，y.name来自X左外连接Y on x.code = y.code 

谢谢

animesh

大家好，



我已经解决了这个问题。谢谢你的帮助。查询如下：

  SELECT  E.CODE，DD .Name 
  From  X 
  Left   join  
（ SELECT 代码，名称 FROM （ SELECT  ROW_NUMBER（） OVER （ partition   by  CODE 订单 按 CODE） as  rownum，CODE，Name  FROM  Y） as  Y 
 < span class =code-keyword>其中 Y.rownum =  1 ） as  DD 
   E.CODE = DD.CODE 

谢谢，

Ni锡

Hi all,

I have two tables named 'X' and 'Y'. 'Y' table contains two columns 'code'(non unique) and 'Name'. 'X' table contains column like 'ID'(unique),'Code'(non Unique) etc. I want to take 'Name' column from table 'Y'and push it into table 'X' based on 'Code' column. My 'X' table contains rows 100 and 'Y' table contains rows '18'. I want only 100 rows as my final result. I did left join, i used sub-queries but i didn't get the required o/p. Column 'Code' is not unique in both tables.

Table 'X'----------------------------------------------------- Table 'Y'
ID.... Code ----------------------------------------------Code....Name
1...... abc ----------------------------------------------- abc..... Brown
2...... abc ------------------------------------------------abc..... Brown
3...... cdv ------------------------------------------------cdc..... ZXCVB
4...... cbc ------------------------------------------------onm..... lkmjnk
5...... onm

I wrote down below query to get data:

" select Y.Name,X.Code
FROM X
left join Y
on X.Code = E.Code
WHERE X.Code in (SELECT distinct Code FROM Y) "

I think by using ROW_NUMBER() function we can get this. Please help me.
Thanks.

解决方案

If there are different names in Table 'Y' against a single code then this will would not work

--- Query1
select distinct x.id,x.code,y.name from X left outer join Y on  x.code=y.code

you will have to change it something like this to get 100 rows each time:

--- Query2 (updated)
select x.code,MAX(y.name) as name
from X 
left outer join Y on  x.code=y.code
Group by x.code

If you are certern that name would not be changed in Table 'Y' which i doubt then you the Query1 else use the Query2.

Hope it helps

Hello ,
I think you need a simple left outer join to get the desired out put . According to your requirement you need all rows from X.

Try this

select distinct x.id,x.code,y.name from X left outer join Y on  x.code=y.code

thanks
animesh

Hi all,

I have solved the problem. Thanks for your help. The query is like below:

SELECT E.CODE,DD.Name
From X
Left join
(SELECT CODE, Name FROM (SELECT ROW_NUMBER() OVER(partition by CODE Order by CODE) as rownum, CODE,Name FROM Y) as Y
where Y.rownum = 1) as DD
on E.CODE= DD.CODE

Thank you,
Nitin

这篇关于如何从一个表中获取数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！