table `program_list`
id date time program channel_id
1 2015-06-15 00:37:00 动物世界 CCTV1
2 2015-06-15 01:11:00 精彩一刻 CCTV1
3 2015-06-15 02:31:00 星光大道 CCTV1
4 2015-06-15 04:09:00 今日说法 CCTV1
5 2015-06-15 04:59:00 新闻联播 CCTV1
6 2015-06-15 00:13:00 一槌定音 CCTV2
7 2015-06-15 01:08:00 经济信息联播 CCTV2
8 2015-06-15 01:58:00 中国大能手(10) CCTV2
9 2015-06-15 02:57:00 对话 CCTV2
10 2015-06-15 03:55:00 财经周刊 CCTV2
根据节目表想查出当前所有频道播放的节目,因为节目是按时间排序的,所以采用以下sql语句:$query_program = "SELECT * FROM (SELECT * FROM program_list where `time`<='02:00:00' and `date`='2015-06-15' ORDER BY `id` desc ) A GROUP BY `channel_id` ";
现在需求是:同时查出后一个节目。比如现在2:00:00,当前CCTV1节目为“01:11:00 精彩一刻”,后一个节目为“02:31:00 星光大道”,如何同时将每个频道这两个节目都查询出来? 求教~~~
解决方案 »
- 如何实现两个主数据库互相同步?
- 用存储过程 拆分字段
- mysql按小时统计注册人数怎么弄,只有一个注册时间字段格式为('2010-06-10 20:39:18')
- SQL语句
- Mysql初级认证
- 在mysql中用tinyint 类型做主关健字的类型长度为4,并且默认值为autoincreatment
- 请教高手, MySQL学习方法
- 表外键与自己的主键关联(a foreign key constraint fails)
- procedure里面给表添加一行,但是ON DELETE NO ACTION ON UPDATE NO ACTION,
- mysql的复杂sql(有group by ) 只能建立temptable视图, 查询很慢, 怎么解决?
- 数据替换
- 提问mysql group by 的问题
(SELECT ss,ss1,`channel_id` FROM (
SELECT *,(SELECT id FROM ss2
WHERE a.`channel_id`=`channel_id`
AND `date`='2015-6-15' AND TIME(`time`)>='02:00:00'
ORDER BY TIME
LIMIT 1
) AS ss ,
(SELECT id FROM ss2
WHERE a.`channel_id`=`channel_id`
AND `date`='2015-6-15' AND TIME(`time`)<='02:00:00'
ORDER BY TIME DESC
LIMIT 1
) AS ss1 FROM ss2 a) a1 GROUP BY ss,ss1,`channel_id`) b
ON a.`channel_id`=b.`channel_id`
AND (a.`id`=b.ss OR a.id=b.ss1)
from program_list t
where not exists (select 1 from program_list where channel_id=t.channel_id and date+time between t.date+t.time and now() )
or not exists (select 1 from program_list where channel_id=t.channel_id and date+time between now() and t.date+t.time )
select * from
(SELECT * FROM (SELECT * FROM program_list where `time`>'12:00:00' and `date`='2015-06-29' ORDER BY `id` ) A GROUP BY `channel_id`
union
SELECT * FROM (SELECT * FROM program_list where `time`<='12:00:00' and `date`='2015-06-29' ORDER BY `id` desc ) A GROUP BY `channel_id`
) as a
order by `channel_id`,`id`感谢两位版主~~