SQL连接2个表的QUERY [英] SQL join QUERY of 2 tables

问题描述

我有桌子1。



table1有一列：ID

数据是：

ID

1

2

3

4

5

6

7
8

9

10



另一张表如表2所示



有2列：ID，Amt



数据是：



ID Amt

1 10

5 20

7 100



现在我需要一个结果



ID Amt

1 10

2 10

3 10

4 10

5 20

6 20

$ 100





请告诉我查询以获取此

Hi,


I have a table table1.

table1 has one column:ID
data are:
ID
1
2
3
4
5
6
7
8
9
10

Another table is present as Table2

has 2 column: ID,Amt

Data are:

ID Amt
1 10
5 20
7 100

Now i need a result as

ID Amt
1 10
2 10
3 10
4 10
5 20
6 20
7 100


PLease tell me query to achive this

推荐答案

非常奇怪的数据和要求...

使用上面显示的数据，以下查询有效：

Very strange data and requirement...
With the data shown above, following query works:

SELECT Table1.ID,  Max(Table2.Amt)
FROM Table1, Table2
where table1.id>=table2.id and table1.id<=7
group by table1.id

table2的最大值不一定是硬的编码，但可以通过子查询检索：

The maximum value of table2 need not be hard coded but can be retrieved via a subquery:

SELECT Table1.ID,  Max(Table2.Amt)
FROM Table1, Table2
where table1.id>=table2.id and table1.id<= (select max(table2.id) from table2)
group by table1.id

[/ Edit]

[/Edit]

select a.id,case when a.id between 1 and 4 then 10 when a.id between  5 and 6 then 20 when a.id between 7 and 10 then 100 end  From table1 as a left join table2 as b on a.id=b.id

加入查询：

SELECT t2.ID, t2.Amt
FROM Table1 t1, Tabele2 t2
WHERE t1.ID = t2.ID

-KR

-KR

这篇关于SQL连接2个表的QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！