第一句SQL---------------如下
select LinkID,count(*) as 出现次数 from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and C.Speed>20 and C.Speed<=35 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID第二句SQL----------------如下
select LinkID,count(*) as 总现次数 from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID第一句查出来的东西是下面这个样子:
LindID 出现次数
16239 4
------------------------------------
第二句查出来的东西是下面这个样子:
LindID 总现次数
16239 9现在我想把这两个SQL联起来,弄成下面这个表,请问如何联?唉,基础不牢,请帮看看:
LindID 出现次数 总现次数
16239 4 9
select LinkID,count(*) as 出现次数 from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and C.Speed>20 and C.Speed<=35 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID第二句SQL----------------如下
select LinkID,count(*) as 总现次数 from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID第一句查出来的东西是下面这个样子:
LindID 出现次数
16239 4
------------------------------------
第二句查出来的东西是下面这个样子:
LindID 总现次数
16239 9现在我想把这两个SQL联起来,弄成下面这个表,请问如何联?唉,基础不牢,请帮看看:
LindID 出现次数 总现次数
16239 4 9
sum(case when C.Speed>20 and C.Speed<=35 then 1 else 0 end) as 出现次数,
count(*) as 总现次数
from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and
C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID
??
select LinkID,sum(case when c.speed between 20 and 35 then 1 esle 0 end) as 出现次数 ,count(1) as 总现次数 from
(select A.LinkID,A.Speed,A.time,B.Class from T_yongdu A right join Linkthree B
on A.LinkID=B.LinkID) C
where C.Class=1 and C.LinkID=16239 and
C.time between '2010-07-26 07:00:00.000' and '2010-07-26 09:00:00.000'
Group by C.LinkID