先根据如下语句生成测试表并填充数据

CREATE TABLE z ( a INT, b INT);
INSERT INTO Z SELECT 1,200;
INSERT INTO Z SELECT 1,100;
INSERT INTO Z SELECT 1,100;
INSERT INTO Z SELECT 2,400;
INSERT INTO Z SELECT 2,500;
INSERT INTO Z SELECT 3,NULL;
接着根据a列进行分组,并使用聚合函数GROUP_CONCAT,SQL语句如下

SELECT a, GROUP_CONCAT(b)
FROM z
GROUP BY a;
运行结果如下
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAHIAAACDCAIAAADecdiWAAAFSElEQVR4nO2dXW7bOBCAeZycwBB0gpzDLXSPvDouoAP4BkV/FoX8nhMYa6dPArZO2zS7eYliR7Ety5h9kETrL4pbc8ShPB+IQKGNaPhlTFHDQBHvP3zhpryJ9x++AKMa/VoXi4XeABQix8JaVUJI6+Pjo94AFCLHwlpVwlpRIKQ1CAK9ARyK79q26ze+RY5Fv9aHhwe9ARzKAVrlWFKt2228Wkfttzje3f37n66z/177+s6y3l2/Npbn1eZ5tUm1Rts4+V55m11aIsUaXJdfjePd7a87vLOrbNdDy3rbt14ZS0ta9+3zW/HmL7O1ZjZnl5awhrPXtUbb8HmN0mZDma7CGk6Lr8a73Y+fvxDPrnYgMv7Z0BJvPtWNJTlG1jobWsIazCphnYjWTbR9Clfq2/SyZ11Ow9VTuJoOLJEdyxbHu5vvP7HOjjqW/ufSG5KxJMep1vUmWj49Y7SP/XQC6PX7vd7g7+Krcbyb3/zAO7vKNh305GxWGYgcS3KMrrW5maT1sLEUta6jxTJsv8Vx/G3+XdfZMcaSHKdaV+vN4+Kp/baN43++3eg6O8ZYkuNU61dGKfLmVRtRFGk8u1rkWPSXWoypYB0AoQoWa8UNpQOwVhRYKwp6tDo2VKvrhLT6YAsQAoQA2037nKxnDC/2SNrW6rsgBAjiWsfgZKocAa4PYyft8V0QDgDU9OTRka0+2MS15nBtcH1wZEpmwVd78rDWRqoSs+NqTx7W2kQyAwBrVYh0CnUfeZ4Efh8f7JxTqLtA0bpkpSsBAULsr7alULSTD1KusUgvsBqgo/V4WCsKrBUF1opCWWvAKKKgVSNBh7OVQigdgLWiwFpRYK0o6NFKfXcgIx8n6ZtXM3YHKnFSL7UAmFDBSsjFyYVBddRq5TL2sbBWFHgSQCEXJ/VLlhG7A1AXJ+kFVgOktB4Ja0WBtaLAWlEoa8WumZ8OBa0aCTqcrRRC6QCsFQXWigJrRUGPVt4dUAzvDuCFbF4FiwuD6uAyNgqsFQWeBFDg3QHl8O4ARVgrCqwVBdaKQlkrds38dCho1UjQ4WylEEoHYK0osFYUWCsK7Wqte2JPNRQ6jJ19JZvwzWvliT21oRBh7IDtpNUW6qUWSfLEntpQKOC7YLtNZUB6hcHXQtFO6hTMerJQ3QwAlLS6duGhIsIGm77WWqdASeseMyaByhN7akMhhG/CJav2iT3VUAhR3M6iusBqhKLWP4W1osBaUWCtKJS1YtfMT4eCVo0EHc5WCqF0ANaKAmtFgbWi0K5Wc3YHnNxNtnw8NtWbV3N2B0q1PtKlljzEdwdKWmkXBiW0dwcg9wGXD8amXsYG8rsDeZJQDdBq0u6AGf98xJTdAXlp9dNdLNKXLGN2B3ILwfzSheoCqxFCWo+GtaLAWlFgrSiUtWLXzE+HglYhhK6vQRCAgG60slaNdFlrkjvtw9mKRZe1dj1bI3809y5CNT/t/N7zbv2zA7Ris6+6195HI2tdOrfeRQhnwZU3v3KitP8suBoFy+SrfHNDZ8lsqUdDtuZ2B0pl7FayNZwkyXUWXI3uJ6Ms0Y7RKiJ/NJ+c685WSX2NElWrzKzEzvl9OhscpRWWzu0+8fVkKwBkf0JectpCtu7Hn9m5u5hPzo/VCvLXQyRba/ay2tVacPfHWus6ta0EZBk4Pb2ObE06J47h2eq7hX+f3HK2lufWtD+ceHNvFCzlBS3Tna4ZDJhbxy8+Vqj1lUDOTl63N/eSVu3x7u9K3wogtxKo0t66FeMToDNbX4bvsrDofk2A662KtWLXzE+HglZGLawVhf8BK7xBq58XM1IAAAAASUVORK5CYII=" alt="" />
此外,GROUP——CONCAT聚合函数还有一些其他的用法,其语法如下:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

