[Oracle] decode 函数及其用法

2022-11-25 14:04:42

http://blog.csdn.net/oscar999/article/details/18399177

前言

DECODE()函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。

语法结构如下：decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

实例使用

1. 创建一个学生成绩表。栏位有：学生名，科目名和分数

create table student_score(
name varchar2(30),
subject varchar2(20),
score number(4,1)
);

2. 插入测试数据

insert into student_score (name,subject,score)values('zhang san','Chinese',90);
insert into student_score (name,subject,score)values('zhang san','Mathematics',80);
insert into student_score (name,subject,score)values('zhang san','English',79);

3.测试一：

select name,subject,decode(subject, 'Chinese',score,0) from student_score;

结果如下：

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAZcAAABUCAIAAADBD+30AAAMMElEQVR4nO2d36vcxhXH519aiBLXJiFwi8HgQkBgEC63+eWHhLbOXQIBi5gb+pASChahrUP3LeAktE2hu3kI7kO60JpwSWNokpo6doux6aX27k1aN43TuN0+SJpfOqMdSauZ0cz5PCR7d0ea853R+WpGd+8x2UUQBBkyZGmKlS/s7u7aDsECYapG3GR3d5f3FqMuZqyvXsnz2XYUpglTNeImll3s57/43dOnXnvyyfNPPP6T72y/evLb2Rtv/NZYDBuB5rMHWvQJUzXiJpZd7NTTr61Wq/v3/3vvq6+/+Nd/Pvv83okTr+zt7RkLozs0nz3Qok+YqhE3sexiTz310/v3/3f27C4h5MyZs7dvfxHHL1+4cMFYGN2h+eyBFn3CVI24iWUXe+LxH9/78mtCyGcHXxJCbt3652OP/WBYOUDz2QMt+nigerGYjUeExJntQBiL2XhE4myxwDCqLLKYkNF4BkRl2cW2t1+9e/crUnL9+sHx4y/p5EB+BY7GM/bjaDxbLJb06qQ/zsYjIlys+WhMpzsjwgMP0FpoPrfWwoKkcXByqDop4DjjxJI6RYss5g8bi40IN4wmVfOSmZzZWEdCfga+bS6h0TnzVHVq8IXLWDxwXVTxuelONSPAqAC9Ypudl3f4vsBLtHizzbyUI6OYF9YjO3thqYssJpXAltZd7OTJ7ODg34SQmzf/QQi58qc7x469qJsDozgecSPC25b8kfTjqJhgaESaQvO5vZZycorXNZesoLGuAaVYb5RvZnF5QYhnsKBamrI8SigqUMJsPGI3Knqb1j4nHXOnBn+xyGLmM8KB58Tz0zOIL+SMqEa1Vi8fRs1R3eYlzhbwvPBkcXG3YPeb8mCppWUXS5If7e/fJYRcu3ZACLl8ef/o0TMNciCL2e20HBH6CbsPjMbZeJT/mMUkHisvuxbQfG6vZZHPF3fRrE2kUriqQXnm4kRApxtysa4zuM4LQAl8UtUcXuMvzMTcG3xYMhRVxcXkjFC4WJ1eRUiVo9rOCz3VukFgPVazW2ps2cVOnHjlxo3PP/10+cknty9/uP/++7e2tl5olAOFVjqX/AfCNGcxibP8NbjYbpvSNJ9ba1nmm1xuTa6xqVFcK6Ki6XRH9YBjUy7WfQaLHxdZXG6L1kqAlw+ksmBRnHNWXhR5M9cGH3wsVdl/EQK6mJgR4EUO6NVIBOioNvMiJKa6X8HF+NfiLSTHsosdPrx99eqSCpnPbzz66PONcmDBGZMwqXS0uNklRJzgja7FWmthomiIG9rU1GTLplys+wwWP0I2pIqzsYtBez3plu7O4OtIVq7FxIyouchVeuupP6puXipu1WAtpliXUSy72De3nvvoo7//4cP9U6eeJ4T85uJfHn5kp2kOzMajfJM4A/2dn11uKbtxF2utRdAFZqN6Wa5qwJ2t3x3lRmawqYTuO0pwY+LI4MOStV1syWVE/UVe4/I1FEfdOddiXvgVXLsdpYtrsW8df+mDD/526dJNQsjFi9ffeefqkcPfb/xsOL9BjMbTO1P+4V8+pKqb5MZdrJMWcTszFS+RYuqlueSvaYXSZf9P9zc4gxt+ul97TmFfY3vwqZqaA/We7lcyArz4Jb016zU6zJWjyhVD83kppwWeF24oqk/3l24+Fzt27MXfX7pJCNnefm42u/qrt68c+sb32tzJoSspvzNUf20MPzLo/E2L9lqEFWQRRuU9aZ0ZZ8WumWshPzopTiUdBw6gBdVCWJDqdRL4N6EvVdSdU3xuannwwcWX3KFq6Vq2kG2OZoQY1c50KmtTRM4HBo5Ss3nhYpuNR8X3OdTzsqReXA74sswU535HefRo+t57f6U63nzr4wcfenYQ35mk0Hz2QIs+Hqjm7/B2AXdJLmA+sPoeHf2+2NbWC+++e206/fPbv7zy5lsfv/76H6MHnhlEDlBoPnugRR8PVLvz3f3ZeFRdX7iA+cBqenT3u/vnz//6kYfHR46cPnTouw8+9Gz0wDOnT/9wEDlAofnsgRZ9wlSNuIn9+mJ7e3sXRIZVEYGvtDV0LfqEqRpxE/suNnTCrBcYpmrETay5WN63H/ikRZ8wVSNuIrjYxkph17L0qO6+T1r0CVM14ibS8ghdrDE+adEnTNWIm6CLdcUnLfqEqRpxE3SxrvikRZ8wVSNugi7WFZ+06BOmasRNDLjYJInSeaVXdWtCSDLpIY6e4LRMkuLvcKI0TaJ0XqdlnkaDkimBLoYYII3yhBIypUwz9ma/LjbPo2jiYqvVKo2GlN6llklCx3WeRqXoYWnRB10M6Zt5GhXOMUmoZbG7P7c66n8txmJh0Bygq5fc7PI30yhJkvKt8kjWsmyWe0UUyS1Z02SS20ldbGXTqLSbBh3xWoAV51otzOBUXbAA+X6hsOGWvYEuhvQNn1J0NcAtCyZJmaHWXaz4bM5Fl0aE2i3N9MmEmTE9H1v/0JbcTm3t5pQNE7eQ0u1I1DJXLLpALeVHlXVyVQu1YO41ELaiZX+giyF9I6zFynsznzX0tWUXo034FRNot/yqjTMX4WX+f84bKs4hwZ2U61KvI1GL2sUALZWP4C645ZUYTiVsZcveQBdD+oc9aI4ix11skhBlejNvIsJ7G3IxMchmHUla1DvK1i6mOicYtoltJAe6GGISp3eU/FauJlD+HGlUu0Ti9lNztp0DEZ8ZNu2oooXXSU/dycVgFwbCbuDXGwJdDDEGn5XzNClesldmfkcpPcoWMp/fByUTuoiMUukhOG2ZPwNPJvTkyURsyTqN0tpfEQq/WhCeyul1JGmpqlVoEfrNd3/KLoAY4bBVb/cEuhjSN3wiw++WiwYD3xcDMJUD8vKnD8LM5zBVI27io4txayIDu6ww8zlM1Yib+OhiZvFJiz5hqkbcBF2sKz5p0SdM1YiboIt1xSct+oSpGnETdLGu+KRFnzBVI24iu9gSQRBkyOBarDE+adEnTNWIm6CLdcUnLfqEqRpxE3SxrvikRZ8wVSNugi7WFZ+06BOmasRN0MW64pMWfcJUjbhJny6mLj9akwPDrLs/ScS/WV1TY7aCadWdq/6jiyG2MFt3n5X6miSij9XnwLBq1S9pfTGu6DV7DQLVVOxFtap4Y2fQxRArWKq7X3QNu5hXdfeTJCqKCNGXwNnkwjyly4Cq5eVsvubLSwal+WfKsFUdAdWFhNZRkrASTtXxkWYQQUxiqUriCiiUyLmYJ3X383OlUTJh/1GeTbEW06q7X1T3nhc1HKkE/Y7K7oQP2KjNWXVIcHwE1QhiFksVqyvbyZWPdfeLFqVxMheDzrZuR1lXd788tGjDXEy7o0p36mbQ+AiqEcQsVlwMsLCV13X38zVRYQuKs2m6GFhNH3axJh1VutN4fFb5d5XQxRArGN9RcnsTCZYD3tTdFx0ntwXV2cR6+TUV+gEXBl2sUUdlM2lHKTyClK0RXQxxA9N196UNEZ/kNAc8qbtPu+TL7ZMonQNnk/ouVnAK1VKMP6Pt5sXetfiKh3ZHYNV/ebYqvzAguKNEHAHr7vdBmPkcpmrETXx0May73z9hqkbcxEcXM4tPWvQJUzXiJuhiXfFJiz5hqkbcBF2sKz5p0SdM1YiboIt1xSct+oSpGnET2cWWCIIgQwbXYo3xSYs+YapG3ARdrCs+adEnTNWIm6CLdcUnLfqEqRpxE3SxrvikRZ8wVSNugi7WFZ+06BOmasRNenUxZXmqmhwYZt19+W+sW0hQlGDVqpFvftDQxRBb2Kq7r6xYDTLIuvuK+m1NMX9gO9DFECtYrLsv18miOeBP3f3SR6QahU0jlMwIXqBBYYOD1h/oYogVrNTdzxNOXiZwLuZL3X1WmEwwkaYRgkuqirUBYYOD1h/oYogVbNXdX1XrVks54EPdfWgt1iJCHRcDwwYHrT/QxRArWHQxOVmFHPCj7r7CgHpxsWrY6GJIGJjeUXJ1sdVrMW/q7otew/3TRM0i1HAxuBw+uhgSAqbr7qu/aOFd3X3gmxbFP+qhHSFYDn/9m8LB1bL9vYEuhtgB6+73QTctJiLsA3QxxB18dDH36+6bjbAP0MUQd/DRxczikxZ9wlSNuAm6WFd80qJPmKoRN0EX64pPWvQJUzXiJuhiXfFJiz5hqkbcRHaxJYIgyJBBF0MQZNigiyEIMmzQxRAEGTb/Bx/5iv6ugcQ1AAAAAElFTkSuQmCC" alt="" />

