使用mysql连接两个表 [英] joining two tables using mysql

问题描述

表格分支





Branch_ID --- Branch_Name - - Branch_City

SB001 ------ Malleshwaram ---班加罗尔

SB002 ------ MG Road ---- ---- Bangalroe

SB003 ------ MG Road --------迈索尔

SB004 ------耆那教--- ----迈索尔





桌上贷款

Account_no --- - Branch_Id ---- 余额

AE1185698 ---- SB001 --------- 102000

AE8552266 ---- SB003 --------- 40000

AE1003996 ---- SB004 --------- 15000

AE1100996 ---- SB002 --------- 100000





i想要以下形式输出



min ------- 城市 ------ max ------ 城市

15000 ----迈索尔---- 10200 ---- Blore





i试过这个



选择min（余额），branch.city，max（余额），branch（city）来自贷款。







提前致谢

Table branch


Branch_ID---Branch_Name---Branch_City
SB001------Malleshwaram---Bangalore
SB002------MG Road--------Bangalroe
SB003------MG Road--------Mysore
SB004------Jainagar-------Mysore


table loan
Account_no----Branch_Id----Balance
AE1185698----SB001---------102000
AE8552266----SB003---------40000
AE1003996----SB004---------15000
AE1100996----SB002---------100000


i want the output in the below form

min-------city------max------city
15000----mysore----10200----Blore


i tried this

select min(balance), branch.city, max(balance), branch(city) from loan.



Thanks in advance

推荐答案

这样可行......

This would work ...

SELECT MINCTE.Balance, MINCTE.Branch_City, MAXCTE.Balance, MAXCTE.Branch_City
FROM (select L.*, B.Branch_City from loan L
    INNER JOIN branch B on L.Branch_Id=B.Branch_ID
    order by Balance LIMIT 1) MINCTE
CROSS JOIN
    (select L.*, B.Branch_City from loan L
    INNER JOIN branch B on L.Branch_Id=B.Branch_ID
    order by Balance desc LIMIT 1) MAXCTE

MINCTE是获取最小值和相关城市名称的派生表

MAXCTE对最大值做同样的事情

这两个派生表然后加入CROSS

MINCTE is the derived table to get the minimum value and the associated city name
MAXCTE does the same for the maximum value
Those two derived tables are then CROSS joined

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