请教一条sql语句

Question

--表结构
--车辆表
create table #bus
(
 id int identity(1,1),
 name nvarchar(50),--车辆名称
)
--发车日志
create table #log
(
  id int identity(1,1),
  name nvarchar(50),--车辆名称
  starttime datetime --发车时间
)
--测试数据
--车辆
insert into #bus values('车辆1')
insert into #bus values('车辆2')
insert into #bus values('车辆3')
insert into #bus values('车辆4')
--发车日志
insert into #log values('车辆1','2011-5-31 8:00:00')
insert into #log values('车辆1','2011-5-31 8:30:00')
insert into #log values('车辆2','2011-5-31 8:15:00')
insert into #log values('车辆2','2011-5-31 8:45:00')
insert into #log values('车辆1','2011-6-1 8:00:00')
insert into #log values('车辆3','2011-6-1 8:15:00')
insert into #log values('车辆3','2011-6-1 8:45:00')
insert into #log values('车辆3','2011-6-1 9:15:00'）
--查询逻辑:从外部传入的查询时间是从2011-5-31至2011-6-2,按日期分组统计每辆车每日的发车次数
--想要的结果
序号     日期        车牌号    发车次数
1      2011-5-31      车辆1       2
2      2011-5-31      车辆2       2
3      2011-5-31      车辆3       0
4      2011-5-31      车辆4       0
5      2011-6-1       车辆1       1
6      2011-6-1       车辆2       0
7      2011-6-1       车辆3       3
8      2011-6-1       车辆4       0
9      2011-6-2       车辆1       0
10     2011-6-2       车辆2       0
11     2011-6-2       车辆3       0
12     2011-6-2       车辆4       0

Answer 1

select convert(varchar(10),日期,120) as 日期,
       车牌号,count(*) as 发车次数 
from 
(select a.name,dateadd(dd,number,'2011-5-31')
from #bus a,master..spt_values 
where type='p' 
and dateadd(dd,number,'2011-5-31')<='2011-6-2')
b
left join #log c
on b.name=c.name
group by convert(varchar(10),日期,120),车牌号

Answer 2

create table #bus
(
 id int identity(1,1),
 name nvarchar(50),--车辆名称
)
--发车日志
create table #log
(
  id int identity(1,1),
  name nvarchar(50),--车辆名称
  starttime datetime --发车时间
)
--测试数据
--车辆
insert into #bus values('车辆1')
insert into #bus values('车辆2')
insert into #bus values('车辆3')
insert into #bus values('车辆4')
--发车日志
insert into #log values('车辆1','2011-5-31 8:00:00')
insert into #log values('车辆1','2011-5-31 8:30:00')
insert into #log values('车辆2','2011-5-31 8:15:00')
insert into #log values('车辆2','2011-5-31 8:45:00')
insert into #log values('车辆1','2011-6-1 8:00:00')
insert into #log values('车辆3','2011-6-1 8:15:00')
insert into #log values('车辆3','2011-6-1 8:45:00')
insert into #log values('车辆3','2011-6-1 9:15:00')
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-5-31'
set @dt2='2011-6-2'
;with c1 as(
select dateadd(d,number,@dt1)dt from master..spt_values where type='p' and dateadd(d,number,@dt1)<=@dt2
),c2 as(
select a.dt,b.name from c1 a,#bus b
),c3 as(
select a.dt,a.name,count(b.starttime)ct from c2 a left join #log b on a.dt=convert(varchar(10),b.starttime,120) and a.name=b.name
group by a.dt,a.name
)select row_number()over(order by dt,name)序号,dt 日期,name 车牌号,ct 发车次数 from c3
/*
序号                   日期                      车牌号                                                发车次数
-------------------- ----------------------- -------------------------------------------------- -----------
1                    2011-05-31 00:00:00.000 车辆1                                                2
2                    2011-05-31 00:00:00.000 车辆2                                                2
3                    2011-05-31 00:00:00.000 车辆3                                                0
4                    2011-05-31 00:00:00.000 车辆4                                                0
5                    2011-06-01 00:00:00.000 车辆1                                                1
6                    2011-06-01 00:00:00.000 车辆2                                                0
7                    2011-06-01 00:00:00.000 车辆3                                                3
8                    2011-06-01 00:00:00.000 车辆4                                                0
9                    2011-06-02 00:00:00.000 车辆1                                                0
10                   2011-06-02 00:00:00.000 车辆2                                                0
11                   2011-06-02 00:00:00.000 车辆3                                                0
12                   2011-06-02 00:00:00.000 车辆4                                                0
警告: 聚合或其他 SET 操作消除了空值。(12 行受影响)*/
go
drop table #bus,#log

