请教一个SQL语句的写法 （关于GROUP和JOIN）

吹雪 2013-03-21 01:14:25
如下两表：
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班），请教该如何写法？
...全文
237 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
莱昂纳多达芬奇 2013-03-21
  • 打赏
  • 举报
 回复
有个bus_seatcount就好了
usbusb2007 2013-03-21
  • 打赏
  • 举报
 回复
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
莱昂纳多达芬奇 2013-03-21
  • 打赏
  • 举报
 回复
搞错了，分母要乘天数，你自己改下。。。
莱昂纳多达芬奇 2013-03-21
  • 打赏
  • 举报
 回复
bus_name 上座率 bus01 13% bus02 25% bus03 7% bus04 2%
莱昂纳多达芬奇 2013-03-21
  • 打赏
  • 举报
 回复 
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
seusoftware 2013-03-21
  • 打赏
  • 举报
 回复
--班车，这里是一个实体表，描述班车 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 ) GO insert 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_Date Ticket_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
szm341 2013-03-21
  • 打赏
  • 举报
 回复 

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%
*/
吹雪 2013-03-21
  • 打赏
  • 举报
 回复
就是从03-01到03-03是3天时间 那么将这三天的总数除以这三天的可售票总数，得出来的。
szm341 2013-03-21
  • 打赏
  • 举报
 回复
引用 4 楼 hailongxl 的回复:
谢谢楼上的，能否实现这样的： 2013-03-01 -> 2013-03-03 bus_name 上座率 bus01 4.44% bus02 8.33% bus03 2.5% bus04 0.95%
你这结果是咋算的？不是按天统计吗
吹雪 2013-03-21
  • 打赏
  • 举报
 回复
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%
吹雪 2013-03-21
  • 打赏
  • 举报
 回复
谢谢楼上的，能否实现这样的： 2013-03-01 -> 2013-03-03 bus_name 上座率 bus01 4.44% bus02 8.33% bus03 2.5% bus04 0.95%
莱昂纳多达芬奇 2013-03-21
  • 打赏
  • 举报
 回复 
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_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-21
  • 打赏
  • 举报
 回复
满座率=当天该班车的MAX(Ticket_SeatNo)/Bus_SeatsCount
發糞塗牆 2013-03-21
  • 打赏
  • 举报
 回复
如果2013-03-20日Tickets表里Ticket_Bus=1的记录的MAX(Ticket_SeatNo)=n表示该车次当天卖出了n张票。 这一句，我想到应该要加个case when来做判断，也就是实现判断是否已经卖出甚至卖完了。
SQL语言 group by 和 LEFT JOIN 关键字语句使用
MySql 语言 group by 和LEFT JOIN 关键字语句使用 (1) GROUP BY 语句 : GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。 语法： SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator ...
7种SQL Join语句
1、SQL语句结构 select distinct < select_list > from < left_table > < join_type > join < right_table > on < join_condition > where < where_condition > group by < group_by_list > having...
python写sql语句_python基础---sql语句
数据库系统：数据库：文件夹数据表：文件表记录：一条数据sql语句规范sql是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言 在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写； SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。 用空...
SQL入门之第十六讲——总结下之前的SQL语句书写顺序
SQL语句的书写顺序与执行顺序不是一致的 1、SQL语句的书写顺序 语句的书写顺序是比较重要的，如果顺序不对去执行也是会报错的。 之前的SELECT、WHERE、ORDER BY、LIMIT、JOIN、UNION、GROUP BY、HAVING等语法，如果这些语句在SQL中同时存在，那书写顺序就非常重要了。 基本语法： SELECT <字段名> FROM <表名a> JOIN <表名b> ON <a.字段名 = b.字段名> WHERE <筛
sql语句写法
一、LEFT JOIN 1.1 用法 LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。 1.2 例子 “Persons” 表： “Orders” 表： 现在希望列出所有人，以及他们的订购-如果有的话，可以使用下面的sql语句 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Pers
MS-SQL Server

34,837

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章