开发者

complex combine sql

开发者 https://www.devze.com 2022-12-19 00:36 出处:网络
query 1 : this will count all the record with email \'emali@test.com\' select count(*) as total from userbase

query 1 : this will count all the record with email 'emali@test.com'

select count(*) as total from userbase
join extrauserinfofromhrms on userbase.username = extrauserinfofromhrms.useremail
right join logevent on userbase.username = logevent.useremail
join eachworkflow on logevent.workflowid= eachworkflow.workflowid
where logevent.actionname ='complete'  and logevent.useremail like 'email@test.com'

query 2: list all user with emails..

select userbase.username from userbase
join extrauserinfofromhrms on userbase.username 开发者_如何转开发= extrauserinfofromhrms.useremail

how to use query2 to list all users with emailaddress and use each emailaddrses as parameter in query1?


SQL is a set-based language. It works best when we stop thinking in a row-by-row fashion. What you ought to be using is an aggregating query this:

select userbase.username  
      , count(*) as total 
from userbase 
    join extrauserinfofromhrms on userbase.username = extrauserinfofromhrms.useremail 
    right join logevent on userbase.username = logevent.useremail 
    join eachworkflow on logevent.workflowid= eachworkflow.workflowid 
where logevent.actionname ='complete'  
and logevent.useremail like 'email@test.com' 
group by userbase.username 
/
0

精彩评论

暂无评论...
验证码 换一张
取 消