字段名: cn |objectClass
字段值:Administrator |top,person,orgPerson,user根据objcetClass值的逗号生成下列:字段名: cn |objectClass |col1|col2 |col3 |col4
字段值:Administrator |top,person,orgPerson,user |top |person |orgPerson |user注意:字段objectClass的值不定,也就是n个","分割
字段值:Administrator |top,person,orgPerson,user根据objcetClass值的逗号生成下列:字段名: cn |objectClass |col1|col2 |col3 |col4
字段值:Administrator |top,person,orgPerson,user |top |person |orgPerson |user注意:字段objectClass的值不定,也就是n个","分割
解决方案 »
- 左链接查询速度慢,怎么解决
- 急求帮忙 loongson下安装lamp
- 怎样避免mysql(root)密码不被人更改!
- mysql 可视化窗口下删除数据报错
- 一个SQL中使用了LEFT函数截取子串,可是字符串是中英文混合的
- mysql如何实现sql的计划功能.就是定时执行一些语句.
- mysql3.23升级到4.0.15a的问题
- ************怎么设置表的最大行*****************
- “无法为更新行集定位:一些只可能已在最好读取后改变”,请帮忙看看,谢谢!
- 建立一个账号abc 对test库的test表有DELETE 测试的时候竟然能插入 能查询 求大神给指点
- 请教一个MySQL跨表查询语句(突然想不起来了)
- 关于自动编号的问题?
--建立测试环境
Create Table 表(year varchar(10),name varchar(10),value varchar(10))
--插入数据
insert into 表
select '2002','a','1' union
select '2002','b','4' union
select '2002','c','5' union
select '2003','a','7' union
select '2003','b','5' union
select '2003','c','4' union
select '2004','a','4'
select * from 表
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT name'
SELECT @SQL= @SQL+
',sum(CASE WHEN year = ''' + year + ''' THEN value else 0 END) [' + year + ']'
FROM (SELECT DISTINCT year FROM 表) A
SET @SQL=@SQL+' FROM 表 GROUP BY name'
exec (@SQL)
--删除测试环境
Drop Table 表
/*
name 2002 2003 2004
a 1 7 4
b 4 5 0
c 5 4 0
substring_index('top,person,orgPerson,user',',',1) as col1,
substring_index(substring_index('top,person,orgPerson,user',',',2),',',-1) as col2,
substring_index(substring_index('top,person,orgPerson,user',',',3),',',-1) as col3,
substring_index(substring_index('top,person,orgPerson,user',',',4),',',-1) as col4
from table_name ;
substring_index(objectClass,',',1) as col1,
substring_index(substring_index(objectClass,',',2),',',-1) as col2,
substring_index(substring_index(objectClass,',',3),',',-1) as col3,
substring_index(substring_index(objectClass,',',4),',',-1) as col4
from table_name ;