Answer 3

create table #bus
(
 id int identity(1,1),
 name nvarchar(50),--车辆名称
)
--发车日志
create table #log
(
  id int identity(1,1),
  name nvarchar(50),--车辆名称
  starttime datetime --发车时间
)
--测试数据
--车辆
insert into #bus values('车辆1')
insert into #bus values('车辆2')
insert into #bus values('车辆3')
insert into #bus values('车辆4')
--发车日志
insert into #log values('车辆1','2011-5-31 8:00:00')
insert into #log values('车辆1','2011-5-31 8:30:00')
insert into #log values('车辆2','2011-5-31 8:15:00')
insert into #log values('车辆2','2011-5-31 8:45:00')
insert into #log values('车辆1','2011-6-1 8:00:00')
insert into #log values('车辆3','2011-6-1 8:15:00')
insert into #log values('车辆3','2011-6-1 8:45:00')
insert into #log values('车辆3','2011-6-1 9:15:00')declare @btime datetime,@etime datetime
select @btime='2011-5-31',@etime='2011-6-2'select DATEADD(DD,number,@btime),b.name,sum(case when c.starttime is null then 0 else 1 end)
from master..spt_values a inner join #bus b on 1=1
left join #log c on DATEDIFF(dd,DATEADD(DD,number,@btime),c.starttime)=0 and b.name=c.name
where a.type='p' and number<DATEDIFF(DD,@btime,@etime)+1
group by DATEADD(DD,number,@btime),b.name
order by 1,2/*
                        name                                               
----------------------- -------------------------------------------------- -----------
2011-05-31 00:00:00.000 车辆1                                                2
2011-05-31 00:00:00.000 车辆2                                                2
2011-05-31 00:00:00.000 车辆3                                                0
2011-05-31 00:00:00.000 车辆4                                                0
2011-06-01 00:00:00.000 车辆1                                                1
2011-06-01 00:00:00.000 车辆2                                                0
2011-06-01 00:00:00.000 车辆3                                                3
2011-06-01 00:00:00.000 车辆4                                                0
2011-06-02 00:00:00.000 车辆1                                                0
2011-06-02 00:00:00.000 车辆2                                                0
2011-06-02 00:00:00.000 车辆3                                                0
2011-06-02 00:00:00.000 车辆4                                                0(12 行受影响)

Answer 4

select time , name ,count(*) from
(
select convert(varchar(10),bus.datetime,120) as time,bus.name as name,log.id as id from bus left join log on bus.name = log.name 
) m
group by time,name
order by time,name

Answer 5

select convert(varchar(10),b.日期,120) as 日期,
       b.name as 车牌号,count(c.name) as 发车次数 
from 
(select a.name,dateadd(dd,number,'2011-5-31') as 日期 
from #bus a,master..spt_values 
where type='p' 
and dateadd(dd,number,'2011-5-31')<='2011-6-2')
b
left join 
#log c
on 
b.name=c.name and convert(varchar(10),b.日期,120)= convert(varchar(10),c.starttime,120)
group by convert(varchar(10),b.日期,120),b.name
order by 日期,车牌号

Answer 6

--表结构
--车辆表
create table #bus
(
 id int identity(1,1),
 name nvarchar(50),--车辆名称
)
--发车日志
create table #log
(
  id int identity(1,1),
  name nvarchar(50),--车辆名称
  starttime datetime --发车时间
)
--测试数据
--车辆
insert into #bus values('车辆1')
insert into #bus values('车辆2')
insert into #bus values('车辆3')
insert into #bus values('车辆4')
--发车日志
insert into #log values('车辆1','2011-5-31 8:00:00')
insert into #log values('车辆1','2011-5-31 8:30:00')
insert into #log values('车辆2','2011-5-31 8:15:00')
insert into #log values('车辆2','2011-5-31 8:45:00')
insert into #log values('车辆1','2011-6-1 8:00:00')
insert into #log values('车辆3','2011-6-1 8:15:00')
insert into #log values('车辆3','2011-6-1 8:45:00')
insert into #log values('车辆3','2011-6-1 9:15:00')
--查询逻辑:从外部传入的查询时间是从2011-5-31至2011-6-2,按日期分组统计每辆车每日的发车次数
--想要的结果select b.date,a.[name],count(c.[name]) as cnt
from #bus a cross join (select distinct convert(varchar(10),starttime,120) as date from #log) b
left join #log c on a.[name] = c.[name] and convert(varchar(10),starttime,120) = b.date
group by b.date,a.[name]
order by b.datedrop table #bus,#log
/**************************date       name                                               cnt
---------- -------------------------------------------------- -----------
2011-05-31 车辆1                                                2
2011-05-31 车辆2                                                2
2011-05-31 车辆3                                                0
2011-05-31 车辆4                                                0
2011-06-01 车辆1                                                1
2011-06-01 车辆2                                                0
2011-06-01 车辆3                                                3
2011-06-01 车辆4                                                0
警告: 聚合或其他 SET 操作消除了空值。(8 行受影响)

Answer 7

select
  序号=row_number()over(order by getdate()),
  a.name,
  convert(varchar(10),b.starttime,120),
  count(b.starttime) as 发车次数,
from
  ##bus a,#log b
where
  a.name=b.name
group by
  a.name,
  convert(varchar(10),b.starttime,120),

Answer 8

use tempdb;
/*
--表结构
--车辆表
create table #bus
(
 id int identity(1,1),
 name nvarchar(50),--车辆名称
)
--发车日志
create table #log
(
  id int identity(1,1),
  name nvarchar(50),--车辆名称
  starttime datetime --发车时间
)
--测试数据
--车辆
insert into #bus values('车辆1')
insert into #bus values('车辆2')
insert into #bus values('车辆3')
insert into #bus values('车辆4')
--发车日志
insert into #log values('车辆1','2011-5-31 8:00:00')
insert into #log values('车辆1','2011-5-31 8:30:00')
insert into #log values('车辆2','2011-5-31 8:15:00')
insert into #log values('车辆2','2011-5-31 8:45:00')
insert into #log values('车辆1','2011-6-1 8:00:00')
insert into #log values('车辆3','2011-6-1 8:15:00')
insert into #log values('车辆3','2011-6-1 8:45:00')
insert into #log values('车辆3','2011-6-1 9:15:00')
*/
--2011-5-31至2011-6-2,按日期分组统计每辆车每日的发车次数
select 
t3.[date] as [日期],
t1.[name] as [车牌号],
ISNULL(t5.num,0) as [发车次数]
from #bus as t1
cross join 
(
select distinct CAST(t2.starttime as date) as [date] 
from #log as t2 
where t2.starttime between '2011-5-31' and '2011-6-2'
) as t3
left join
(
select t4.name,CAST(t4.starttime as date) as [date],COUNT(CAST(t4.starttime as date)) as [num]
from #log as t4
group by t4.name,CAST(t4.starttime as date)
) as t5
on t3.date = t5.date and t1.name = t5.name;