如何仅检索具有“SUM(if(release ='ER_APR15',1,0))AS'GROUP6'> 0`的记录?(How do I retrieve only record which has `SUM(if(release = 'ER_APR15',1,0)) AS 'GROUP6' > 0`?)
我有两个表:
tblscr_role Field: scr_roleid, role, requestid scrs Field: requestid, release我有以下query :
SELECT `role` as 'Role Name', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_APR15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_JUNE15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'GROUP6' from scrs, tblscr_role Where (scrs.requestid = tblscr_role.requestid) and (role Like '%:XX%') and `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role`; 如何仅检索具有SUM(if(release = 'ER_APR15',1,0)) AS 'GROUP6' > 0记录SUM(if(release = 'ER_APR15',1,0)) AS 'GROUP6' > 0 ? 如果我有低于SQL并且我希望得到一个结果,其中任何两列的值都超过0,我该怎么做?查询:
SELECT `role` AS 'Role Name', SUM( if( `release` = 'ER_APR15', 1, 0 ) ) AS 'ER_APR15', SUM( if( `release` = 'ER_JUNE15', 1, 0 ) ) AS 'ER_JUNE15', SUM( if( `release` = 'GROUP6', 1, 0 ) ) AS 'GROUP6' FROM scrs, tblscr_role WHERE (scrs.requestid = tblscr_role.requestid) AND (role LIKE '%:XX%') AND `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role` HAVING ((SUM( if( `release` = 'ER_APR15', 1, 0 ) ) >0) OR (SUM( if( `release` = 'ER_JUNE15', 1, 0 ) ) >0) OR (SUM( if( `release` = 'GROUP6', 1, 0 ) ) >0))I have following two tables:
tblscr_role Field: scr_roleid, role, requestid scrs Field: requestid, releaseI have existing following query:
SELECT `role` as 'Role Name', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_APR15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_JUNE15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'GROUP6' from scrs, tblscr_role Where (scrs.requestid = tblscr_role.requestid) and (role Like '%:XX%') and `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role`; How do I retrieve only record which has SUM(if(release = 'ER_APR15',1,0)) AS 'GROUP6' > 0? If I have below SQL and I want to have a result where any of the two column have more that value 0, how can I do that?Query:
SELECT `role` AS 'Role Name', SUM( if( `release` = 'ER_APR15', 1, 0 ) ) AS 'ER_APR15', SUM( if( `release` = 'ER_JUNE15', 1, 0 ) ) AS 'ER_JUNE15', SUM( if( `release` = 'GROUP6', 1, 0 ) ) AS 'GROUP6' FROM scrs, tblscr_role WHERE (scrs.requestid = tblscr_role.requestid) AND (role LIKE '%:XX%') AND `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role` HAVING ((SUM( if( `release` = 'ER_APR15', 1, 0 ) ) >0) OR (SUM( if( `release` = 'ER_JUNE15', 1, 0 ) ) >0) OR (SUM( if( `release` = 'GROUP6', 1, 0 ) ) >0))最满意答案
你可以使用;
SELECT `role` as 'Role Name', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_APR15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_JUNE15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'GROUP6' from scrs, tblscr_role Where (scrs.requestid = tblscr_role.requestid) and (role Like '%:XX%') and `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role` having SUM(if(release = 'ER_APR15',1,0)) > 0你也可以使用子查询..
Select * from ( Your_query_without_having ) as x Where GROUP6 > 0第二个问题:
值> 0任何两列:
SELECT role AS 'Role Name', SUM( if( release = 'ER_APR15', 1, 0 ) ) AS 'ER_APR15', SUM( if( release = 'ER_JUNE15', 1, 0 ) ) AS 'ER_JUNE15', SUM( if( release = 'GROUP6', 1, 0 ) ) AS 'GROUP6' FROM scrs, tblscr_role WHERE (scrs.requestid = tblscr_role.requestid) AND (role LIKE '%:XX%') AND release IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY role HAVING ( SUM( if( release = 'ER_APR15', 1, 0 ) ) >0 and SUM( if( release = 'ER_JUNE15', 1, 0 ) ) >0 ) or ( SUM( if( release = 'ER_APR15', 1, 0 ) ) >0 and SUM( if( release = 'GROUP6', 1, 0 ) ) >0 ) or ( SUM( if( release = 'ER_JUNE15', 1, 0 ) ) >0 and SUM( if( release = 'GROUP6', 1, 0 ) ) >0 )或使用sub query :
Select * from ( Your_query_without_having ) as x Where ( ER_APR15 > 0 and ER_JUNE15 > 0 ) or ( ER_APR15 > 0 and GROUP6 > 0 ) or ( ER_JUNE15 > 0 and GROUP6 > 0 );You can use having ;
SELECT `role` as 'Role Name', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_APR15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'ER_JUNE15', SUM(if(`release` = 'ER_APR15',1,0)) AS 'GROUP6' from scrs, tblscr_role Where (scrs.requestid = tblscr_role.requestid) and (role Like '%:XX%') and `release` IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY `role` having SUM(if(release = 'ER_APR15',1,0)) > 0You can use subquery too..
Select * from ( Your_query_without_having ) as x Where GROUP6 > 0For second question:
Any two columns having value > 0:
SELECT role AS 'Role Name', SUM( if( release = 'ER_APR15', 1, 0 ) ) AS 'ER_APR15', SUM( if( release = 'ER_JUNE15', 1, 0 ) ) AS 'ER_JUNE15', SUM( if( release = 'GROUP6', 1, 0 ) ) AS 'GROUP6' FROM scrs, tblscr_role WHERE (scrs.requestid = tblscr_role.requestid) AND (role LIKE '%:XX%') AND release IN ( 'ER_APR15', 'ER_JUNE15', 'GROUP6') GROUP BY role HAVING ( SUM( if( release = 'ER_APR15', 1, 0 ) ) >0 and SUM( if( release = 'ER_JUNE15', 1, 0 ) ) >0 ) or ( SUM( if( release = 'ER_APR15', 1, 0 ) ) >0 and SUM( if( release = 'GROUP6', 1, 0 ) ) >0 ) or ( SUM( if( release = 'ER_JUNE15', 1, 0 ) ) >0 and SUM( if( release = 'GROUP6', 1, 0 ) ) >0 )or using sub query:
Select * from ( Your_query_without_having ) as x Where ( ER_APR15 > 0 and ER_JUNE15 > 0 ) or ( ER_APR15 > 0 and GROUP6 > 0 ) or ( ER_JUNE15 > 0 and GROUP6 > 0 );更多推荐
发布评论