请教一个SQL语句的写法 (关于GROUP和JOIN)
本帖最后由 hailongxl 于 2013-03-21 13:16:27 编辑 如下两表:
1、班车表 Buses,字段如下:
Bus_Id 自动编号
Bus_Name 名称
Bus_SeatsCount 座位数量
2、售票表 Tickets,字段如下:
1、Ticket_Id 自动编号
2、Ticket_Bus 所选班车 (关联到Bus_Id)
3、Ticket_SeatNo 座位号 (大于等于1且小于等于Bus_SeatsCount)
如果2013-03-20日Tickets表里Ticket_Bus=1的记录的MAX(Ticket_SeatNo)=n表示该车次当天卖出了n张票。
有如下可能:Buses表内有该班车的记录但是Tickets表里无该车的售票记录(所以可能要用到JOIN)
想按天统计指定时间段内(该时间段包含1天或多于1天)所有班车的满座率(每班车每天1班),请教该如何写法?
[解决办法]
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_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_date
bus_nameticket_date上座率
bus012013-03-016%
bus022013-03-0110%
bus032013-03-017%
bus012013-03-026%
bus022013-03-0210%
bus022013-03-035%
bus042013-03-032%
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(无列名)
bus014.44%
bus028.33%
bus032.50%
bus040.95%
bus050.00%
*/
2013-01-12 00:00:00.00020.000000
2013-01-13 00:00:00.00010.000000
2013-01-13 00:00:00.00020.000000
2013-01-14 00:00:00.00010.000000
2013-01-14 00:00:00.00020.000000
2013-01-15 00:00:00.00010.000000
2013-01-15 00:00:00.00020.000000
2013-01-16 00:00:00.00010.000000
2013-01-16 00:00:00.00020.000000
2013-01-17 00:00:00.00010.000000
2013-01-17 00:00:00.00020.000000
2013-01-18 00:00:00.00010.000000
2013-01-18 00:00:00.00020.000000
2013-01-19 00:00:00.00010.000000
2013-01-19 00:00:00.00020.000000
2013-01-20 00:00:00.00010.000000
2013-01-20 00:00:00.00020.000000
2013-01-21 00:00:00.00010.000000
2013-01-21 00:00:00.00020.000000
2013-01-22 00:00:00.00010.000000
2013-01-22 00:00:00.00020.000000
2013-01-23 00:00:00.00010.000000
2013-01-23 00:00:00.00020.000000
2013-01-24 00:00:00.00010.000000
2013-01-24 00:00:00.00020.000000
2013-01-25 00:00:00.00010.000000
2013-01-25 00:00:00.00020.000000
2013-01-26 00:00:00.00010.000000
2013-01-26 00:00:00.00020.000000
2013-01-27 00:00:00.00010.000000
2013-01-27 00:00:00.00020.000000
2013-01-28 00:00:00.00010.000000
2013-01-28 00:00:00.00020.000000
2013-01-29 00:00:00.00010.000000
2013-01-29 00:00:00.00020.000000
2013-01-30 00:00:00.00010.000000
2013-01-30 00:00:00.00020.000000
2013-01-31 00:00:00.00010.000000
2013-01-31 00:00:00.00020.000000
2013-02-01 00:00:00.00010.000000
2013-02-01 00:00:00.00020.000000
2013-02-02 00:00:00.00010.000000
2013-02-02 00:00:00.00020.000000
2013-02-03 00:00:00.00010.000000
2013-02-03 00:00:00.00020.000000
2013-02-04 00:00:00.00010.000000
2013-02-04 00:00:00.00020.000000
2013-02-05 00:00:00.00010.000000
2013-02-05 00:00:00.00020.000000
2013-02-06 00:00:00.00010.000000
2013-02-06 00:00:00.00020.000000
2013-02-07 00:00:00.00010.000000
2013-02-07 00:00:00.00020.000000
2013-02-08 00:00:00.00010.000000
2013-02-08 00:00:00.00020.000000
2013-02-09 00:00:00.00010.000000
2013-02-09 00:00:00.00020.000000
2013-02-10 00:00:00.00010.000000
2013-02-10 00:00:00.00020.000000
2013-02-11 00:00:00.00010.000000
2013-02-11 00:00:00.00020.000000
2013-02-12 00:00:00.00010.000000
2013-02-12 00:00:00.00020.875000
[解决办法]
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