DISTINCT选项可以去除重复值,ORDER选择可以对列进行排序,SEPARATOR用于选择拼接的字符串。例如上述例子中,可以对b列进行去重并按递减的顺序排序,同时用“:”拼接字符串。其SQL语句可写为:

SELECT a,GROUP_CONCAT(DISTINCT b ORDER BY b DESC SEPARATOR ':')
FROM z
GROUP BY a;

运行结果如下图

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAVkAAABSCAIAAABxIDD7AAAJdElEQVR4nO2dzW7iyBqGv+vpC0DVvoTcAqu2I0u5hOyyaWkCRPIGZZv9KMTMSCyyzDqbAGGDdGZ6Zg5zjrLoTgIhIUQ1i7JNuX5sQzeYDO+jVxEU5a8+18+Lwdihn89/hSAIop/Pf+UAgJ2nfC94eHgoNwEAdhyxBuEFAOw62+IF9/f35SYAwI4j1iC8AIBdB14AAOB8e7zg27dv5SawfQwDh8jvlJ2GiWHgOMGw7CxW4f1mvn7EGizfC75+/WosHwYOxTjBMPWcUqOq1uTawIunqQiZS63j6xXNrcipyI2aIqQ3SGUjhx0GDvkd5fUoxqLQ76RjOEHHEND3F/WLZhvYEjN0bO6oqTsab5yzd8Z90hrWg3T8Ra1h4MibZGReMJmh0uFxMPNYq0G0OazvoLxxerTsk1+bhasg1mDkBa+v8+nzbPOaz9/+/t//9db7jY/ETvrR00HdO+nfnrCk5PaE0X5oqPmLR8Qag6lcWdRn6QjtfZIrKE3HwafPs9DbD7Na2ffYx/qt1IolQlxBqv+c2mq6CP6xfpsuvz1hRF5bqR/XFI+9X8wBk6erZKt1kbHQPGqmsUgGzrh3enDLoJtfZSf951noRR0VesQag0KZF0zG1OHW3jPWz95BeU5aBsvQD8ZJtYzEGnyavkReMHudP01f1qFencV+xmp99dX5/O2/o7/11l29cr/BWKMXPW27tB9GD9I1RbVUZWNhEkHRoMb0PDNbCffJa0sZGiNEXcHqg9AjVh9Y9uvlKdwn8dS4C6lyyy5kb1g4WzXOonDfZcYBLTIWcc75A5Qx6KZXlf3qN5ihsiXzgskYOtzee7kJZ+6gOlXs/WCeVMtIrMGnDXjBQsloaXnorVtmoTTwxgWT9FTe6PbqjPQmRE19geW1EnrkhvZZqMybJHlTcDG0pkYNqyj0iIjUXs0zkWLZ2r0gXkhqHxYZC+vAxTXlI2hlQSr9FiVD+jtN6BFjzA1XztySjN7hGb1nHKDMHZSy0qaKtR8sk2oZaV4we508Pa9FPWn/WaObfnX+9vbnXyO9dRbVvK2xeENjnF6DqTHbLnkXSrl4mpnJxBqwQCtJ/ORvVuS2S6zWMwfv1hir3ZoabbvkXRiTETPObZuzVZIsmG2Rrug1mEgpt5fyB840anmTR00ma1YslbklGb3DM+oUrJ/TOfFUsfWDbVItI7EGJ0/Pa/aCXoMlKZo6zuYFhtUiT+XFQGpdYJziufM+NQ/0Ps1vpVtjbk08NY9Kt5Z+SxALXusWqxfoS1rNMO6TAhvmZmsbr6wVldtLcv2CA2QedGOG9h7IzXyV2SKaK74C7WantWKYKpZ+sE6qZaR6wcvsdTyZ/nh16xVW706m48m0W2MUP040n799+eMvvfVUZRFECjW+8Ii8liFs6BJVav3xJHSJHXelaG4oJ5Ohbo0lwceTacv1WrZWUgFDl0jUMUXoHzNyL+JWLjx17ybaS1rwaPOkvFuvuKHez2pAuX7RbE1xjAOaJJAxFsaBM+6d3qJl0M2v2nogN/PiyWgdbu09Y/3sHYxkmirmfrBMqm69QtIgGh9LEmtwPJlGXvD8MnscP61DLTcyrYrrViq1m/Sr8/nb71/+NLZ+c7zwvMpx77Fbkze/OWZE7Lir1vzUiiN0a5XF9rUbUaIlYJQckNxQL4xaUQK2XIqfqhHUpsNPcfLWl+T85cpx0Na4d1zRKigB5frFs9XjjKXgSq/au87YS9HAtbL3Li5vWQddq++2xqYeKJJ5Tlcn5eYON/fe+OlRH6C8HbTOB2M/qNvKMyfuDdtjbQ0+jp/W7gXZyvCCndXNMUtPKQhaozQveJ49PE42r/l8/tvvf5TV+sPjxSdSYD/dlJKJrO5PFaJPF2WnAe2ExBp8eJxEXjB9frl/GG9er/P5f377UlbrEASJNXj/MI684BYAsNskv0EujdlsVmLrAACxBsu/NgnXKQJQLttynSK8AIBygRcAADiHFwAABPACAADnpXiB73D97itZXnAXOEdER0RH5FyJTTt+VJLcBEYv0en4p9JtaFaJAMC/lo16wTDgRJyW9YKB7w/Eo45/5AR3vHNOomR45dB5h3NDidr0lUNHRLEXrBABgH83Gz8uGHJnWS+QNg5OneCu4ydv3XeBcxoMuV4ijiac4E7aOnnJUN8UAYBd4l15gb5uo8d6SWEvyIgAwC7xjrwg+oCw+kqGFwBg5714QWIEfPUjfHxGAMDOe/AC7Wi/0Dd/WZ8R8N0hACplnEcgTsSVo/AML4hOAaROKxY4I5j2AjmIPzDVxzlFsNvgt0YAAM7hBQAAAbwAAMA5vAAAIEh5wTcAwA7DcVwAAIAXAAA4hxcAAATwAgAA5/ACAIBgg14w5E78A2QnMOdhIfl1MNHiUsVVfkHcOU9uZ4LfIAOQYoNe0Flcg+ATV64EzPOC1Ppc7cqizjk55764PAnXJgGgUM5nhMD5Hi9Y5b5GwyvHuRra6+OaZbDrlOEFpsuWC35GiC9SXO6+RpER8CXvjATALlGCF+gfEHjR7w7FHU2WXcnD4JTkq57p1HHgBQCk2bQXGI2AF/WCZe59agSfEQCwsOnzCLZFluUFyT3R7wLxfr7ifY34Yp3ju0MAFDbnBfJNjfTTilleIP2vFOkfJSx3X6NFKJxTBMAEfmsEAOAcXgAAEMALAACcwwsAAIKUF6z7rikAgG2Gb8txAXEIgsoSvACCIM7hBRAECcELIAji/P14wah9Vq1+vv4xu73XrF4etj+U3/sQtD3aqBf4yW+Q/eW8YHRwWP18zT+0Dy+rhwejqPxD+/CsPRJ/k8oZhXLMvWZVKYGg3dYGvUC6r5H5XibWLK+b4m38Q/vwrNk8i9/Sv8cLaNQ+qzb3yh8ACNoSlfMZwSeuXP+T5QXJe7hY0nvN6MPCd3kBHx0cLg4xIGjntWkvCBxOmhFke8Fi0cZL+vpztbn3vV7AE0+BIKjE44LinxF0L0gt+JW9wFgIQbuqcryg43PlJgHLeQHx0cFh8wDHBRD0w7Q5LxgGi2OBpY4L1O8LovLr5mW1etYeJd8sEufyGQd8XwBBy2ij5xGSmxrpdw4qfB5BWtKyR1xWq0J6yWXzWnlKHOcRIEjR+/itUfRu/wP3HL8vgKC03ocX4HeHELRuvRcvgCBovYIXQBDEueIF675rCgBgm9mK4wIAwDYALwAAcA4vAAAI4AUAAM7hBQAAAbwAAMA5vAAAIPgH26hSMxEBQpsAAAAASUVORK5CYII=" alt="" />

