insert into table_bus (bus_name, bus_seatcount) values ('bus01', 30); insert into table_bus (bus_name, bus_seatcount) values ('bus02', 20); insert into table_bus (bus_name, bus_seatcount) values ('bus03', 40); insert into table_bus (bus_name, bus_seatcount) values ('bus04', 35); insert into table_bus (bus_name, bus_seatcount) values ('bus05', 35); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-2'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-2'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-2'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-2'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-3'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,1,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,2,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,3,'2013-3-1'); insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(4,1,'2013-3-3');得出: 2013-03-01 -> 2013-03-03 bus_name 上座率 bus01 4.44% bus02 8.33% bus03 2.5% bus04 0.95% bus05 0.00%
select bus_name ,convert(varchar,convert(decimal(10,2),sum(ISNULL(b.num,0))*100.0/(a.bus_seatcount*DATEDIFF(d,'2013-03-01','2013-03-04'))))+'%' from table_bus a outer apply (select MAX(Ticket_SeatNo)num from table_ticket where a.Bus_Id=Ticket_Bus group by date)b group by bus_name,bus_seatcount order by bus_name /* bus_name (无列名) bus01 4.44% bus02 8.33% bus03 2.50% bus04 0.95% bus05 0.00% */
select bus.bus_name, cast(tempbus.total_num*100/bus.bus_seatcount as varchar(50))+'%' 上座率 from table_bus bus join ( select b.bus_name, SUM(b.num) as total_num from ( select t.bus_name, t.ticket_date, COUNT(t.bus_name) as num from ( select bus_name, ticket_date from table_bus a left join table_ticket b on a.bus_id = b.ticket_bus_id where ticket_date between '2013-2-1' and '2013-3-4' )t group by t.bus_name, t.ticket_date )b group by b.bus_name ) tempbus on bus.bus_name = tempbus.bus_name
select bus_name,date,cast((max(ticket_seat_no)*100/bus_seatcount) as varchar)+'%' from table_bus left join table_ticket on bus_id =ticket_bus_id group by bus_name,date,bus_seatcount
insert into table_bus (bus_name, bus_seatcount) values ('bus02', 20);
insert into table_bus (bus_name, bus_seatcount) values ('bus03', 40);
insert into table_bus (bus_name, bus_seatcount) values ('bus04', 35);
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-3');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,3,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(4,1,'2013-3-3');select bus.bus_name, b.ticket_date, cast(b.num*100/bus.bus_seatcount as varchar(50))+'%' 上座率
from table_bus bus join
(
select t.bus_name, t.ticket_date, COUNT(t.bus_name) as num from
(
select bus_name, ticket_date from table_bus a
left join table_ticket b
on a.bus_id = b.ticket_bus_id
where ticket_date between '2013-2-1' and '2013-3-4'
)t
group by t.bus_name, t.ticket_date
)b
on bus.bus_name = b.bus_name
order by b.ticket_datebus_name ticket_date 上座率
bus01 2013-03-01 6%
bus02 2013-03-01 10%
bus03 2013-03-01 7%
bus01 2013-03-02 6%
bus02 2013-03-02 10%
bus02 2013-03-03 5%
bus04 2013-03-03 2%
2013-03-01 -> 2013-03-03
bus_name 上座率
bus01 4.44%
bus02 8.33%
bus03 2.5%
bus04 0.95%
insert into table_bus (bus_name, bus_seatcount) values ('bus02', 20);
insert into table_bus (bus_name, bus_seatcount) values ('bus03', 40);
insert into table_bus (bus_name, bus_seatcount) values ('bus04', 35);
insert into table_bus (bus_name, bus_seatcount) values ('bus05', 35);
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,1,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(1,2,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,2,'2013-3-2');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(2,1,'2013-3-3');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,1,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,2,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(3,3,'2013-3-1');
insert into table_ticket(ticket_bus_id, ticket_seat_no, date) values(4,1,'2013-3-3');得出:
2013-03-01 -> 2013-03-03
bus_name 上座率
bus01 4.44%
bus02 8.33%
bus03 2.5%
bus04 0.95%
bus05 0.00%
select bus_name
,convert(varchar,convert(decimal(10,2),sum(ISNULL(b.num,0))*100.0/(a.bus_seatcount*DATEDIFF(d,'2013-03-01','2013-03-04'))))+'%'
from table_bus a outer apply
(select MAX(Ticket_SeatNo)num from table_ticket where a.Bus_Id=Ticket_Bus group by date)b
group by bus_name,bus_seatcount
order by bus_name
/*
bus_name (无列名)
bus01 4.44%
bus02 8.33%
bus03 2.50%
bus04 0.95%
bus05 0.00%
*/
create table Buses
(
Bus_Id int identity,
Bus_Name nvarchar(100),
Bus_SeatsCount tinyint
)
GO--票务,这里是一个事实表,描述票务的情况,所有还得有个日期,票数为0的车次没有记录
create table Tickets
(
Ticket_Id int identity,
Ticket_Bus int,
Ticket_SeatNo tinyint,
Ticket_Date datetime
)
GO--测试数据,班车
insert into Buses values('bus1',35)
insert into Buses values('bus2',40)
--select * from Buses
GO--测试数据,有票的日期和班车,中间的seatno没写
insert into Tickets values (1,1,'2013-1-10')
insert into Tickets values (1,20,'2013-1-10')
insert into Tickets values (2,1,'2013-2-12')
insert into Tickets values (2,35,'2013-2-12')
--select * from Tickets
GO--如果报表需要展示每一天的上座率,还得配合一个日期列表,因为票务表中没有不出票的记录
create table days
(
Ticket_Date datetime
)
GOinsert into days
select DATEADD(DAY,num,'2013-01-01') from
(
select ROW_NUMBER() over(order by number) as num from spt_values
) t
GO
--select * from days--构造一个日期和班车的视图
create view days_bus
as
select d.Ticket_Date,b.Bus_Id from days d cross join Buses b
GO--用每天每辆车的座位号/最大座位号,得出上座率
select l.Ticket_Date,l.Bus_Id,isnull(l.max_SeatNo,0)*1.0/b.Bus_SeatsCount as seat_rate
from
(
select db.Ticket_Date,db.Bus_Id,MAX(t.Ticket_SeatNo) as max_SeatNo
from days_bus db left join Tickets t
on db.Ticket_Date = t.Ticket_Date and db.Bus_Id = t.Ticket_Bus
where db.Ticket_Date <= '2013-02-12'
group by db.Ticket_Date,db.Bus_Id
) as l inner join Buses b
on l.Bus_Id = b.Bus_Id
order by l.Ticket_DateTicket_Date Bus_Id seat_rate
2013-01-02 00:00:00.000 1 0.000000
2013-01-02 00:00:00.000 2 0.000000
2013-01-03 00:00:00.000 1 0.000000
2013-01-03 00:00:00.000 2 0.000000
2013-01-04 00:00:00.000 1 0.000000
2013-01-04 00:00:00.000 2 0.000000
2013-01-05 00:00:00.000 1 0.000000
2013-01-05 00:00:00.000 2 0.000000
2013-01-06 00:00:00.000 1 0.000000
2013-01-06 00:00:00.000 2 0.000000
2013-01-07 00:00:00.000 1 0.000000
2013-01-07 00:00:00.000 2 0.000000
2013-01-08 00:00:00.000 1 0.000000
2013-01-08 00:00:00.000 2 0.000000
2013-01-09 00:00:00.000 1 0.000000
2013-01-09 00:00:00.000 2 0.000000
2013-01-10 00:00:00.000 1 0.571428
2013-01-10 00:00:00.000 2 0.000000
2013-01-11 00:00:00.000 1 0.000000
2013-01-11 00:00:00.000 2 0.000000
2013-01-12 00:00:00.000 1 0.000000
2013-01-12 00:00:00.000 2 0.000000
2013-01-13 00:00:00.000 1 0.000000
2013-01-13 00:00:00.000 2 0.000000
2013-01-14 00:00:00.000 1 0.000000
2013-01-14 00:00:00.000 2 0.000000
2013-01-15 00:00:00.000 1 0.000000
2013-01-15 00:00:00.000 2 0.000000
2013-01-16 00:00:00.000 1 0.000000
2013-01-16 00:00:00.000 2 0.000000
2013-01-17 00:00:00.000 1 0.000000
2013-01-17 00:00:00.000 2 0.000000
2013-01-18 00:00:00.000 1 0.000000
2013-01-18 00:00:00.000 2 0.000000
2013-01-19 00:00:00.000 1 0.000000
2013-01-19 00:00:00.000 2 0.000000
2013-01-20 00:00:00.000 1 0.000000
2013-01-20 00:00:00.000 2 0.000000
2013-01-21 00:00:00.000 1 0.000000
2013-01-21 00:00:00.000 2 0.000000
2013-01-22 00:00:00.000 1 0.000000
2013-01-22 00:00:00.000 2 0.000000
2013-01-23 00:00:00.000 1 0.000000
2013-01-23 00:00:00.000 2 0.000000
2013-01-24 00:00:00.000 1 0.000000
2013-01-24 00:00:00.000 2 0.000000
2013-01-25 00:00:00.000 1 0.000000
2013-01-25 00:00:00.000 2 0.000000
2013-01-26 00:00:00.000 1 0.000000
2013-01-26 00:00:00.000 2 0.000000
2013-01-27 00:00:00.000 1 0.000000
2013-01-27 00:00:00.000 2 0.000000
2013-01-28 00:00:00.000 1 0.000000
2013-01-28 00:00:00.000 2 0.000000
2013-01-29 00:00:00.000 1 0.000000
2013-01-29 00:00:00.000 2 0.000000
2013-01-30 00:00:00.000 1 0.000000
2013-01-30 00:00:00.000 2 0.000000
2013-01-31 00:00:00.000 1 0.000000
2013-01-31 00:00:00.000 2 0.000000
2013-02-01 00:00:00.000 1 0.000000
2013-02-01 00:00:00.000 2 0.000000
2013-02-02 00:00:00.000 1 0.000000
2013-02-02 00:00:00.000 2 0.000000
2013-02-03 00:00:00.000 1 0.000000
2013-02-03 00:00:00.000 2 0.000000
2013-02-04 00:00:00.000 1 0.000000
2013-02-04 00:00:00.000 2 0.000000
2013-02-05 00:00:00.000 1 0.000000
2013-02-05 00:00:00.000 2 0.000000
2013-02-06 00:00:00.000 1 0.000000
2013-02-06 00:00:00.000 2 0.000000
2013-02-07 00:00:00.000 1 0.000000
2013-02-07 00:00:00.000 2 0.000000
2013-02-08 00:00:00.000 1 0.000000
2013-02-08 00:00:00.000 2 0.000000
2013-02-09 00:00:00.000 1 0.000000
2013-02-09 00:00:00.000 2 0.000000
2013-02-10 00:00:00.000 1 0.000000
2013-02-10 00:00:00.000 2 0.000000
2013-02-11 00:00:00.000 1 0.000000
2013-02-11 00:00:00.000 2 0.000000
2013-02-12 00:00:00.000 1 0.000000
2013-02-12 00:00:00.000 2 0.875000
from table_bus bus join
(
select b.bus_name, SUM(b.num) as total_num
from
(
select t.bus_name, t.ticket_date, COUNT(t.bus_name) as num from
(
select bus_name, ticket_date from table_bus a
left join table_ticket b
on a.bus_id = b.ticket_bus_id
where ticket_date between '2013-2-1' and '2013-3-4'
)t
group by t.bus_name, t.ticket_date
)b
group by b.bus_name
) tempbus
on bus.bus_name = tempbus.bus_name
bus01 13%
bus02 25%
bus03 7%
bus04 2%
select bus_name,date,cast((max(ticket_seat_no)*100/bus_seatcount) as varchar)+'%' from
table_bus left join table_ticket on bus_id =ticket_bus_id
group by bus_name,date,bus_seatcount