我已经有个一个zhan表(经过所有站的所有列车的信息,各字段如图);
现在我想形成一个lieche表,包含(checi,type,始发站,始发时间,终点站,终点时间,里程,经历站数),就是已存在的所有列车;
我已经用这样的语句形成了一个表create table checi as (SELECT checi,type,station as start,atime as stime from zhan where snum=1);只是这样还少这终点站,终点时间,里程,经历站数,请大家帮忙看看如何形成完整的lieche表啊
现在我想形成一个lieche表,包含(checi,type,始发站,始发时间,终点站,终点时间,里程,经历站数),就是已存在的所有列车;
我已经用这样的语句形成了一个表create table checi as (SELECT checi,type,station as start,atime as stime from zhan where snum=1);只是这样还少这终点站,终点时间,里程,经历站数,请大家帮忙看看如何形成完整的lieche表啊
用你上述数据,要求结果是什么
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
select checi,type
,max(case when a.snum=b.start_num then a.station else '' end) as start_station
,max(case when a.snum=b.start_num then a.atime else null end) as start_time
,max(case when a.snum=b.end_num then a.station else '' end) as end_station
,max(case when a.snum=b.end_num then a.dtime else null end) as end_time
,max(b.distance) as distance
,max(b.station_qty) as qty
from zhan a inner join (
select checi,type,count(snum) as station_qty,min(snum) as start_num,max(snum) as end_num
,max(distance) as distance
from zhan
group by checi,type
) as b on a.checi=b.checi and a.type=b.type
group by checi,type