如果是中文课程的话，显示分数，其他课程分数为零。

这条SQL 看上去使用意义不大。

测试二：

select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAALgAAAAmCAIAAAD4PcajAAAFV0lEQVR4nO2a32sUVxTHz7804NU0QRFSAoEUhAuBIWXrrzwotnEHQchgWOmDRQoZpG2EeROi0mqhuz5I+mAX2iCLNVC1oTFpCQldGmdW29QaNe324c6cufNjNzOTHefOON+X3Nm5O3O+537uuXc2A4uFCoUQVAoVCiEwBVA7L6pUKmmH0GNVKhU2RqKAknYIvREDJe0oeikRQfnyq++Pj18+enTmyOHPPyhdGntfu3r1u7RDiyYEJQdemEQEZfz45Xa7vb3979bL18//fvX02dbo6MVGo5F2dBGEoOTAC5OIoBw79sX29n9TUxUAmJyc2th4TumF2dnZtKOLIAQlB16YRATlyOHPtl68BoCnrRcAsL7+16FDH2cruQhKDrwwiQhKqXRpc/Ml2FpZaY2MnA+TXKOmSACSUnMOJaVmGKZpGniOHdYUCQCoht/VKICkVKtlCXhJSs2IYQRBie3FsqBRJxaq8Y7QIBczna6W/RkINGVlAI+5w0DjIoIyNqa1Wv8AwNranwCw+POT4eFzYUGRKJUsky5QAk55DiUrp9xIxBaCEtuLaZo1RQKgmh2MRum0Ozw06G54M+A3ZRgatVEwDI36EAx0xBoCgSLLnzabmwCwvNwCgIWF5tDQZGhQlJpGWangzeMZNtvYKU2R2KFGgSrBOY0nBCW+Fzv6AIM7gOLNQAdQHAT9Vw50xBoCgTI6enF19dnjx+ajRxsL95t3764PDp6NAAqmAdPHn3BlVqNANdYOrNJxoUFQduXFPZYmrpi+CP3E8BkINGWtafbi672yz7iIoPT3l5aWTIy5Xl89ePBMJFAMbuxdeURSuISy1b/T5IsnBGX3Xrp82LGiuDPQxZTjP4sV5d3B0w8e/PHj/eb4+BkA+Hbu1/0HylGTW1Mktpq4923c3g0TClQzkgIlvhduGxFo0OwKCp+B7qZY9ZneybiIoLw3cv7evd/n59cAYG5u5datpYH+j6KCYhULSak+qTIWrD4ahcBlOxlQYnsx429mfRkINMsvOiGMiwjK8PC5H+bXAKBUOl2rLX19c7HvnQ9jlOtAJthMLV/oDErXp8SQQlBie2Fy1cKQj8eBGXCbKler3IWDqm4mHo+HhtQ7d37DkK9df7h338ls/UiFoOTAC5OIoAwOnr19e7la/eXmjcVr1x9eufIT2XMiW8lFUHLghUlEUGZmvjmwXxkYmOjrO7V330my58TExCfZSi6CkgMvTCKCYppmo9GYdStb/3Hl30fJuhcmQUHJuooXlxJXkq97vlHlyQsTGyBI8oXLUDJz9M5snrww4UwuQOml8uSFqQAlEeXJC1MBSiLKkxemApRElCcvTGmBostErftC6dwbAGQ96aB6pwyBohL23wVXenXZ+2EKoNRZaFFAabfbKilA6b3qKrHGQZeRirpKrDY3nVOqKE6AjjC5Ns7AeGIfqkSWZfsj+5tOT7tbXSUAhBBvT6errKvE6R8cm92V2HRGuJHbi+DiCztORW5O6rLtV0xQrHN1LmSVAGKO7Ou6Mwnwerrs6+nMkZ1XMSd3zoVC38jnRXC5KoqNO1+8sS0iKNiFn/eBmPO1hxs/V5P95cbRN6oecRflbhnuRh28CCx0RgjJIii67FkdAkDhR9wzaLsBxR1ktBsFesmIsrf08DW/S/T8NVTSdaLjkFt7iy6guDd1UW/k95IR8R7rqmw1nVaKTz2+ZzJMLnfe6mLVR6J6HtywJ9tVyjpeXNbdPZ2bErXrA5RrJ+3aIYW7kceL4OLTEvypPZvfwh/cvLM/CWUFlPB6a0Dh6tgb+EGmACXZUNIOoWfKkxcmBOV/yKmVdywSwfEAAAAASUVORK5CYII=" alt="" />

