时间戳

首页 > 解决方案 > SQL 查询 SQLSTATE[42S21]: 列已存在: 1060 列名重复

php - SQL 查询 SQLSTATE[42S21]: 列已存在: 1060 列名重复

问题描述

我正在尝试加入 3 个表，其中 1 个表有另外两个表的主键。代码如下：

起初，我尝试在不使用任何别名的情况下运行查询。然后改了。


     public function member_show()
     {
        $number_of_member =
        DB:: table('members')
        ->distinct('email')
        -> count('email');

        $data = DB::select("
          SELECT m.member_id, m.member_name, m.email, m.password,              
          cl.club_name, ct.city_name, jobtype.type_name 
          FROM members as m
          LEFT JOIN clubs as cl ON cl.club_id = m.club_id 
          LEFT JOIN cities as ct ON ct.city_id = m.city_id 
          LEFT JOIN (SELECT * 
          FROM jobs as jb
          LEFT JOIN j_types as jt
          ON jt.type_id = jb.type_id) as jobtype
          ON jobtype.member_id = m.member_id
         ");

        return view('member.show',['num_member' => $number_of_member, 'data'     
        => $data]);
    }

错误是：

   SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name   
   'type_id' (SQL: SELECT m.member_id, m.member_name, m.email, m.password,
   cl.club_name, ct.city_name, jobtype.type_name FROM members as m LEFT JOIN  
   clubs as cl ON cl.club_id = m.club_id LEFT JOIN cities as ct ON
   ct.city_id = m.city_id LEFT JOIN (SELECT * FROM jobs as jb LEFT JOIN j_types
   as jt ON jt.type_id = jb.type_id) as jobtype ON jobtype.member_id = 
   m.member_id    )

标签： phpsqllaravel

解决方案

在内部选择type_id同时存在于j_types和jobs表中，因此您必须选择其中之一。

(SELECT * 
          FROM jobs as jb
          LEFT JOIN j_types as jt
          ON jt.type_id = jb.type_id) as jobtype

更改为这样的内容：

(SELECT jb.*, jt.x, jt.y, jt.z
          FROM jobs as jb
          LEFT JOIN j_types as jt
          USING (type_id)) as jobtype

推荐阅读