SELECT u.*, d.id AS aid, d.name, d.pro, d.city, d.country, d.detail, d.tel AS atel, r.name AS roleName
FROM tb_user u
INNER JOIN tb_address d ON u.id = d.user_id
LEFT JOIN tb_role_user ur ON ur.user_id = u.id
LEFT JOIN tb_role r ON r.id = ur.role_id;

-- 選擇用戶及其關(guān)聯(lián)的地址、角色信息
SELECT 
    -- 用戶表的所有列
    u.id AS userId, u.username, u.email, u.password, u.created_at, u.updated_at,
    -- 地址表的特定列及別名
    d.id AS addressId, d.name AS addressName, d.province AS pro, d.city, d.country, d.detail AS addressDetail, d.tel AS addressTel,
    -- 角色名的別名
    r.name AS roleName
FROM 
    tb_user u
    -- 內(nèi)連接：只選擇有地址的用戶
    INNER JOIN tb_address d ON u.id = d.user_id
    -- 左外連接：選擇所有用戶及其可能的角色關(guān)聯(lián)
    LEFT JOIN tb_role_user ur ON u.id = ur.user_id
    -- 再左外連接：選擇所有角色關(guān)聯(lián)及其角色名
    LEFT JOIN tb_role r ON ur.role_id = r.id;

create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);

select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';

select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='廣州市';