sql 查询问题

Question

我想查询时间是在某个时间段内的数据，但是为什么查询出来的数据都不是在我指定的时间段以内呢？
sql代码：
select * from (  
select '排障' type, usertruename workername,bill.* from 
(select recworkerID workerid ,recBeginTime begintime,recEndTime endtime,
DATEDIFF(n,recBeginTime,recEndTime) clsj,recstate state, b.bilID,bilName,
bilNumber,bilCusCompany,bilCusName,bilCusPhone,bilCusAdd,bilComAdd,bilComName,
bilComPhone,bilComEmail,bilMgrAdd,bilMgrName,bilMgrPhone,bilMgrEmail,bilSetName,
productId,bilProductName,bilSN,bilIP,bilADSL,'排障' bilVisit,bilTraffic,bilFailure,
bilCL_zxxltd,bilCL_sbcssz,bilCL_qssb,bilCL_gdaz,bilCL_cscg,bilCL_ybsj,bilCL_hdsld,
bilCL_qt,bilCusSignFor,bilQianMingDX,bilUserId,bilQianMingXH,bilHuiFangRen,
bilChecker,bilHuiFangQK,bilCheckQK,bilDateTime,bilCheckTime,bilState,bilUserName,
b.recID,bilHuiFangRenID,bilCheckerID,logoutId,bilisadsl from repairrecord rr join 
bill b on rr.recbillid = b.bilid) bill join users on bill.workerid = users.userid   
union all  select '新装' type, usertruename workername,bill.* from (
select bilQianMingid workerid ,bilBeginTime begintime,bilEndTime endtime,DATEDIFF
(n,bilBeginTime,bilEndTime) clsj,bilstate state, bilID,bilName,bilNumber,
bilCusCompany,bilCusName,bilCusPhone,bilCusAdd,bilComAdd,bilComName,bilComPhone,
bilComEmail,bilMgrAdd,bilMgrName,bilMgrPhone,bilMgrEmail,bilSetName,productId,
bilProductName,bilSN,bilIP,bilADSL,bilVisit,bilTraffic,bilFailure,bilCL_zxxltd,
bilCL_sbcssz,bilCL_qssb,bilCL_gdaz,bilCL_cscg,bilCL_ybsj,bilCL_hdsld,bilCL_qt,
bilCusSignFor,bilQianMingDX,bilUserId,bilQianMingXH,bilHuiFangRen,bilChecker,
bilHuiFangQK,bilCheckQK,bilDateTime,bilCheckTime,bilState,bilUserName,recID,
bilHuiFangRenID,bilCheckerID,logoutId,bilisadsl from bill) bill left join users 
on bill.workerid = users.userid  ) report  where  1=1 and bilvisit = '协助新装'or 
bilvisit='自带新装' and BeginTime between '2013-04-01' and '2013-04-18' and EndTime 
between '2013-04-01' and '2013-04-18' order by begintime desc   
SQL

Answer 1

