ORACLE 判断同一列多种情况值不同的结果(见详情)，在线等。

[yangzhongzhen]

各位大虾：
现遇到一SQL，看着还挺棘手，烦请帮忙看看， 下面是一SQL query出来的结果，确认无问题，


TYPE_ID PARAM_NAME STRING_VALUE DATA_TYPE_ID
5000000 WORKGROUP_OPTION_NGE_ENABLE_BUYING 1 1
5000000 WORKGROUP_OPTION_ENABLE_NGE 1 1
5000000 WORKGROUP_OPTION_NGE_ENABLE_SELLING 1 1
5000000 WORKGROUP_OPTION_NGE_ENABLE_PO 0 1
5000000 WORKGROUP_OPTION_NGE_ENABLE_QUOTES 1 1
就是基于当前结果，做一判断，当满足WORKGROUP_OPTION_NGE_ENABLE_BUYING对应的string_value=1,WORKGROUP_OPTION_ENABLE_NGE对应的string_value=1,WORKGROUP_OPTION_NGE_ENABLE_SELLING对应的string_value=1,WORKGROUP_OPTION_NGE_ENABLE_PO对应的string_value=0,WORKGROUP_OPTION_NGE_ENABLE_QUOTES对应的string_value=1,如果这些条件都满足，得出来一结果为true。
我采用case when判断，似乎不对。

MAX(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_ENABLE_NGE' AND PPP.PR_DATA_TYPE_ID=1 AND PPA.STRING_VALUE=1 AND NSP.NL_SERVICE_PROVIDER_TYPE_ID=5000000 THEN
(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_NGE_ENABLE_BUYING' AND PPP.PR_DATA_TYPE_ID=1 AND PPA.STRING_VALUE=1 THEN
(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_NGE_ENABLE_SELLING' AND PPP.PR_DATA_TYPE_ID=1 AND PPA.STRING_VALUE=1 THEN
(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_NGE_ENABLE_RFE' AND PPP.PR_DATA_TYPE_ID=1 AND PPA.STRING_VALUE=1 THEN
(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_NGE_ENABLE_PO' AND PPP.PR_DATA_TYPE_ID=1 AND NVL(PPA.STRING_VALUE,0)=0 THEN
(CASE WHEN PPP.PARAM_NAME='WORKGROUP_OPTION_NGE_ENABLE_QUOTES ' AND PPP.PR_DATA_TYPE_ID=1 AND PPA.STRING_VALUE=1 THEN
'Yes' ELSE 'No' END)
ELSE 'No' END)
ELSE 'No' END)
ELSE 'No' END)
ELSE 'No' END)
ELSE 'No' END)


烦请各位大佬帮忙看看，谢谢。

[nayi_224]

select TYPE_ID,
       decode(
       sum(decode(WORKGROUP_OPTION_NGE_ENABLE_BUYING, 1, 1, 0) + 
       decode(WORKGROUP_OPTION_ENABLE_NGE	           , 1, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_SELLING	   , 1, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_PO	       , 0, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_QUOTES	   , 1, 1, 0))
       , 5, 'true', 'false')
  from tab
 group by TYPE_ID
;

[nayi_224]

引用 1 楼 nayi_224 的回复:

select TYPE_ID,
       decode(
       sum(decode(WORKGROUP_OPTION_NGE_ENABLE_BUYING, 1, 1, 0) + 
       decode(WORKGROUP_OPTION_ENABLE_NGE	           , 1, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_SELLING	   , 1, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_PO	       , 0, 1, 0) + 
       decode(WORKGROUP_OPTION_NGE_ENABLE_QUOTES	   , 1, 1, 0))
       , 5, 'true', 'false')
  from tab
 group by TYPE_ID
;

写的什么玩意

select TYPE_ID,
       decode(
       sum(case when p_name || _val in ('WORKGROUP_OPTION_NGE_ENABLE_BUYING_1', '', '', '', '') then 1 else 0 end)
       , 5, 'true', 'false')
  from tab
 group by TYPE_ID
;
;

[yangzhongzhen]

明白这些意思。不需要解释了，谢谢大侠

[andylei]

你可以采用CASE WHEN，或者DECODE等函数判断