![](https://i-blog.csdnimg.cn/blog_migrate/1396a8d906f1f667debccf6fb454c9cf.jpeg)
-- 查找含有A 或 C 站点的车次
drop table SCHEDULETEST;
create table SCHEDULETEST
(
SID int not null ,
CHECI VARCHAR ( 8 ),
CHEZHAN VARCHAR ( 8 )
);
新增示例数据
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (1, 'K1', 'A');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (2, 'K1', 'B');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (3, 'K1', 'C');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (1, 'K1', 'A');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (4, 'K2', 'A');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (5, 'K2', 'B');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (6, 'K2', 'C');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (7, 'K3', 'D');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (8, 'K4', 'D');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (9, 'K5', 'D');
insert into SCHEDULETEST (SID, CHECI, CHEZHAN)
values (9, 'K5', 'C');
select
*
from
scheduletest;
SELECT CHECI as ' 经过A和C站点的车次 '
FROM SCHEDULETEST
WHERE CHEZHAN IN ( ' A ' , ' C ' )
GROUP BY CHECI
HAVING COUNT ( distinct SID) >= 2 ;
SELECT CHECI
FROM SCHEDULETEST A
WHERE (CHEZHAN = ' A ' OR CHEZHAN = ' C ' )
GROUP BY CHECI
HAVING COUNT ( distinct SID) >= 2 ;
-- 取出每一车次停靠的站点数
select checi ' 车次 ' , count ( distinct chezhan) AS ' 停靠站点数量 '
from SCHEDULETEST
group by checi;
-- 取出每一车次停靠 A,C的站点次数
select checi AS 车次,
SUM ( case chezhan when ' A ' THEN 1 ELSE 0 END ) ' 停靠A站点的次数 ' ,
SUM ( case chezhan when ' C ' THEN 1 ELSE 0 END ) ' 停靠C站点的次数 '
FROM SCHEDULETEST
GROUP BY CHECI;
SELECT CHECI as ' 经过A和C站点的车次 '
FROM SCHEDULETEST
WHERE CHEZHAN IN ( ' A ' , ' C ' )
GROUP BY CHECI
HAVING COUNT ( distinct SID) >= 2 ;
SELECT CHECI
FROM SCHEDULETEST A
WHERE (CHEZHAN = ' A ' OR CHEZHAN = ' C ' )
GROUP BY CHECI
HAVING COUNT ( distinct SID) >= 2 ;
-- 取出每一车次停靠的站点数
select checi ' 车次 ' , count ( distinct chezhan) AS ' 停靠站点数量 '
from SCHEDULETEST
group by checi;
-- 取出每一车次停靠 A,C的站点次数
select checi AS 车次,
SUM ( case chezhan when ' A ' THEN 1 ELSE 0 END ) ' 停靠A站点的次数 ' ,
SUM ( case chezhan when ' C ' THEN 1 ELSE 0 END ) ' 停靠C站点的次数 '
FROM SCHEDULETEST
GROUP BY CHECI;