没有查出相应的数据，lz可以检查一下语句的逻辑是否符合需求。
帮整理格式，参考SELECT  *
FROM    ( SELECT    '排障' type ,
                    usertruename workername ,
                    bill.*
          FROM      ( SELECT    recworkerID workerid ,
                                recBeginTime begintime ,
                                recEndTime endtime ,
                                DATEDIFF(n, recBeginTime, recEndTime) clsj ,
                                recstate state ,
                                b.bilID ,
                                bilName ,
                                bilNumber ,
                                bilCusCompany ,
                                bilCusName ,
                                bilCusPhone ,
                                bilCusAdd ,
                                bilComAdd ,
                                bilComName ,
                                bilComPhone ,
                                bilComEmail ,
                                bilMgrAdd ,
                                bilMgrName ,
                                bilMgrPhone ,
                                bilMgrEmail ,
                                bilSetName ,
                                productId ,
                                bilProductName ,
                                bilSN ,
                                bilIP ,
                                bilADSL ,
                                '排障' bilVisit ,
                                bilTraffic ,
                                bilFailure ,
                                bilCL_zxxltd ,
                                bilCL_sbcssz ,
                                bilCL_qssb ,
                                bilCL_gdaz ,
                                bilCL_cscg ,
                                bilCL_ybsj ,
                                bilCL_hdsld ,
                                bilCL_qt ,
                                bilCusSignFor ,
                                bilQianMingDX ,
                                bilUserId ,
                                bilQianMingXH ,
                                bilHuiFangRen ,
                                bilChecker ,
                                bilHuiFangQK ,
                                bilCheckQK ,
                                bilDateTime ,
                                bilCheckTime ,
                                bilState ,
                                bilUserName ,
                                b.recID ,
                                bilHuiFangRenID ,
                                bilCheckerID ,
                                logoutId ,
                                bilisadsl
                      FROM      repairrecord rr
                                JOIN bill b ON rr.recbillid = b.bilid
                    ) bill
                    JOIN users ON bill.workerid = users.userid
          UNION ALL
          SELECT    '新装' type ,
                    usertruename workername ,
                    bill.*
          FROM      ( SELECT    bilQianMingid workerid ,
                                bilBeginTime begintime ,
                                bilEndTime endtime ,
                                DATEDIFF(n, bilBeginTime, bilEndTime) clsj ,
                                bilstate state ,
                                bilID ,
                                bilName ,
                                bilNumber ,
                                bilCusCompany ,
                                bilCusName ,
                                bilCusPhone ,
                                bilCusAdd ,
                                bilComAdd ,
                                bilComName ,
                                bilComPhone ,
                                bilComEmail ,
                                bilMgrAdd ,
                                bilMgrName ,
                                bilMgrPhone ,
                                bilMgrEmail ,
                                bilSetName ,
                                productId ,
                                bilProductName ,
                                bilSN ,
                                bilIP ,
                                bilADSL ,
                                bilVisit ,
                                bilTraffic ,
                                bilFailure ,
                                bilCL_zxxltd ,
                                bilCL_sbcssz ,
                                bilCL_qssb ,
                                bilCL_gdaz ,
                                bilCL_cscg ,
                                bilCL_ybsj ,
                                bilCL_hdsld ,
                                bilCL_qt ,
                                bilCusSignFor ,
                                bilQianMingDX ,
                                bilUserId ,
                                bilQianMingXH ,
                                bilHuiFangRen ,
                                bilChecker ,
                                bilHuiFangQK ,
                                bilCheckQK ,
                                bilDateTime ,
                                bilCheckTime ,
                                bilState ,
                                bilUserName ,
                                recID ,
                                bilHuiFangRenID ,
                                bilCheckerID ,
                                logoutId ,
                                bilisadsl
                      FROM      bill
                    ) bill
                    LEFT JOIN users ON bill.workerid = users.userid
        ) report
WHERE   1 = 1
        AND bilvisit = '协助新装'
        OR bilvisit = '自带新装'
        AND BeginTime BETWEEN '2013-04-01' AND '2013-04-18'
        AND EndTime BETWEEN '2013-04-01' AND '2013-04-18'
ORDER BY begintime DESC   

Answer 2