最新文章

  1. sql查找去重复并且字段不为空的数据
  2. JS截取,删除,替换字符串常用方法详细
  3. sqlserver表分区小结
  4. 取得DIV的ID还是CLASS
  5. mysql中all privileges包含哪些权限
  6. C++之路进阶——bzoj1823(满汉全席)
  7. 【LEETCODE OJ】Binary Tree Postorder Traversal
  8. Xcode5创建自己的静态库详解
  9. [质疑]编程之美求N!的二进制最低位1的位置的问题
  10. 改进的sqlhelper学习日志
  11. 基于注解Spring MVC综合Hibernate(需要jar包,spring和Hibernate整合配置,springMVC组态,重定向,)批量删除
  12. linux服务器开发二(系统编程)--进程相关
  13. c++实现一个小算法
  14. WC2001 高性能计算机
  15. decode-ways(动态规划)
  16. XML5个转义符:<,>,&,”,©;的转义字符分别如下: < >& " '
  17. vector容器用法详解
  18. HttpContext.Current.Session 和 Session 的区别
  19. hive报错: Specified key was too long; max key length is 767 bytes
  20. MyBatis 中#与$的区别

热门文章

  1. JAVA常用集合
  2. 转发:已知rsa的模数和指数 生成pem公钥文件
  3. JVM中的堆和栈
  4. c刷题
  5. 20155205 郝博雅 Exp 8 Web基础
  6. Java 包与类的命名(util、service、tool、dao )区别
  7. php中的冒泡排序和选择排序d
  8. fiddler中断request,修改参数问题
  9. 网络编程——I/O复用
  10. Entity Framework 自动生成代码 如何用继承