sql问题：A表的两个字段分别与另B表的主键进行关联，然后在分别显示出来A表的两个字段分别对应B表的值。
发布网友
发布时间：2022-04-10 11:45
我来回答
共3个回答
懂视网
时间：2022-04-10 16:06
create table test(
2 rsrp string,
3 rsrq string,
4 tkey string,
5 distan string
6 );
7
8 insert into test values(‘-90.28‘,‘-37‘,‘tkey1‘,‘10‘);
9 insert into test values(‘-92.35‘,‘-40‘,‘tkey1‘,‘30‘);
10 insert into test values(‘-94.36‘,‘-34‘,‘tkey2‘,‘5‘);
11 insert into test values(‘-93.88‘,‘-38‘,‘tkey2‘,‘19‘);
12
13 select * from test;
14 +------------+------------+------------+--------------+--+
15 | test.rsrp | test.rsrq | test.tkey | test.distan |
16 +------------+------------+------------+--------------+--+
17 | -90.28 | -37 | tkey1 | 10 |
18 | -92.35 | -40 | tkey1 | 30 |
19 | -94.36 | -34 | tkey2 | 5 |
20 | -93.88 | -38 | tkey2 | 19 |
21 +------------+------------+------------+--------------+--+
22
23 create table test_latlng
24 (
25 tkey string,
26 lat string,
27 lng string
28 );
29 insert into test_latlng values(‘tkey1‘,‘lat1‘,‘lng1‘);
30 insert into test_latlng values(‘tkey1‘,‘lat2‘,‘lng2‘);
31 insert into test_latlng values(‘tkey1‘,‘lat3‘,‘lng3‘);
32 insert into test_latlng values(‘tkey1‘,‘lat4‘,‘lng4‘);
33 insert into test_latlng values(‘tkey2‘,‘lat1‘,‘lng1‘);
34 insert into test_latlng values(‘tkey2‘,‘lat2‘,‘lng2‘);
35 insert into test_latlng values(‘tkey2‘,‘lat3‘,‘lng3‘);
36 insert into test_latlng values(‘tkey2‘,‘lat4‘,‘lng4‘);
37
38 0: jdbc:hive2://10.78.152.62:21066/> select * from test_latlng;
39 +-------------------+------------------+------------------+--+
40 | test_latlng.tkey | test_latlng.lat | test_latlng.lng |
41 +-------------------+------------------+------------------+--+
42 | tkey1 | lat1 | lng1 |
43 | tkey1 | lat2 | lng2 |
44 | tkey1 | lat3 | lng3 |
45 | tkey1 | lat4 | lng4 |
46 | tkey2 | lat1 | lng1 |
47 | tkey2 | lat2 | lng2 |
48 | tkey2 | lat3 | lng3 |
49 | tkey2 | lat4 | lng4 |
50 +-------------------+------------------+------------------+--+
51
52 select rsrp,rsrq,t10.tkey,lat,lng,t10.rn
53 from
54 (
55 select rsrp,rsrq,tkey,row_number()over(partition by tkey order by cast(rand() * 100 as int) asc) as rn
56 from test
57 group by rsrp,rsrq,tkey
58 ) t10
59 inner join
60 (
61 select lat,lng,tkey,row_number()over(partition by tkey order by cast(rand() * 10000 as int) asc) as rn
62 from test_latlng
63 group by lat,lng,tkey
64 ) t11
65 on t10.tkey=t11.tkey and t10.rn=t11.rn;
66 +---------+-------+-----------+-------+-------+---------+--+
67 | rsrp | rsrq | t10.tkey | lat | lng | t10.rn |
68 +---------+-------+-----------+-------+-------+---------+--+
69 | -90.28 | -37 | tkey1 | lat2 | lng2 | 1 |
70 | -92.35 | -40 | tkey1 | lat3 | lng3 | 2 |
71 | -93.88 | -38 | tkey2 | lat3 | lng3 | 1 |
72 | -94.36 | -34 | tkey2 | lat2 | lng2 | 2 |
73 +---------+-------+-----------+-------+-------+---------+--+
TSQL：A表字段与B表中的关联，关联条件中一列是随机关联的实现方式
标签：rom jdbc sel srp 方式 log div color rand
热心网友
时间：2022-04-10 13:14
select t2.name as Name1,t3.name as Name2
from
A t1,B t2,B t3
where t1.收件人甲ID=t2.ID
and t1.代收人乙ID=t3.ID
看看这个是你说的那个意思吗？
热心网友
时间：2022-04-10 14:32
SELECT b1.name as Name1, b2.name as Name2
FROM A as a
INNER JOIN B as b1 ON a.甲ID＝b1.ID
INNER JOIN B as b2 ON a.乙ID=b2.ID
