求高人帮修改下SQL查询语句……

[犇犇犇程序猿]

具体就是对一个表（连接后的）的不同条件生成的查询结果count(),然后比较count的大小返回较小值……
貌似是 min(count(a.*),count(b.*)) 有错误。

select min(count(a.*),count(b.*))
from GpsReceRealTime left join GpsRegionType
on convert(varchar,GpsRegionType.RegionID)=(right(GpsReceRealTime.alarmInfo,len(GpsReceRealTime.alarmInfo)-charindex('\',GpsReceRealTime.alarmInfo))) left join GisCar
on GpsReceRealTime.telephone=GisCar.SimNum as a,
GpsReceRealTime left join GpsRegionType
on convert(varchar,GpsRegionType.RegionID)=(right(GpsReceRealTime.alarmInfo,len(GpsReceRealTime.alarmInfo)-charindex('\',GpsReceRealTime.alarmInfo))) left join GisCar
on GpsReceRealTime.telephone=GisCar.SimNum as b
where
a.GpsRegionType.RegionID='1'--开始区域
and a.GisCar.CarNum='1-047'--车牌
and a.GpsReceRealTime.recetime >=''--开始时间
and a.GpsReceRealTime.recetime <= ''--到时间
and (a.GpsReceRealTime.carStatu & 0x00000400)<>'0')

and
b.RegionID='3'--终点区域
and b.GisCar.CarNum='1-047'--车牌
and b.GpsReceRealTime.recetime >=''--开始时间
and b.GpsReceRealTime.recetime <= ''--到时间
and (b.GpsReceRealTime.carStatu & 0x00000400)<>'0')

[-晴天]

只能说,错误很多...

[--小F--]

问题太多了
本来min函数里面就只能有一个值,怎么出现了两个?

[-晴天]

min函数不是直接比较两个数,而是比较列中最小的.
连接后的count(a.*)count(b.*)有区别吗?
你的连接搞得太复杂了,对查询很不利的.

[playwarcraft]

min(count(a.*),count(b.*)) ==> SQL 沒有直接這麼用

要用 case when count(a.*)>=count(b.*) then count(b.*) else count(a.*) end

[tim_spac]

select min(cnt)
from (
    select count(*) as cnt
    from GpsReceRealTime 
    left join GpsRegionType 
        on convert(varchar,GpsRegionType.RegionID)=(right(GpsReceRealTime.alarmInfo,len(GpsReceRealTime.alarmInfo)-charindex('\',GpsReceRealTime.alarmInfo))) 
    left join GisCar on GpsReceRealTime.telephone=GisCar.SimNum 
    where GpsRegionType.RegionID='1'--开始区域 
    and GisCar.CarNum='1-047'--车牌 
    and GpsReceRealTime.recetime >=''--开始时间 
    and GpsReceRealTime.recetime <= ''--到时间 
    and (GpsReceRealTime.carStatu & 0x00000400) <>'0') 

    union 

    select count(*) as cnt
    from GpsReceRealTime 
    left join GpsRegionType 
        on convert(varchar,GpsRegionType.RegionID)=(right(GpsReceRealTime.alarmInfo,len(GpsReceRealTime.alarmInfo)-charindex('\',GpsReceRealTime.alarmInfo))) 
    left join GisCar on GpsReceRealTime.telephone=GisCar.SimNum  as b 
    where RegionID='3'--终点区域 
    and GisCar.CarNum='1-047'--车牌 
    and GpsReceRealTime.recetime >=''--开始时间 
    and GpsReceRealTime.recetime <= ''--到时间 
    and (GpsReceRealTime.carStatu & 0x00000400) <>'0')
) as a

[feixianxxx]

重写吧。。。

[flairsky]

参考sql白皮书，语法错误先解决

[JonasFeng]

先修改了下楼主的语法错误和逻辑错误：

select min(a.cnt)
from (
    select count(1) as cnt
    from GpsReceRealTime GRR
    left join GpsRegionType GRT
        on convert(varchar,GRR.RegionID) = 
          (right(GRT.alarmInfo,len(GRR.alarmInfo) - charindex('\',GRR.alarmInfo))) 
    left join GisCar GC on GRR.telephone = GC.SimNum 
    where GRT.RegionID='1'--开始区域 
    and GC.CarNum='1-047'--车牌 
    and GRR.recetime >=''--开始时间 
    and GRR.recetime <= ''--到时间 
    and (GRR.carStatu & 0x00000400) <>'0' 

    union all 

    select count(1) as cnt 
    from GpsReceRealTime GRR 
    left join GpsRegionType  GRT 
        on convert(varchar,GRT.RegionID)=
           (right(GRR.alarmInfo,len(GRR.alarmInfo)-charindex('\',GRR.alarmInfo))) 
    left join GisCar GC  on GRR.telephone = GC.SimNum  
    where GRT.RegionID='3'--终点区域 
    and GC.CarNum='1-047'--车牌 
    and GRR.recetime >=''--开始时间 
    and GRR.recetime <= ''--到时间 
    and (GRR.carStatu & 0x00000400) <> '0')
  as a

另外，为了提高效率，楼主可以把下面这句写成视图。

    select × 
    from GpsReceRealTime GRR 
    left join GpsRegionType  GRT 
        on convert(varchar,GRT.RegionID)=
           (right(GRR.alarmInfo,len(GRR.alarmInfo)-charindex('\',GRR.alarmInfo))) 
    left join GisCar GC  on GRR.telephone = GC.SimNum  

[ChinaJiaBing]

贴数据与结果...

[犇犇犇程序猿]

不好意思啊，大家 这个是我朋友用我号发的贴，现在他辞职了。。。 这个贴我立刻结贴！