我这么加,可就是查不出结果,哪里写错了吗? select ww.*,qq.renshu from (select a.*,b.mingcheng,c.mingma,d.mingcheng as mch,e.mingcheng as pl,f.DFMingCheng as zj from Tbl_checkIn a,tbl_diqu b,tbl_ShouPiao c,tbl_YanPiaoDian d,tbl_PiaoLei e,Tbl_SFZ f where a.diqu=b.bianhao and a.KaHao=c.kahao and a.CheckInNo=d.bianhao and left(a.zhengjian,6)=f.SFZBianHao and a.piaolei=e.bianhao and mingma>='11' and mingma<='22' and mingma>='28' and mingma<='32' and mingma>='57' and mingma<='75' and mingma>='82' and mingma<='91' and convert(char(10),CheckInTime,120) >= '20120615' and convert(char(10),CheckInTime,120) <= '20120625') ww join (select zhengjian,sum(renshu) as renshu from tbl_checkin group by zhengjian) qq on ww.zhengjian =qq.zhengjian
--试下select ww.*, qq.renshu from (select a.*, b.mingcheng, c.mingma, d.mingcheng as mch, e.mingcheng as pl, f.DFMingCheng as zj from Tbl_checkIn a, tbl_diqu b, tbl_ShouPiao c, tbl_YanPiaoDian d, tbl_PiaoLei e, Tbl_SFZ f where a.diqu = b.bianhao and a.KaHao = c.kahao and a.CheckInNo = d.bianhao and left(a.zhengjian, 6) = f.SFZBianHao and a.piaolei = e.bianhao and ( (and mingma >= '11' and mingma <= '22') or (and mingma >= '28' and mingma <= '32') or (and mingma >= '57' and mingma <= '75') or (and mingma >= '82' and mingma <= '91')) and convert(char(10), CheckInTime, 120) >= '20120615' and convert(char(10), CheckInTime, 120) <= '20120625') ww join (select zhengjian, sum(renshu) as renshu from tbl_checkin group by zhengjian) qq on ww.zhengjian = qq.zhengjian
--这种写法测试通过 select * from sys_code where 1=1 and ( ( id>'106' and id<'108') or ( id>'112' and id<'14') ) and 1=1
你就按这种方式来组织你的多个区间条件where 1=1 and ( ( id>'106' and id<'108') or ( id>'112' and id<'14') ) and 1=1我给你写的是方法,我也没环境测试,你就直接运行?
((mingma>='11' and mingma<='22') or (mingma>='28' and mingma<='32') or (mingma>='57' and mingma<='75') or (mingma>='82' and mingma<='91'))
--..... SELECT * FROM tb WHERE ( CASE WHEN mingma>='11' and f_ConsumerName<='22' THEN 1 ELSE 0 END + CASE WHEN mingma>='28' and mingma<='32' THEN 1 ELSE 0 END + CASE WHEN mingma>='57' and mingma<='75' THEN 1 ELSE 0 END + CASE WHEN mingma>='82' and mingma<='91' THEN 1 ELSE 0 END + ) >= 1--组合条件搜索,当上述4种条件任意一种或多种同时满足
试过你这个条件返回的值吗and mingma>='11' and mingma<='22' and mingma>='28' and mingma<='32' and mingma>='57' and mingma<='75' and mingma>='82' and mingma<='91' 永远不可能成立
你这管用的话,在加and 或or肯定也管用啊
我这么加,可就是查不出结果,哪里写错了吗?
select ww.*,qq.renshu from
(select a.*,b.mingcheng,c.mingma,d.mingcheng as mch,e.mingcheng as pl,f.DFMingCheng as zj
from Tbl_checkIn a,tbl_diqu b,tbl_ShouPiao c,tbl_YanPiaoDian d,tbl_PiaoLei e,Tbl_SFZ f
where a.diqu=b.bianhao and a.KaHao=c.kahao
and a.CheckInNo=d.bianhao
and left(a.zhengjian,6)=f.SFZBianHao
and a.piaolei=e.bianhao
and mingma>='11'
and mingma<='22'
and mingma>='28'
and mingma<='32'
and mingma>='57'
and mingma<='75'
and mingma>='82'
and mingma<='91'
and convert(char(10),CheckInTime,120) >= '20120615'
and convert(char(10),CheckInTime,120) <= '20120625') ww
join (select zhengjian,sum(renshu) as renshu from tbl_checkin group by zhengjian) qq
on ww.zhengjian =qq.zhengjian
--试下select ww.*, qq.renshu
from (select a.*,
b.mingcheng,
c.mingma,
d.mingcheng as mch,
e.mingcheng as pl,
f.DFMingCheng as zj
from Tbl_checkIn a,
tbl_diqu b,
tbl_ShouPiao c,
tbl_YanPiaoDian d,
tbl_PiaoLei e,
Tbl_SFZ f
where a.diqu = b.bianhao
and a.KaHao = c.kahao
and a.CheckInNo = d.bianhao and
left(a.zhengjian, 6) = f.SFZBianHao and a.piaolei = e.bianhao and ( (and mingma >= '11' and mingma <= '22') or (and mingma >= '28' and mingma <= '32') or (and mingma >= '57' and mingma <= '75') or (and mingma >= '82' and mingma <= '91')) and convert(char(10), CheckInTime, 120) >= '20120615' and convert(char(10), CheckInTime, 120) <= '20120625') ww
join (select zhengjian, sum(renshu) as renshu
from tbl_checkin
group by zhengjian) qq
on ww.zhengjian = qq.zhengjian
select * from sys_code where 1=1 and ( ( id>'106' and id<'108') or ( id>'112' and id<'14') ) and 1=1
在关键字 'and' 附近有语法错误。
消息 170,级别 15,状态 1,第 20 行
第 20 行: 'qq' 附近有语法错误。
SELECT *
FROM tb
WHERE (
CASE WHEN mingma>='11' and f_ConsumerName<='22' THEN 1 ELSE 0 END +
CASE WHEN mingma>='28' and mingma<='32' THEN 1 ELSE 0 END +
CASE WHEN mingma>='57' and mingma<='75' THEN 1 ELSE 0 END +
CASE WHEN mingma>='82' and mingma<='91' THEN 1 ELSE 0 END +
) >= 1--组合条件搜索,当上述4种条件任意一种或多种同时满足
and mingma<='22'
and mingma>='28'
and mingma<='32'
and mingma>='57'
and mingma<='75'
and mingma>='82'
and mingma<='91' 永远不可能成立