mysql - (SQL) How can I put the count() into a column that already exists? -

-

in next code show alias count() "coin" want put column named "matches" in results, hope can me please.

the new column "matches' exists in table "candidates" , want fill count() values

sql code:

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

solution: discard use sql extract values on column "matches" used hibernate that:

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>();     (object[] object : objects ) {         candidate candidate = (candidate) object[0];         biginteger match = (biginteger) object[1];         candidate.setmatches( match.intvalue() );         candidates.add(candidate);     }     return candidates; }

just changed coin matches in select , order by.

       create temporary table if not exists table2     (select table1.candidate_id , count(*) coin   (     (select c.* jobweb.candidates c, jobweb.additional_knowledge ak     (ak.candidate_id = c.candidate_id) , (ak.knowledge '%ccna%' or      ak.knowledge_description '%ccna%' or      ak.knowledge '%java%' or ak.knowledge_description '%java%'))     union     (select c.* jobweb.candidates c , jobweb.work_experience      ( we.candidate_id = c.candidate_id ) ,     ( we.position_name '%sdh%' or we.functions_desciption '%sdh%' or      we.position_name '%sharepoint%' or we.functions_desciption '%sharepoint%' or      we.position_name '%proyecto%' or we.functions_desciption '%proyecto%' or      we.position_name '%ingeniero%' or we.functions_desciption '%ingeniero%' ))     union     (select c.* jobweb.candidates c, jobweb.formal_education fe     (fe.candidate_id =  c.candidate_id , fe.education_description '%ingeniero%'))     )  table1 group table1.candidate_id order coin desc)      update set a.matches=b.coin      candidates inner join table2 b on a.candidate_id=b.candidate_id

Comments

Post a Comment

Popular posts from this blog

c++ - How to add Crypto++ library to Qt project -

-
Image
i downloaded crypto++ source , compiled cryptlib project in visual studio 2013, , added generated .lib file qt project, made .pro file this: qt += core gui qt += sql greaterthan(qt_major_version, 4):qt += widgets target = untitled template = app sources += main.cpp\ mainwindow.cpp headers += mainwindow.h \ databasecontrol.h \ test.h forms += mainwindow.ui win32:config(release, debug|release): libs += -l$$pwd/ -lcryptlib else:win32:config(debug, debug|release): libs += -l$$pwd/ -lcryptlibd else:unix: libs += -l$$pwd/ -lcryptlib includepath += $$pwd/ dependpath += $$pwd/ win32-g++:config(release, debug|release): pre_targetdeps += $$pwd/libcryptlib.a else:win32-g++:config(debug, debug|release): pre_targetdeps += $$pwd/libcryptlibd.a else:win32:!win32-g++:config(release, debug|release): pre_targetdeps += $$pwd/cryptlib.lib else:win32:!win32-g++:config(debug, debug|release): pre_targetdeps += $$pwd/cryptlibd.lib else:unix: pre_targetdeps += $$pw
Read more

jQuery Mobile app not scrolling in Firefox -

-
i have mobile app built in jquery mobile. scrolling fine in chrome & safari, fails scroll in firefox on desktop. have other instances of same app, different features, , works across browsers. life of me, cannot figure this, please help! located here: http://re-brand.us/dsus/index.html seems bug in firefox: bug log . able scroll using keyboard not trackpad on mac. are using mac? i'll keep looking see if can find way around it.
Read more

How to use vim as editor in Matlab GUI -

-
i using matlab r2013b. using gui, command windows , editor in split screen. in matlab preferences > editor/debugger > editor. there option set custom editor instead of matlab editor. set local editor /usr/bin/vim , not able open files @ anymore. there possibility use vim editor in split screen mode? i aware of !vim file.m , not solution looking for, works satisfactory in -nodisplay mode. rather want keep split screen mode. i did not find solution 100% satisfying, using tmux intermediate solution. allows 2 use split screen 2 terminals. in 1 of can open matlab -nodesktop in terminal mode, , in other 1 vim . possible switch between both split screens. you can use functionality of gui matlab typing things commandhistory , who et cetera.
Read more