联接时重复列 [英] Duplicate column on join

问题描述

在将一个表筛选为每个用户的最新条目之后，我尝试连接三个表.但是，突然之间我遇到了错误Duplicate column name 'username'.我需要在此重复"列上加入.我该如何解决?

I'm trying to join three tables, after filtering one down to the most recent entry per user. However, all of a sudden I'm running into the error Duplicate column name 'username'. I need to join on this "duplicate" column. How do I fix this?

SELECT customers.id,customers.name,customers.username,customers.phone,customers.email,radcheck.value as password
    FROM customers
    RIGHT JOIN radcheck ON customers.username = radcheck.username
    LEFT JOIN (
            SELECT * FROM radacct INNER JOIN (
                SELECT username,MAX(acctupdatetime) AS latest FROM radacct GROUP BY username
            ) as radrecent 
            ON radacct.username = radrecent.username 
            AND radacct.acctupdatetime = radrecent.latest
    ) as radlatest 
        ON customers.username = radlatest.username
    WHERE radcheck.attribute = 'Cleartext-Password'

推荐答案

在*中，您有两列为username.您需要限定其中一个或两个.下面的示例:

In the * you have two columns that are username. You need to qualify one or both of them. Example below:

SELECT 
   customers.id,customers.name,customers.username,customers.phone,customers.email,radcheck.value as password
        FROM customers
        RIGHT JOIN radcheck ON customers.username = radcheck.username
        LEFT JOIN (
                SELECT radrecent.username, latest FROM radacct INNER JOIN (
                     --^^^^^^^^^
                    SELECT username,MAX(acctupdatetime) AS latest FROM radacct GROUP BY username
                ) as radrecent 
                ON radacct.username = radrecent.username 
                AND radacct.acctupdatetime = radrecent.latest
        ) as radlatest 
            ON customers.username = radlatest.username
        WHERE radcheck.attribute = 'Cleartext-Password'

这篇关于联接时重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！