统计中文课程的分数。看上去有点意义。

总体看来, decode 的使用看上去和case when 有点类似。如果只是用作以上两种状况，看上去意义不大。

select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;
select name,score as CHINESE from student_score;

使用的两句使用后的效果一样，看上去使用decode 多此一举。

行转列-有意义的使用

往以上table 再插入一些其他学生的成绩：

insert into student_score (name,subject,score)values('li shi','Chinese',96);
insert into student_score (name,subject,score)values('li shi','Mathematics',86);
insert into student_score (name,subject,score)values('li shi','English',76);
insert into student_score (name,subject,score)values('wang wu','Chinese',92);
insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
insert into student_score (name,subject,score)values('wang wu','English',72);

使用以下SQL:

select name,
sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
from student_score
group by name;

返回：
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAATcAAABVCAIAAACjCzSAAAAL7ElEQVR4nO1c74sctxnWn9SFTOLaJARcDAYXUgYOBpdrfvlDQlvHSyDgIeZCP6SEwi2hrQP7LeAktEmgu/kQ3A/pQmvCkcbQJDV17BZj06P27jmtm8ZJ3E4/zErzSno1OzvSzElv9HBwe/NLep5Xj6TRrV62uH07/sSf+OPzD7sUERHhN9hWRESE32CLDlBQwdbW1n5XwRYEKNgjRBG2traEobpyaReP7R9ldPe7FlYgQMEeIYrQk0t/9evfP3nilccfP/vYo7/4webLx78/eu2133VRXHcQ0Q2XCwEK9ghRhJ5ceuLJV4qiuHfvv3e//Przf391+7O7Gxsv7ezsdFFiRxDRDZcLAQr2CFGEnlz6xBO/vHfvf2fObDHGTp8+c/Pm52n64rlz57oosSOI6IbLhQAFe4QoQk8ufezRn9/94mvG2O29LxhjN27865FHfuKnIiaI6LrlMp8OB4PhdD53WlkcHVFYiT45roSHIqzUpyeXbm6+fOfOl4zj6tW9Y8deaKLIfDocMDYYTqs/OZ+5OFf+OR0OGGPpSNw7ShkbDCeTUwMGMRhO2zQXEd3WXJYURmlVl3SkR6i7Nm1PwSRyfeNzzsjmyc5EWLNFlVWdTE7pFfbFpcePj/b2/sMYu379n4yxS3++dfTo800VGaTpYCmE5FLklPLnwKRLC4jotuayWCymwwFj6YhXZpSm246q1wT2FEwi75dLW9zrSIS1Sw9gLM2yn+3u3mGMXbmyxxi7eHH3yJHTaygySsv+G/IRZ8qRtjw1Gg7KP0cpS4fG3qsFRHTbc+G11wkO+fiajuaw04XHF1IvvnyOfGCuX+CYgkHkKSxZq8q3NjZWcuH+Ly9YniuvXPnk6Xw+B1OUcoIC7+hCBH36YybI53SgNcIaorFW6iw+d+jSjY2Xrl377NNPF598cvPih7vvv3/j8OHn1lJklFYteDoH/XNp0+rAKC2jPBhORXNXpydWfbAVFzCQgoM8rqNUCqdy/NZ2ym+vnMyPjNJ0W7vA1ECtw6GKLDVZrUtFOOJceECZFNkVTx4MJ7cmpbK6zqNUFdxRHKUWZQgWsKvsUjVw2u164MTnDl168ODm5csLwWs2u/bww8+u5dK53CaAXTktYFt9UHI4ltpzMR3Uw6keh42DlbZkDDZ3+QJTA7WnoIgsj3CMKTFCOSpcOF/9rpVPVkIMa9dPHE1BNIV1ogdOvnh/XPqdw8989NE//vjh7okTzzLGfnv+rw8+dGpdRabDATa/4rGTgpqODCFsDRHd9lx4h2IiuNqlGBHRKFe+4jqgUCMyp4bbbBUX011NnowvyRi86lAE9MiyStvVkGgMKxa4/XTpd4+98MEHf79w4Tpj7Pz5q++8c/nQwR+vq8iSlT7DARNFXTvnLm3NZbFq9WiFS8EkShBMy3fy+XQ4GJyaqBc4p2BqoLC20+GAYU5eyWWlA01P1me8UBalW+xOBI2g1LkoM14pcC/64dKjR5//w4XrjLHNzWem08tvv3XpwLd/1EIR1JDlGAWpwnud/yemNZcS0ixA/k9MvUv1uR+fOC1ZLbQVC9N7qatwwCNi9YavAZcvXYyBNZ46LuYZb5Mnw6d9b1uVxb0IcotCbSb9yw2f8arTQFThRW8uPXIkf++9v4k6v/7Gx/c/8HSg32oIlwsBCvboXwSx6NUaPbn08OHn3n33ymTyl7fevPT6Gx+/+uqfkvueCqtZiOiGy4UABXv0JgIYSvF1rOboyaVnz/7moQeHhw6dPHDgh/c/8HRy31MnT/40rGYhohsuFwIU7BGiCP3tL93Z2Tknw8/9BybAfYmBciFAwR4hihB3gTdFiLuHFRCgYI8QRejcpWUZNECACwEK9ghRhMql7ZKy1GBBKO8RAS4EKNgjRBHgmBddWgcCXAhQsEeIIkSXNgUBLgQo2CNEEaJLm4IAFwIU7BGiCNGlTUGACwEK9ghRBIcuHWdJPtOebvdMj+Azlzwpv+OSjatj42z5xZcqKj5TsAbnm+TIQR9FEHVTQofX2YFLZ2UziS7dD8zyZCn8OBPBzhP+cZyJhustBXsI6pUavoswzkSHMsuTVYFzNJZCeTgWiwXvG/i5paGzMexMeG1mecJYkiRqX1Jdmo3zROkvC/kitaDNjPdU/Hw7ft5EVwWcw1TRls+Xn7ylYI9KhBmmgd8i4FWW69y1S7W5MP9zPB7rt44zPvrD7hH0L/K8TueFFAREAB3YmvAwuiWksVSbz8AW4C0FB5jxWb8+o/NeBNSkSp27d2lZIHeYsBKcmAOXSh/L34AEmNWhdcAKou1S+CaTyDGY5QmcO3hMwRazPBPNZjxWTvktAjaS6nXu3qWlX/Iky/MkGy8rBe2mWLO1S9GC6Lu0Agx4ObLCs0FQaAcpxHJz8V0EbdUVrXMPLi3GWVZ283mWlHoqb/l1Lp3lifTiWuNSpKCiKERR5nfa1fAuuhoUSXWd/KfQGsCa1ccgRFBMaqqzuzVe7b8BQhExgqstiU/Uyhv5c/i0lT8OvnTgb9qwJlpBYJU+r3+xrYFv0RWA4miHpJh4S8EBwOvTkm0YIsiTO3Odw/pWQ/spqyU8i24bEKBgjxBFCMSloI9pNRA6QIjRVUCAgj1CFCEQl3oAAlwIULBHiCJElzYFAS4EKNgjRBGiS5uCABcCFOwRogjRpU1BgAsBCvYIUQTJpYuIiAi/EcfSOhDgQoCCPUIUIbq0KQhwIUDBHiGKEF3aFAS4EKBgjxBFiC5tCgJcCFCwR4giRJc2BQEuBCjYI0QRXLgUfH1P2RVTo8iK/dz+wefoYnmP5P0KRVH4TcEaaN4jn0Uw5T1CounCpdVWoXEm+7RekfrdLb7Bm+iqQPMeVfktwM4obynYA8175LcIeN4jNJpuZ7zqHlOhiNRv8MrlSZaJDlDdZOYqGVL1iPJSset0jYJkLr4BzXuEbnz3loI90LxHoYgwUyqqRdOpS7WN4MClyxOwQtWGV9BtuE+GBJlXD2pckMbFN6B5j+A8BQZ7X2rYB7C8R6GIAOuJRtOhS9XpbqEpomRzQbs698mQ5IeCIpsVZODiE5C8R6E0UFdA8x6FIYKa9wiJpiuXIhYtFEVAftESiEs7SYYEIJKzNC8I5eIrvrEzXjTvURgiINnml3A6453BvEUSKkXAVLOmJXWTDEleVFm3IJ2Lr5AXTvjYAkYZ/ym0Bpr3KAgRTCaF0XTgUiVXCyxTKCJfAlJmJ7myWt5BMiRp6Up6K25WkMLFNyB5jwr8Pc1bCg6AvdSEIII6GKDRpPGthj6SIXkW3TYgQMEeIYoQskv7TYYUYnQVEKBgjxBFCNml/YIAFwIU7BGiCNGlTUGACwEK9ghRhOjSpiDAhQAFe4QoQnRpUxDgQoCCPUIUQXLpIiIiwm/EsbQOBLgQoGCPEEWILm0KAlwIULBHiCJElzYFAS4EKNgjRBGiS5uCABcCFOwRogjRpU1BgAsBCvYIUQQnLkW/6bx8unyF9k0+sJl7/SINN7Z9Zj18ji6a9wgcr7Yp7kft+gGe96jwVwRj3qMCq7O1S+FWIUNGFV62S/P0nDbJm+iqQDPlFPKOvBLeUrAHmveo8FoEPO9RYaizwxmvuhu73qXGAVYHlrioNm3SN2gsRTPloNsWvaVgDzTvUSgiSCY11NmJS0sbqd5oMpY2GRLRxEVo2qTmz1wXHka3BJopZ5ZnWabOg72l4ADYVtJQRJDzHuF1djuWrj3jbeQo7M0XzZexxjPXhIfR5UAy5YD39qoP85iCLdC8R2GIIE93TXV2ucarjGrOXCoAEqlEl+oQxGF/KWYiQVBoBzTvURgiyFNcU51tXQq7sW7GUiRxURFdqkFaOAFCiWbrP4XWQPMeBSGC+h5qqLODNV7DerJQRF50XjoZPbiqADAb4MvucMGo+TPXhm/RFcDzHkkrbsuoeEvBAQyN0HsRkGRAaJ3jtxoagQAXAhTsEaII0aVNQYALAQr2CFGE6NKmIMCFAAV7hChCdGlTEOBCgII9QhQhurQpCHAhQMEeIYoQXdoUBLgQoGCPEEWQXLqIiIjwG9GlERG+I7o0IsJ3/B+JS3NSY0mmbgAAAABJRU5ErkJggg==" alt="" />

将行的数据转化为列，是不是很有意义了。

使用case then 也可以达到相同的效果。

select name,
sum(case when subject='Chinese'
then nvl(score,0)
else 0
end) "Chinese",
sum(case when subject='Mathematics'
then nvl(score,0)
else 0
end) "Mathematics",
sum(case when subject='English'
then nvl(score,0)
else 0
end) "English"
from student_score
group by name;

码农公寓