Question

It s difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.

Closed 11 years ago.

我有两张没有压抑的桌子:

人口表:

   ID | NAME | AGE
    ------------
   1  | DAVE | 43
   1  | ANNE | 25
   1  | MIKE | 58

PLACES 表格 :

ID | COUNTRY | TOWN
------------------
1  |   USA    | WILMINGTON
1  |   GER    | BERLIN
1  |   POR    | LISBON
1  |   JPN    | KYOTO

我想要这样的结果:

NAME | AGE | COUNTRY | TOWN
-----------------------------
ANNE |  25 | GER     | BERLIN
DAVE |  43 | JPN     | KYOTO
MIKE |  58 | POR     | LISBON
           | USA     | WILMINGTON

Answer 1

让我试试看

SELECT COALESCE(a.Name,   ) Name,
       COALESCE(a.Age,   ) Age,
       b.Country,
       b.Town
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS RowNum, Name, Age
     FROM   People) a Right Join 
    (SELECT ROW_NUMBER() OVER (ORDER BY [Country]) AS RowNum, Country, Town
     FROM   Places) b ON a.RowNum = b.RowNum

Answer 2

您需要在每个表格中至少一张数据才能加入对应。通常, 此选项会处理初级密钥/ 外国密钥关系。您可以在您的 PLACES 表格中创建一个主密钥( 如 1=USA, 2=GER, 3=POR, 4=JPN), 然后将外国密钥放在您人口表格中适合每个人的位置的一列( 如 2=ANNE, 4=DAVE, 3=MIKE) 。

Answer 3

我猜你希望按字母顺序把这些组合起来但是这不怎么合理...

WITH x AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY NAME) FROM People),
     y AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY COUNTRY) FROM Places)
SELECT * FROM x FULL OUTER JOIN y ON x.rn = y.rn;

Answer 4

我完全不建议这样做! 但我觉得这是一个很好的挑战。我强烈建议采用Sean的建议,用钥匙来这样做。

无论如何,这个查询会准确提供你所要求的内容,而不改变你的计划(它也假设你的数据是准确的表示)。

示例:http://www.sqlfiddle.com/#!3/a0486/8

Select Distinct
  case when town =  Wilmington  Then NULL else Name end as Name, 
  case when town =  Wilmington  Then NULL else Age end as Age,
  Country, 
  Town
FROM People, Places
Where 
     (Name =  Anne  AND Town =  Berlin )
  OR (Name =  Dave  AND Town =  Kyoto )
  OR (Name =  Mike  AND Town =  Lisbon )
  OR (Town =  Wilmington )
Order By Country Asc

<强 > EDIT

为了完成我的回答并提供一个更合适的解决办法,我建议你在两个表格中加上一个PlaceID,然后左翼加入其中。

示例:http://www.sqlfiddle.com/#!3/d5ee8/3

Create Table People(
  Name varchar(255),
  Age int,
  PlaceID int
)

Create Table Places(
  PlaceID int,
  Country varchar(255),
  Town varchar(255)
)

Insert Into People Values ( Dave , 43, 2)
Insert Into People Values ( Anne , 25, 1)
Insert Into People Values ( Mike , 58, 3)

Insert Into Places Values (1,  Ger ,  Berlin )
Insert Into Places Values (2,  Jpn ,  Kyoto )
Insert Into Places Values (3,  Por ,  Lisbon )
Insert Into Places Values (4,  USA ,  Wilmington )

Select 
  Name, Age, Country, Town 
FROM 
  Places
  Left Join People On Places.PlaceID = People.PlaceID

友情链接