本文通过实例介绍了MySQL中的group_concat函数的使用方法,比如select group_concat(name) 。
MySQL中group_concat函数
完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
基本查询
mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)
以id分组,把name字段的值打印在一行,逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
以id分组,把name字段的值打印在一行,分号分隔
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)
以id分组,把去冗余的name字段的值打印在一行,
逗号分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)
以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)
本人在做用户有哪些角色动态显示是用到.可以提高代码简洁性与程序的效率,不用对比用户角色关联表与用户角色与用户表.
JavaScript 动态实现树形菜单(使用dtree控件)
@author Bob 2012/08/09
动态生成树型菜单
数据库脚本:
use master
go
if exists (select * from sysdatabases where name = 'tree')
drop database tree
go
create database tree
on
(
Name = 'tree_data', --主数据文件的逻辑名称
FileName = 'D:/tree_data.mdf', --数据文件的物理名称
Size = 5Mb, -- 主数据文件的初始大小
FileGrowth = 20% --主数据文件增长率
)
log on
(
Name = 'tree_log',
FileName = 'd:/tree_log.ldf',
Size = 3Mb,
FileGrowth = 10%
)
go
if exists (select * from sysobjects where name = 'dtree')
drop table dtree
go
use tree
go
/*--- 创建节点表 ---*/
create table dtree
(
id varchar(20) , --节点编号
pid varchar(20) , --父节点
[name] varchar(20), --节点名称
url varchar(20), --连接地址
title varchar(20), --节点描述
target varchar(20), --Target
icon varchar(20), --图标
iconOpen varchar(20), --展开状态下的图标路径
[open] varchar(20) --是否展开
)
/*--- 初始化测试数据 ---*/
insert into dtree values('0','-1','T16班','index.jsp','blank','','','','')
insert into dtree values('1','0','教师','index.jsp','blank','','','','')
insert into dtree values('2','0','班干部','index.jsp','blank','','','','')
insert into dtree values('3','0','组长','index.jsp','blank','','','','')
insert into dtree values('4','0','学员','index.jsp','blank','','','','')
insert into dtree values('5','1','班主任','index.jsp','blank','','','','')
insert into dtree values('6','1','教员','index.jsp','blank','','','','')
insert into dtree values('7','2','班长','index.jsp','blank','','','','')
insert into dtree values('8','2','学委','index.jsp','blank','','','','')
insert into dtree values('9','4','学员1','index.jsp','blank','','','','')
insert into dtree values('10','4','学员2','index.jsp','blank','','','','')
insert into dtree values('11','4','学员3','index.jsp','blank','','','','')
insert into dtree values('12','4','学员4','index.jsp','blank','','','','')
insert into dtree values('13','4','学员5','index.jsp','blank','','','','')
insert into dtree values('14','4','学员6','index.jsp','blank','','','','')
insert into dtree values('15','4','学员7','index.jsp','blank','','','','')
insert into dtree values('16','4','学员8','index.jsp','blank','','','','')
insert into dtree values('17','4','学员9','index.jsp','blank','','','','')
insert into dtree values('18','4','学员10','index.jsp','blank','','','','')
insert into dtree values('19','4','学员11','index.jsp','blank','','','','')
insert into dtree values('20','4','学员12','index.jsp','blank','','','','')
insert into dtree values('21','4','学员13','index.jsp','blank','','','','')
insert into dtree values('22','4','学员14','index.jsp','blank','','','','')
insert into dtree values('23','4','学员15','index.jsp','blank','','','','')
select id,pid,[name],url,title,target,icon,iconOpen,[open] from dtree
客户端代码 index.jsp:
HTML PUBLIC"-//W3C//DTD HTML 4.01 Transitional//EN">
树形菜单TreeNodeBiz tnb = new TreeNodeBiz();
%>
d =
document.write(d);
转换成节点数据转换成js文件 关键代码:
if(list==null || list.size() == 0){
System.out.println("没有节点");
return "";
}
StringBuffer contents = new StringBuffer();
contents.append("dtree = new dTree('"+dtree+"');");
for(TreeNode tn : list){
contents.append("/n");
contents.append(dtree+".add('");
contents.append(tn.getId());
contents.append("','");
contents.append(tn.getPid());
contents.append("','");
contents.append(tn.getName());
contents.append("','");
contents.append(tn.getUrl());
contents.append("','");
contents.append(tn.getTitle());
contents.append("','");
contents.append(tn.getTarget());
contents.append("','");
contents.append(tn.getIcon());
contents.append("','");
contents.append(tn.getIconOpen());
contents.append("','");
contents.append(tn.getOpen());
contents.append("');");
contents.append("/n");
}
客户端的代码:
需要导入:dtree.js 和dtree.css
HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
树形菜单全部展开 |
全部关闭
TreeNodeBiz tnb = new TreeNodeBiz();