mysql - (SQL) 如何将 count() 放入已经存在的列中？

Question

在下一个代码中，我将 count() 的别名显示为“coin”，但我想将其放入结果中名为“matches”的列中，我希望有人可以帮助我。

新列“matches”已存在于表“candidates”中，我想用 count() 值填充它

SQL 代码:

SELECT table1.* , count(*) as coin from (
(SELECT c.* from jobweb.candidates c, jobweb.additional_knowledge ak where
(ak.candidate_id = c.candidate_id) and (ak.knowledge like '%ccna%' or 
ak.knowledge_description like '%ccna%' or 
ak.knowledge like '%java%' or ak.knowledge_description like '%java%'))
union all
(SELECT c.* from jobweb.candidates c , jobweb.work_experience we where 
( we.candidate_id = c.candidate_id ) and
( we.position_name like '%sdh%' or we.functions_desciption like '%sdh%' or 
we.position_name like '%sharepoint%' or we.functions_desciption like '%sharepoint%' or 
we.position_name like '%proyecto%' or we.functions_desciption like '%proyecto%' or 
we.position_name like '%ingeniero%' or we.functions_desciption like '%ingeniero%' ))
union all
(SELECT c.* from jobweb.candidates c, jobweb.formal_education fe where
(fe.candidate_id =  c.candidate_id and fe.education_description like '%ingeniero%'))
)  as table1 group by table1.candidate_id order by coin desc

解决方案:我放弃使用 SQL 来提取“matches”列上的值，因此我使用 hibernate 来执行此操作:

public List<Candidate> getCandidatesMatchesNativeSQL(String customQuery) {
    Query query = sessionFactory.getCurrentSession().createSQLQuery(customQuery)
            .addEntity(Candidate.class)
            .addScalar("matchCounter");
    @SuppressWarnings("unchecked")
    List<Object[]> objects = query.list();
    List<Candidate> candidates = new ArrayList<Candidate>();
    for (Object[] object : objects ) {
        Candidate candidate = (Candidate) object[0];
        BigInteger match = (BigInteger) object[1];
        candidate.setMatches( match.intValue() );
        candidates.add(candidate);
    }
    return candidates;
}

Answer 1

刚刚将硬币更改为选择和排序依据中的匹配项。

       CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS 
   (SELECT table1.candidate_id , count(*) as coin   from (
    (SELECT c.* from jobweb.candidates c, jobweb.additional_knowledge ak where
    (ak.candidate_id = c.candidate_id) and (ak.knowledge like '%ccna%' or 
    ak.knowledge_description like '%ccna%' or 
    ak.knowledge like '%java%' or ak.knowledge_description like '%java%'))
    union all
    (SELECT c.* from jobweb.candidates c , jobweb.work_experience we where 
    ( we.candidate_id = c.candidate_id ) and
    ( we.position_name like '%sdh%' or we.functions_desciption like '%sdh%' or 
    we.position_name like '%sharepoint%' or we.functions_desciption like '%sharepoint%' or 
    we.position_name like '%proyecto%' or we.functions_desciption like '%proyecto%' or 
    we.position_name like '%ingeniero%' or we.functions_desciption like '%ingeniero%' ))
    union all
    (SELECT c.* from jobweb.candidates c, jobweb.formal_education fe where
    (fe.candidate_id =  c.candidate_id and fe.education_description like '%ingeniero%'))
    )  as table1 group by table1.candidate_id order by coin desc)

    Update A set A.matches=B.coin from 
    candidates A inner join table2 B on A.candidate_id=B.candidate_id