如何仅检索具有“SUM（if（release ='ER

如何仅检索具有“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, release

I 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)) > 0

You can use subquery too..

Select * from ( Your_query_without_having ) as x Where GROUP6 > 0

For 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 );