{"id":366,"date":"2011-11-24T09:20:35","date_gmt":"2011-11-24T01:20:35","guid":{"rendered":"https:\/\/blog.rexdf.org\/?p=366"},"modified":"2011-11-24T09:20:35","modified_gmt":"2011-11-24T01:20:35","slug":"oracle-case-when","status":"publish","type":"post","link":"https:\/\/blog.rexdf.org\/ja\/2011\/11\/oracle-case-when\/","title":{"rendered":"ORACLE CASE WHEN"},"content":{"rendered":"
\n

ORACLE CASE WHEN \u53ca SELECT CASE WHEN\u7684\u7528\u6cd5<\/span><\/a><\/span><\/h3>\n<\/div>\n
\u5206\u7c7b\uff1a\u00a0<\/span>ORACLE SQL\u4f18\u5316<\/a><\/span>2009-08-12 14:10<\/span>\u00a0<\/span>6644\u4eba\u9605\u8bfb<\/span>\u00a0<\/span><\/span>\u8bc4\u8bba<\/a>(0)<\/span><\/span>\u00a0<\/span><\/span>\u6536\u85cf<\/a><\/span>\u00a0<\/span><\/span>\u4e3e\u62a5<\/a><\/span><\/span><\/div>\n
\n

\u00a0CASE \u8bed\u53e5<\/h3>\n

CASE selector
\nWHEN value1 THEN action1;
\nWHEN value2 THEN action2;
\nWHEN value3 THEN action3;
\n\u2026..
\nELSE actionN;
\nEND CASE;<\/p>\n

CASE\u8868\u8fbe\u5f0f<\/h3>\n

DECLARE
\ntemp VARCHAR2(10);
\nv_num number;
\nBEGIN
\nv_num := &i;
\ntemp := CASE v_num
\nWHEN 0 THEN ‘Zero’
\nWHEN 1 THEN ‘One’
\nWHEN 2 THEN ‘Two’
\nELSE
\nNULL
\nEND;
\ndbms_output.put_line(‘v_num = ‘||temp);
\nEND;
\n\/<\/p>\n

CASE\u641c\u7d22\u8bed\u53e5<\/h3>\n

CASE
\nWHEN (boolean_condition1) THEN action1;
\nWHEN (boolean_condition2) THEN action2;
\nWHEN (boolean_condition3) THEN action3;
\n\u2026\u2026
\nELSE\u00a0\u00a0\u00a0 actionN;
\nEND CASE;<\/p>\n

CASE\u641c\u7d22\u8868\u8fbe\u5f0f<\/h3>\n

DECLARE
\na number := 20;
\nb number := -40;
\ntmp varchar2(50);
\nBEGIN
\ntmp := CASE
\nWHEN (a>b) THEN ‘A is greater than B’
\nWHEN (a<b) THEN ‘A is less than B’
\nELSE
\n‘A is equal to B’
\nEND;
\ndbms_output.put_line(tmp);
\nEND;
\n\/<\/p>\n

SELECT CASE WHEN \u7684\u7528\u6cd5<\/h3>\n

select \u4e0e case\u7ed3\u5408\u4f7f\u7528\u6700\u5927\u7684\u597d\u5904\u6709\u4e24\u70b9\uff0c\u4e00\u662f\u5728\u663e\u793a\u67e5\u8be2\u7ed3\u679c\u65f6\u53ef\u4ee5\u7075\u6d3b\u7684\u7ec4\u7ec7\u683c\u5f0f\uff0c\u4e8c\u662f\u6709\u6548\u907f\u514d\u4e86\u591a\u6b21\u5bf9\u540c\u4e00\u4e2a\u8868\u6216\u51e0\u4e2a\u8868\u7684\u8bbf\u95ee\u3002\u4e0b\u9762\u4e3e\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\u6765\u8bf4\u660e\u3002\u4f8b\u5982\u8868 students(id, name ,birthday, sex, grade)\uff0c\u8981\u6c42\u6309\u6bcf\u4e2a\u5e74\u7ea7\u7edf\u8ba1\u7537\u751f\u548c\u5973\u751f\u7684\u6570\u91cf\u5404\u662f\u591a\u5c11\uff0c\u7edf\u8ba1\u7ed3\u679c\u7684\u8868\u5934\u4e3a\uff0c\u5e74\u7ea7\uff0c\u7537\u751f\u6570\u91cf\uff0c\u5973\u751f\u6570\u91cf\u3002\u5982\u679c\u4e0d\u7528select case when\uff0c\u4e3a\u4e86\u5c06\u7537\u5973\u6570\u91cf\u5e76\u5217\u663e\u793a\uff0c\u7edf\u8ba1\u8d77\u6765\u975e\u5e38\u9ebb\u70e6\uff0c\u5148\u786e\u5b9a\u5e74\u7ea7\u4fe1\u606f\uff0c\u518d\u6839\u636e\u5e74\u7ea7\u53d6\u7537\u751f\u6570\u548c\u5973\u751f\u6570\uff0c\u800c\u4e14\u5f88\u5bb9\u6613\u51fa\u9519\u3002\u7528select case when\u5199\u6cd5\u5982\u4e0b\uff1a
\nSELECT\u00a0\u00a0 grade, COUNT (CASE WHEN sex = 1 THEN 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/*sex 1\u4e3a\u7537\u751f\uff0c2\u4f4d\u5973\u751f*\/
\nELSE NULL
\nEND) \u7537\u751f\u6570,
\nCOUNT (CASE WHEN sex = 2 THEN 1
\nELSE NULL
\nEND) \u5973\u751f\u6570
\nFROM students GROUP BY grade;<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

ORACLE CASE WHEN \u53ca SELECT CASE WHEN\u7684\u7528\u6cd5 \u5206 … \u7d9a\u304d\u3092\u8aad\u3080 →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[6],"tags":[50],"class_list":["post-366","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-2"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/posts\/366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/comments?post=366"}],"version-history":[{"count":0,"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/posts\/366\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/media?parent=366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/categories?post=366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.rexdf.org\/ja\/wp-json\/wp\/v2\/tags?post=366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}