<\/span><\/h2>\n\n\n\npivot(\n <\u805a\u5408\u51fd\u6570>(<\u8981\u805a\u5408\u7684\u5217>)[ as \u522b\u540d][,\u805a\u5408\u51fd\u6570>(<\u8981\u805a\u5408\u7684\u5217>)[ as \u522b\u540d]...]\n for <\u52171[,\u52172...]> \n in (\u5e38\u91cf1 [as \u522b\u540d1] [,\u5e38\u91cf2[ as \u522b\u540d2]...])\n)<\/code><\/pre>\n\n\n\n<\/span>\u8bf4\u660e<\/span><\/h2>\n\n\n\n\u805a\u5408\u51fd\u6570\u90e8\u5206\uff1a\u4e3a\u65b0\u5217\u7684\u503c\uff0c\u53ef\u4ee5\u6709\u591a\u4e2a\u3002<\/p>\n\n\n\n
for\uff1a \u662f\u8981\u65cb\u8f6c\u7684\u5217\uff1a\u7b26\u5408\u6761\u4ef6\u7684\u5217\u503c\u5728in\u91cc\u6307\u5b9a;\u5355\u5217\u65f6\u53ef\u4ee5\u4e0d\u4f7f\u7528\u5c0f\u62ec\u53f7\uff0c\u6bd4\u5982for column_name in \uff0c \u591a\u5217\u65f6\u8981\u4f7f\u7528\u5c0f\u62ec\u53f7\uff0c\u4e14\u591a\u4e2a\u5217\u7684\u503c\u662f\u6574\u4f53\u4f5c\u4e3a\u4e00\u4e2ain\u4e2d\u7684\u5e38\u91cf\u503c\u3002<\/p>\n\n\n\n
in\uff1a\u6bcf\u4e2a\u5e38\u91cf\u503c\u4f1a\u8f6c\u6362\u6210\u4e00\u4e2a\u65b0\u5217\u3002\u5e38\u91cf\u4e3a\u5b57\u7b26\u6216\u65e5\u671f\u65f6\u9700\u8981\u4f7f\u7528\u5355\u5f15\u53f7\u5305\u56f4\u8d77\u6765\u3002\u65b0\u5217\u7684\u5217\u540d\uff0c\u5c06\u91c7\u7528\u5e38\u91cf\u503c\u6216\u8005\u5176\u522b\u540d\u3002\u5982\u805a\u5408\u5217\u6709\u8bbe\u7f6e\u522b\u540d\uff0c\u5219\u8ffd\u52a0\u4e0b\u5212\u7ebf\u548c\u522b\u540d\uff0c\u6bd4\u5982 \u5e38\u91cf\u503c\u522b\u540d1_\u805a\u5408\u5217\u522b\u540d\u3002 \u5e38\u91cf\uff0c\u5982\u679c\u662f\u591a\u5217\uff0c\u5f3a\u70c8\u5efa\u8bae\u591a\u5217\u5e38\u91cf\u8bbe\u7f6e\u522b\u540d\uff0c\u9ed8\u8ba4\u5217\u540d\u4e0d\u5bb9\u6613\u4f7f\u7528\u3002<\/p>\n\n\n\n
\u5355\u5217\uff1afor column in (\u5e38\u91cf1\uff0c\u5e38\u91cf2....)<\/p>\n\n\n\n
\u591a\u5217 for (column1,column2) in ( (col1\u7684\u503c\uff0ccol2\u7684\u503c) name1,(col1\u7684\u503c\uff0ccol2\u7684\u503c) name2,(col1\u7684\u503c\uff0ccol2\u7684\u503c). name3..)<\/p>\n\n\n\n
\u6ce8\u610f\uff1a\u6240\u6709\u672a\u53c2\u4e0efor\u548c\u805a\u5408\u7684\u5217\uff0c\u90fd\u5c06\u9ed8\u8ba4group\u3002\u53ef\u4ee5\u7528\u5d4c\u5957\u67e5\u8be2\u6765\u51cf\u5c11\u6d89\u53ca\u7684\u5217\u3002<\/p>\n\n\n\n
select * from ( select XX from tp) tmp pivot (\u3002\u3002\u3002\u3002\u3002)<\/p>\n\n\n\n
<\/span>\u6d4b\u8bd5\u7248\u672c<\/span><\/h2>\n\n\n\ngbase> select version();\n+---------------------------+\n| version() |\n+---------------------------+\n| 9.5.3.28.4_patch.1bcc63ff |\n+---------------------------+\n1 row in set (Elapsed: 00:00:00.00)<\/code><\/pre>\n\n\n\n<\/span>\u6837\u4f8b\u8868<\/span><\/h2>\n\n\n\n\n\ngbase> desc tp;\n+-------+-------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+-------------+------+-----+---------+-------+\n| name | varchar(10) | YES | | NULL | |\n| class | varchar(10) | YES | | NULL | |\n| score | int(11) | YES | | NULL | |\n+-------+-------------+------+-----+---------+-------+\n3 rows in set (Elapsed: 00:00:00.01)\n<\/code><\/pre>\n\n\n\n<\/span>\u6837\u4f8b\u6570\u636e<\/span><\/h2>\n\n\n\n\u6bcf\u4e2a\u5b66\u751f\uff0c\u6bcf\u95e8\u529f\u8bfe\u6709\u591a\u6b21\u8003\u8bd5\u6210\u7ee9<\/p>\n\n\n\n
gbase> select * from tp order by class,name,score;\n+--------+--------+-------+\n| name | class | score |\n+--------+--------+-------+\n| \u5f20\u4e09 | \u6570\u5b66 | 99 |\n| \u5f20\u4e09 | \u6570\u5b66 | 129 |\n| \u674e\u56db | \u6570\u5b66 | 88 |\n| \u674e\u56db | \u6570\u5b66 | 128 |\n| \u738b\u4e94 | \u6570\u5b66 | 111 |\n| \u738b\u4e94 | \u6570\u5b66 | 161 |\n| \u5f20\u4e09 | \u8bed\u6587 | 120 |\n| \u5f20\u4e09 | \u8bed\u6587 | 120 |\n| \u674e\u56db | \u8bed\u6587 | 140 |\n| \u674e\u56db | \u8bed\u6587 | 150 |\n| \u738b\u4e94 | \u8bed\u6587 | 100 |\n| \u738b\u4e94 | \u8bed\u6587 | 120 |\n+--------+--------+-------+\n12 rows in set (Elapsed: 00:00:00.02)<\/code><\/pre>\n\n\n\n<\/span>\u5355\u4e2a\u805a\u5408<\/span><\/h2>\n\n\n\n\u6bcf\u95e8\u8bfe\u7a0b\uff0c\u6307\u5b9a\u4eba\u5458\u7684\u603b\u5f97\u5206<\/p>\n\n\n\n
gbase> select * from tp pivot(sum(score) for class in ('\u8bed\u6587','\u6570\u5b66'));\n+--------+--------+--------+\n| name | \u8bed\u6587 | \u6570\u5b66 |\n+--------+--------+--------+\n| \u674e\u56db | 290 | 216 |\n| \u5f20\u4e09 | 240 | 228 |\n| \u738b\u4e94 | 220 | 272 |\n+--------+--------+--------+\n3 rows in set (Elapsed: 00:00:00.06)<\/code><\/pre>\n\n\n\n<\/span>\u591a\u4e2a\u805a\u5408<\/span><\/h2>\n\n\n\n\u6bcf\u95e8\u8bfe\u7a0b\uff0c\u6307\u5b9a\u4eba\u5458\u7684\u5e73\u5747\u5206\uff0c\u6700\u9ad8\u5206\u3002\u6ce8\u610f\uff0c\u8981\u907f\u514d\u5217\u540d\u91cd\u590d\uff0c\u6240\u4ee5\u7ed9\u6bcf\u4e2a\u805a\u5408\u51fd\u6570\u7ed3\u679c\u4e00\u4e2a\u522b\u540d\u3002\u663e\u793a\u65f6\u4f1a\u81ea\u52a8\u52a0\u4e0a\u4e0b\u5212\u7ebf\u3002<\/p>\n\n\n\n
\u5f53\u7136\uff0c\u5176\u4e2d\u4e00\u4e2a\u805a\u5408\u5217\u53ef\u4ee5\u4e0d\u52a0\u522b\u540d\uff0c\u5176\u5217\u540d\u5c31\u662f\u5e38\u91cf\u503c\u5bf9\u5e94\u7684\u540d\u5b57\u3002<\/p>\n\n\n\n
gbase> select * from tp pivot(sum(score)sum, avg(score)avg for class in ('\u8bed\u6587','\u6570\u5b66'));\n+--------+------------+------------+------------+------------+\n| name | \u8bed\u6587_sum | \u6570\u5b66_sum | \u8bed\u6587_avg | \u6570\u5b66_avg |\n+--------+------------+------------+------------+------------+\n| \u674e\u56db | 290 | 216 | 145.0000 | 108.0000 |\n| \u5f20\u4e09 | 240 | 228 | 120.0000 | 114.0000 |\n| \u738b\u4e94 | 220 | 272 | 110.0000 | 136.0000 |\n+--------+------------+------------+------------+------------+\n3 rows in set (Elapsed: 00:00:00.06)\n\ngbase> select * from tp pivot(sum(score), avg(score)avg for class in ('\u8bed\u6587','\u6570\u5b66'));\n+--------+--------+--------+------------+------------+\n| name | \u8bed\u6587 | \u6570\u5b66 | \u8bed\u6587_avg | \u6570\u5b66_avg |\n+--------+--------+--------+------------+------------+\n| \u5f20\u4e09 | 240 | 228 | 120.0000 | 114.0000 |\n| \u738b\u4e94 | 220 | 272 | 110.0000 | 136.0000 |\n| \u674e\u56db | 290 | 216 | 145.0000 | 108.0000 |\n+--------+--------+--------+------------+------------+\n3 rows in set (Elapsed: 00:00:00.06)\n<\/code><\/pre>\n\n\n\n<\/span>\u7ed9\u5e38\u91cf\u503c\u8d4b\u4e88\u522b\u540d<\/span><\/h2>\n\n\n\n\u7ed9\u8bfe\u7a0b\u540d\u5b57\u8bbe\u7f6e\u5217\u540d\u3002<\/p>\n\n\n\n
gbase> select * from tp pivot(sum(score)sum, avg(score)avg for class in ('\u8bed\u6587' yw,'\u6570\u5b66' sx));\n+--------+--------+--------+----------+----------+\n| name | yw_sum | sx_sum | yw_avg | sx_avg |\n+--------+--------+--------+----------+----------+\n| \u674e\u56db | 290 | 216 | 145.0000 | 108.0000 |\n| \u5f20\u4e09 | 240 | 228 | 120.0000 | 114.0000 |\n| \u738b\u4e94 | 220 | 272 | 110.0000 | 136.0000 |\n+--------+--------+--------+----------+----------+\n3 rows in set (Elapsed: 00:00:00.06)\n<\/code><\/pre>\n\n\n\n<\/span>FOR\u591a\u5217<\/span><\/h2>\n\n\n\n\u589e\u52a0\u4e00\u4e2a\u5e74\u9f84\u5217<\/p>\n\n\n\n
gbase> desc tp2;\n+-------+-------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+-------------+------+-----+---------+-------+\n| name | varchar(10) | YES | | NULL | |\n| class | varchar(10) | YES | | NULL | |\n| score | int(11) | YES | | NULL | |\n| age | int(11) | YES | | NULL | |\n+-------+-------------+------+-----+---------+-------+\n4 rows in set (Elapsed: 00:00:00.00)\n\ngbase> select * from tp2;\n+--------+--------+-------+------+\n| name | class | score | age |\n+--------+--------+-------+------+\n| \u5f20\u4e09 | \u8bed\u6587 | 120 | 40 |\n| \u674e\u56db | \u8bed\u6587 | 140 | 45 |\n| \u738b\u4e94 | \u8bed\u6587 | 100 | 50 |\n| \u5f20\u4e09 | \u6570\u5b66 | 99 | 40 |\n| \u674e\u56db | \u6570\u5b66 | 88 | 45 |\n| \u738b\u4e94 | \u6570\u5b66 | 111 | 50 |\n| \u5f20\u4e09 | \u8bed\u6587 | 120 | 40 |\n| \u674e\u56db | \u8bed\u6587 | 150 | 45 |\n| \u738b\u4e94 | \u8bed\u6587 | 120 | 50 |\n| \u5f20\u4e09 | \u6570\u5b66 | 129 | 40 |\n| \u674e\u56db | \u6570\u5b66 | 128 | 45 |\n| \u738b\u4e94 | \u6570\u5b66 | 161 | 50 |\n+--------+--------+-------+------+\n12 rows in set (Elapsed: 00:00:00.01)\n\ngbase> select * from tp2 order by class,name,score;\n+--------+--------+-------+------+\n| name | class | score | age |\n+--------+--------+-------+------+\n| \u5f20\u4e09 | \u6570\u5b66 | 99 | 40 |\n| \u5f20\u4e09 | \u6570\u5b66 | 129 | 40 |\n| \u674e\u56db | \u6570\u5b66 | 88 | 45 |\n| \u674e\u56db | \u6570\u5b66 | 128 | 45 |\n| \u738b\u4e94 | \u6570\u5b66 | 111 | 50 |\n| \u738b\u4e94 | \u6570\u5b66 | 161 | 50 |\n| \u5f20\u4e09 | \u8bed\u6587 | 120 | 40 |\n| \u5f20\u4e09 | \u8bed\u6587 | 120 | 40 |\n| \u674e\u56db | \u8bed\u6587 | 140 | 45 |\n| \u674e\u56db | \u8bed\u6587 | 150 | 45 |\n| \u738b\u4e94 | \u8bed\u6587 | 100 | 50 |\n| \u738b\u4e94 | \u8bed\u6587 | 120 | 50 |\n+--------+--------+-------+------+\n12 rows in set (Elapsed: 00:00:00.03)<\/code><\/pre>\n\n\n\n\u591a\u5217\u5e38\u91cf<\/p>\n\n\n\n
gbase> select * from tp2 pivot(sum(score)sum for (name,age) in (('\u5f20\u4e09',40)zs40,('\u674e\u56db',45)ls45));\n+--------+----------+----------+\n| class | zs40_sum | ls45_sum |\n+--------+----------+----------+\n| \u8bed\u6587 | 240 | 290 |\n| \u6570\u5b66 | 228 | 216 |\n+--------+----------+----------+\n2 rows in set (Elapsed: 00:00:00.06)\n<\/code><\/pre>\n\n\n\n\u5982\u679c\u4e0d\u6307\u5b9a\u5217\u522b\u540d\uff0c\u5219\u9ed8\u8ba4\u7684\u5217\u540d\u6709\u4e9b\u6df7\u4e71\uff0c\u4e0d\u65b9\u4fbf\u4f7f\u7528\u3002<\/p>\n\n\n\n
gbase> select * from tp2 pivot(sum(score) for (name,age) in (('\u5f20\u4e09',40),('\u674e\u56db',45)));\n+--------+---------------+--------------+\n| class | (('\u5f20\u4e09',40 | ('\u674e\u56db',45 |\n+--------+---------------+--------------+\n| \u8bed\u6587 | 240 | 290 |\n| \u6570\u5b66 | 228 | 216 |\n+--------+---------------+--------------+\n2 rows in set (Elapsed: 00:00:00.07)<\/code><\/pre>","protected":false},"excerpt":{"rendered":"\u5357\u5927\u901a\u7528GBase 8a\u63d0\u4f9b\u4e86pivot\u51fd\u6570\u5b9e\u73b0\u884c\u8f6c\u5217\u529f\u80fd\uff0c\u4e5f\u53eb\u6570\u636e\u900f\u89c6\u3002<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[36],"class_list":["post-11806","post","type-post","status-publish","format-standard","hentry","category-gbase8a","tag-36"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/11806","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=11806"}],"version-history":[{"count":52,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/11806\/revisions"}],"predecessor-version":[{"id":11861,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/11806\/revisions\/11861"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=11806"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=11806"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=11806"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}