你的 or 用的有问题。
SELECT  *
FROM    ( SELECT    '排障' type ,
                    usertruename workername ,
                    bill.*
          FROM      ( SELECT    recworkerID workerid ,
                                recBeginTime begintime ,
                                recEndTime endtime ,
                                DATEDIFF(n, recBeginTime, recEndTime) clsj ,
                                recstate state ,
                                b.bilID ,
                                bilName ,
                                bilNumber ,
                                bilCusCompany ,
                                bilCusName ,
                                bilCusPhone ,
                                bilCusAdd ,
                                bilComAdd ,
                                bilComName ,
                                bilComPhone ,
                                bilComEmail ,
                                bilMgrAdd ,
                                bilMgrName ,
                                bilMgrPhone ,
                                bilMgrEmail ,
                                bilSetName ,
                                productId ,
                                bilProductName ,
                                bilSN ,
                                bilIP ,
                                bilADSL ,
                                '排障' bilVisit ,
                                bilTraffic ,
                                bilFailure ,
                                bilCL_zxxltd ,
                                bilCL_sbcssz ,
                                bilCL_qssb ,
                                bilCL_gdaz ,
                                bilCL_cscg ,
                                bilCL_ybsj ,
                                bilCL_hdsld ,
                                bilCL_qt ,
                                bilCusSignFor ,
                                bilQianMingDX ,
                                bilUserId ,
                                bilQianMingXH ,
                                bilHuiFangRen ,
                                bilChecker ,
                                bilHuiFangQK ,
                                bilCheckQK ,
                                bilDateTime ,
                                bilCheckTime ,
                                bilState ,
                                bilUserName ,
                                b.recID ,
                                bilHuiFangRenID ,
                                bilCheckerID ,
                                logoutId ,
                                bilisadsl
                      FROM      repairrecord rr
                                JOIN bill b ON rr.recbillid = b.bilid
                    ) bill
                    JOIN users ON bill.workerid = users.userid
          UNION ALL
          SELECT    '新装' type ,
                    usertruename workername ,
                    bill.*
          FROM      ( SELECT    bilQianMingid workerid ,
                                bilBeginTime begintime ,
                                bilEndTime endtime ,
                                DATEDIFF(n, bilBeginTime, bilEndTime) clsj ,
                                bilstate state ,
                                bilID ,
                                bilName ,
                                bilNumber ,
                                bilCusCompany ,
                                bilCusName ,
                                bilCusPhone ,
                                bilCusAdd ,
                                bilComAdd ,
                                bilComName ,
                                bilComPhone ,
                                bilComEmail ,
                                bilMgrAdd ,
                                bilMgrName ,
                                bilMgrPhone ,
                                bilMgrEmail ,
                                bilSetName ,
                                productId ,
                                bilProductName ,
                                bilSN ,
                                bilIP ,
                                bilADSL ,
                                bilVisit ,
                                bilTraffic ,
                                bilFailure ,
                                bilCL_zxxltd ,
                                bilCL_sbcssz ,
                                bilCL_qssb ,
                                bilCL_gdaz ,
                                bilCL_cscg ,
                                bilCL_ybsj ,
                                bilCL_hdsld ,
                                bilCL_qt ,
                                bilCusSignFor ,
                                bilQianMingDX ,
                                bilUserId ,
                                bilQianMingXH ,
                                bilHuiFangRen ,
                                bilChecker ,
                                bilHuiFangQK ,
                                bilCheckQK ,
                                bilDateTime ,
                                bilCheckTime ,
                                bilState ,
                                bilUserName ,
                                recID ,
                                bilHuiFangRenID ,
                                bilCheckerID ,
                                logoutId ,
                                bilisadsl
                      FROM      bill
                    ) bill
                    LEFT JOIN users ON bill.workerid = users.userid
        ) report
WHERE   1 = 1
        AND (bilvisit = '协助新装' OR bilvisit = '自带新装')
        AND BeginTime BETWEEN '2013-04-01' AND '2013-04-18'
        AND EndTime BETWEEN '2013-04-01' AND '2013-04-18'
ORDER BY begintime DESC   

Answer 3

问题出在你后面的where条件里面。
WHERE   1 = 1
        AND bilvisit = '协助新装'
        OR bilvisit = '自带新装' --这个or的问题。
        AND BeginTime BETWEEN '2013-04-01' AND '2013-04-18'
        AND EndTime BETWEEN '2013-04-01' AND '2013-04-18'--改成
WHERE   1 = 1
        AND (bilvisit = '协助新装' OR bilvisit = '自带新装')
        AND BeginTime BETWEEN '2013-04-01' AND '2013-04-18'
        AND EndTime BETWEEN '2013-04-01' AND '2013-04-18'