这是我在工作中的语句,功能是实现动态显示列并且实现查找功能,请大家多多指教!
/*1*/
set group_concat_max_len=8000;
/*2动态获取产品列-用于显示*/
select concat('<select class=textbox size=1 name=FieldName><option></option>',
group_concat('<option value=',b.objname,'>',b.objjc,'</option>'),'<option value=></option></select>') `FieldList`
from gwpuser_filefields b
where b.visible='1' and b.parentid=1
order by b.objorder asc;
/*3动态获取列*/
select @fieldname:=concat(group_concat('a.'
,lower(b.objname)
,' `'
,CASE WHEN ifnull(b.YESNO,'0')='1' THEN concat('ORD_',b.objjc,'') ELSE b.objjc END
,'`')
)
from gwpuser_filefields b
where b.visible='1' and b.parentid=1 and b.objid<>1
order by b.objorder asc
;
/*4列替换列的显示,超链接*/
select @fieldsql:=concat(
replace(
replace(replace(replace(replace(
replace(replace(replace(
replace(@fieldname
,'a.filetitle','concat(''<a href=shell.php?objname=sample&objid='',a.objid,'' target=xiangxing>'',ifnull(a.filetitle,''''),''</a>'')')
,'a.filepic','concat(''<img src='',ifnull(a.filepic,''''),'' width=40px height=20px>'')')
,'a.tphoto','concat(''<img src='',ifnull(a.Tphoto,''''),'' width=40px height=20px>'')')
,'a.mphoto','concat(''<img src='',ifnull(a.Mphoto,''''),'' width=40px height=20px>'')')
,'a.yesno','case when a.yesno=''1'' then ''是'' else ''否'' end ')
,'a.isnew','case when a.isnew=''1'' then ''新'' else ''老'' end ')
,'a.cancomment','case when a.cancomment=''1'' then ''是'' else ''否'' end ')
,'a.isstock','case when a.isstock=''1'' then ''有货'' else ''无货'' end ')
,'a.prodprice',' round(a.ProdPrice,2)'
)
,
',concat(''<a href=shell.php?objname=NOR_ProdSetSetSH&objid='',a.objid,''>修改</a>'') as `修改`'
,',concat(''<a href=shell.php?objname=NOR_ProdSetDel&objid='',a.objid,''&parentid='',''$HTTP[parentid]'',''>删除</a>'') as `删除`') `Result`;
/*5表*/
select @fromsql:=' from gwpuser_fileinfo a ';
/*6条件*/
select @wheresql:=case when '$HTTP[FieldName]'<>'' then ' where a.filetype=''G''
and a.objid<>1 and a.parentid=''$HTTP[parentid]''
and ($HTTP[FieldName] like ''$HTTP[key]%''
or ''$HTTP[key]''='''')'
else 'where a.filetype=''G''
and a.objid<>1 and a.parentid=''$HTTP[parentid]''' end
;
/*7排序*/
select @ordersql:=' order by a.objorder';
/*8总条数*/
select @count:=count(*),case when count(*)='0' then '没有记录' else concat('记录数[ ',count(*),' ]条') end `rows`
from gwpuser_fileinfo a
where a.filetype='G'
and a.objid<>1 and a.parentid='$HTTP[parentid]'
and ( instr('$HTTP[FieldName]','$HTTP[key]')>0 or '$HTTP[key]'='')
;
/*9总语句*/
select @sql:=concat('select @count `PAG_NUM`,',ifnull(@fieldsql,''),ifnull(@fromsql,''),ifnull(@wheresql,''),'$HTTP[saf_dgorder]',' limit 0,200;') `Result`;
/*10*/
PREPARE sqlstr FROM @sql;
/*11*/
EXECUTE sqlstr ;