后台判断 当全部时 直接SQL 当非全部时 SQL+“and a.GongSiNo = '"&request("gongsi")&"'” SQL=select a.gongsimc,a.card_kehu_no as kehu_no, a.card_no,a.card_kehu_mc as kehu_mc, a.card_kehu_shouji as kehu_dh, a.che_no, a.card_kind, a.card_enddate, lastdate, datediff(day,isnull(lastdate,getdate()-365),getdate()) days from ( select zhifu_card_no, max(lastdate) as lastdate from ( select zhifu_card_no , xche_jsrq lastdate from work_pz_sj union select zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj union select card_no as zhifu_card_no, xche_jsrq lastdate from work_pz_sj union select card_no as zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select card_no as zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj ) aa group by zhifu_card_no ) b left join card a on b.zhifu_card_no=a.card_no where a.card_no is not null and lastdate>='"&request("txtBeginDate")&"' and lastdate<='"&request("txtEndDate")&"' and datediff(day,isnull(lastdate,getdate()-365),getdate())>='"&request("txtDay")&"' and datediff(day,isnull(lastdate,getdate()-365),getdate())<='"&request("txtDay2")&"' ORDER BY lastdate
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = ' select a.gongsimc,a.card_kehu_no as kehu_no, a.card_no,a.card_kehu_mc as kehu_mc, a.card_kehu_shouji as kehu_dh, a.che_no, a.card_kind, a.card_enddate, lastdate, datediff(day,isnull(lastdate,getdate()-365),getdate()) days from ( select zhifu_card_no, max(lastdate) as lastdate from ( select zhifu_card_no , xche_jsrq lastdate from work_pz_sj union select zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj union select card_no as zhifu_card_no, xche_jsrq lastdate from work_pz_sj union select card_no as zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select card_no as zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj ) aa group by zhifu_card_no ) b left join card a on b.zhifu_card_no=a.card_no where a.card_no is not null 'if 所属公司参数 <> '全部' sql = sql + '查询条件' end if
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = ' select a.gongsimc,a.card_kehu_no as kehu_no, a.card_no,a.card_kehu_mc as kehu_mc, a.card_kehu_shouji as kehu_dh, a.che_no, a.card_kind, a.card_enddate, lastdate, datediff(day,isnull(lastdate,getdate()-365),getdate()) days from ( select zhifu_card_no, max(lastdate) as lastdate from ( select zhifu_card_no , xche_jsrq lastdate from work_pz_sj union select zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj union select card_no as zhifu_card_no, xche_jsrq lastdate from work_pz_sj union select card_no as zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select card_no as zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj ) aa group by zhifu_card_no ) b left join card a on b.zhifu_card_no=a.card_no where a.card_no is not null 'if 所属公司参数 <> '全部' sql = sql + '查询条件' end if 是这个意思吗
应该是 这个意思吧sql="1=1" if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'" if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'" if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'" if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'" if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'" if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询 if request("条件N")<>"" then sql=sql&" and 查询字段N='"&request("条件N")&"'"这样的就把SQL的条件语句给拼接了,然后再看以下: set rs=server.createobject("adodb.recordset") sql="select * from 表名 where "&sql&""搜索 rs.open sql,conn,1,1
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = ' select a.gongsimc,a.card_kehu_no as kehu_no, a.card_no,a.card_kehu_mc as kehu_mc, a.card_kehu_shouji as kehu_dh, a.che_no, a.card_kind, a.card_enddate, lastdate, datediff(day,isnull(lastdate,getdate()-365),getdate()) days from ( select zhifu_card_no, max(lastdate) as lastdate from ( select zhifu_card_no , xche_jsrq lastdate from work_pz_sj union select zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj union select card_no as zhifu_card_no, xche_jsrq lastdate from work_pz_sj union select card_no as zhifu_card_no, xc_rq lastdate from work_xiche_pz_sj union select card_no as zhifu_card_no, xiao_rq lastdate from xiaosh_pz_sj ) aa group by zhifu_card_no ) b left join card a on b.zhifu_card_no=a.card_no where a.card_no is not null 'if 所属公司参数 <> '全部' sql = sql + '查询条件' end if 是这个意思吗 嗯 对的
可以考虑把参数默认值为null,然后代码都写上: select * from tb where a=isnull(@a,a) and b=isnull(@b,b)这样,如果前端没有选,就传入null值,因为where a=a恒为真,所以不会进行筛选操作
是这样,不过稍微改一下:sql="1=1" if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'" if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'" if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'" if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'" if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'" if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询 if request("所属公司条件")<>"" and request("所属公司条件")<>"全部" then sql=sql&" and 查询字段N='"&request("条件N")&"'"
是这样,不过稍微改一下:sql="1=1" if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'" if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'" if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'" if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'" if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'" if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询 if request("所属公司条件")<>"" and request("所属公司条件")<>"全部" then sql=sql&" and 查询字段N='"&request("条件N")&"'"还是不好使啊 可以加我QQ吗 566971
DECLARE @a INT SET @a =NULL SELECT * FROM TB WHERE a=ISNULL(@a,a) 比如前端传入@a这个参数,给默认值null,下面的查询如果@a没有传值,就默认为null,结果等于select * from tb,如果有传入值,就会走select * from tb where a=@a
当非全部时 SQL+“and a.GongSiNo = '"&request("gongsi")&"'”
SQL=select a.gongsimc,a.card_kehu_no as kehu_no,
a.card_no,a.card_kehu_mc as kehu_mc,
a.card_kehu_shouji as kehu_dh,
a.che_no,
a.card_kind,
a.card_enddate,
lastdate,
datediff(day,isnull(lastdate,getdate()-365),getdate()) days
from (
select zhifu_card_no,
max(lastdate) as lastdate
from (
select zhifu_card_no ,
xche_jsrq lastdate
from work_pz_sj
union
select zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
union
select card_no as zhifu_card_no,
xche_jsrq lastdate
from work_pz_sj
union
select card_no as zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select card_no as zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
) aa
group by zhifu_card_no
) b
left join card a
on b.zhifu_card_no=a.card_no
where a.card_no is not null
and lastdate>='"&request("txtBeginDate")&"'
and lastdate<='"&request("txtEndDate")&"'
and datediff(day,isnull(lastdate,getdate()-365),getdate())>='"&request("txtDay")&"'
and datediff(day,isnull(lastdate,getdate()-365),getdate())<='"&request("txtDay2")&"'
ORDER BY lastdate
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = '
select a.gongsimc,a.card_kehu_no as kehu_no,
a.card_no,a.card_kehu_mc as kehu_mc,
a.card_kehu_shouji as kehu_dh,
a.che_no,
a.card_kind,
a.card_enddate,
lastdate,
datediff(day,isnull(lastdate,getdate()-365),getdate()) days
from (
select zhifu_card_no,
max(lastdate) as lastdate
from (
select zhifu_card_no ,
xche_jsrq lastdate
from work_pz_sj
union
select zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
union
select card_no as zhifu_card_no,
xche_jsrq lastdate
from work_pz_sj
union
select card_no as zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select card_no as zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
) aa
group by zhifu_card_no
) b
left join card a
on b.zhifu_card_no=a.card_no
where a.card_no is not null
'if 所属公司参数 <> '全部'
sql = sql + '查询条件'
end if
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = '
select a.gongsimc,a.card_kehu_no as kehu_no,
a.card_no,a.card_kehu_mc as kehu_mc,
a.card_kehu_shouji as kehu_dh,
a.che_no,
a.card_kind,
a.card_enddate,
lastdate,
datediff(day,isnull(lastdate,getdate()-365),getdate()) days
from (
select zhifu_card_no,
max(lastdate) as lastdate
from (
select zhifu_card_no ,
xche_jsrq lastdate
from work_pz_sj
union
select zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
union
select card_no as zhifu_card_no,
xche_jsrq lastdate
from work_pz_sj
union
select card_no as zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select card_no as zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
) aa
group by zhifu_card_no
) b
left join card a
on b.zhifu_card_no=a.card_no
where a.card_no is not null
'if 所属公司参数 <> '全部'
sql = sql + '查询条件'
end if
是这个意思吗
if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'"
if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'"
if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'"
if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'"
if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'"
if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询
if request("条件N")<>"" then sql=sql&" and 查询字段N='"&request("条件N")&"'"这样的就把SQL的条件语句给拼接了,然后再看以下:
set rs=server.createobject("adodb.recordset")
sql="select * from 表名 where "&sql&""搜索
rs.open sql,conn,1,1
请问如何拼接呢就是当用户通过界面,选择完查询条件后,判断一下,比如:所属公司 参数,sql = '
select a.gongsimc,a.card_kehu_no as kehu_no,
a.card_no,a.card_kehu_mc as kehu_mc,
a.card_kehu_shouji as kehu_dh,
a.che_no,
a.card_kind,
a.card_enddate,
lastdate,
datediff(day,isnull(lastdate,getdate()-365),getdate()) days
from (
select zhifu_card_no,
max(lastdate) as lastdate
from (
select zhifu_card_no ,
xche_jsrq lastdate
from work_pz_sj
union
select zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
union
select card_no as zhifu_card_no,
xche_jsrq lastdate
from work_pz_sj
union
select card_no as zhifu_card_no,
xc_rq lastdate
from work_xiche_pz_sj
union
select card_no as zhifu_card_no,
xiao_rq lastdate
from xiaosh_pz_sj
) aa
group by zhifu_card_no
) b
left join card a
on b.zhifu_card_no=a.card_no
where a.card_no is not null
'if 所属公司参数 <> '全部'
sql = sql + '查询条件'
end if
是这个意思吗
嗯 对的
select * from tb
where a=isnull(@a,a) and b=isnull(@b,b)这样,如果前端没有选,就传入null值,因为where a=a恒为真,所以不会进行筛选操作
是这样,不过稍微改一下:sql="1=1"
if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'"
if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'"
if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'"
if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'"
if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'"
if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询
if request("所属公司条件")<>"" and request("所属公司条件")<>"全部" then sql=sql&" and 查询字段N='"&request("条件N")&"'"
是这样,不过稍微改一下:sql="1=1"
if request("条件1")<>"" then sql=sql&" and 查询字段1='"&request("条件1")&"'"
if request("条件2")<>"" then sql=sql&" and 查询字段2='"&request("条件2")&"'"
if request("条件3")<>"" then sql=sql&" and 查询字段3='"&request("条件3")&"'"
if request("条件4")<>"" then sql=sql&" and 查询字段4='"&request("条件4")&"'"
if request("条件5")<>"" then sql=sql&" and 查询字段5='"&request("条件5")&"'"
if request("条件6")<>"" then sql=sql&" and 查询字段6 like '%"&request("条件6")&"%'" '模糊查询
if request("所属公司条件")<>"" and request("所属公司条件")<>"全部" then sql=sql&" and 查询字段N='"&request("条件N")&"'"还是不好使啊 可以加我QQ吗 566971
SET @a =NULL SELECT * FROM TB WHERE a=ISNULL(@a,a)
比如前端传入@a这个参数,给默认值null,下面的查询如果@a没有传值,就默认为null,结果等于select * from tb,如果有传入值,就会走select * from tb where a=@a