需求:
1.1. Allows searching of Feeder Updates by Channel ID and/or DateTime_Updated range
1.2. The following information is to be displayed for each search result
• Channel ID
• Start Time (DateTime_Updated field and Type = 0)
• End Time (DateTime_Updated field and Type = 1)
1.3. Allow listing of all Feeder Updates sorted by DateTime_Updated Descending
Schema.sql
DROP TABLE IF EXISTS `Feeder_Update`;
CREATE TABLE `Feeder_Update` (
`ID` bigint(64) unsigned NOT NULL auto_increment,
`Channel_ID` int(10) unsigned NOT NULL,
`Type` tinyint(1) NOT NULL,-- Type: 0 - Start Date, 1 - End Date
`DateTime_Updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
INDEX `Index_ChannelID` (`Channel_ID`, `Type`, `DateTime_Updated`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
样本数据
"ID","Channel_ID","Type","DateTime_Updated"
1,31087,0,"2009-08-07 18:46:56"
2,31087,1,"2009-08-07 18:46:58"
3,35754,0,"2009-08-07 18:47:01"
4,35754,1,"2009-08-07 18:47:04"
5,31087,0,"2009-08-07 18:47:06"
6,35754,0,"2009-08-07 18:47:08"
7,31087,1,"2009-08-07 18:47:11"
8,35754,1,"2009-08-07 18:47:13"
显示结果:
"Channel_ID","Start Time " , "End Time "
31087 ,"2009-08-07 18:46:56" ,"2009-08-07 18:46:58"
35754 ,"2009-08-07 18:47:01" ,"2009-08-07 18:47:04"
31087 ,"2009-08-07 18:47:06" ,"2009-08-07 18:47:11"
35754 ,"2009-08-07 18:47:08" ,"2009-08-07 18:47:13"主要是Channel_ID是可以重复的,不知道从何下手了,考虑过按时间排序后,然后程序来判断,但是这样无法实现分页了。底层数据库多个系统在使用,不能更改表结构。
各位朋友,有什么解决办法吗?谢谢
1.1. Allows searching of Feeder Updates by Channel ID and/or DateTime_Updated range
1.2. The following information is to be displayed for each search result
• Channel ID
• Start Time (DateTime_Updated field and Type = 0)
• End Time (DateTime_Updated field and Type = 1)
1.3. Allow listing of all Feeder Updates sorted by DateTime_Updated Descending
Schema.sql
DROP TABLE IF EXISTS `Feeder_Update`;
CREATE TABLE `Feeder_Update` (
`ID` bigint(64) unsigned NOT NULL auto_increment,
`Channel_ID` int(10) unsigned NOT NULL,
`Type` tinyint(1) NOT NULL,-- Type: 0 - Start Date, 1 - End Date
`DateTime_Updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
INDEX `Index_ChannelID` (`Channel_ID`, `Type`, `DateTime_Updated`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
样本数据
"ID","Channel_ID","Type","DateTime_Updated"
1,31087,0,"2009-08-07 18:46:56"
2,31087,1,"2009-08-07 18:46:58"
3,35754,0,"2009-08-07 18:47:01"
4,35754,1,"2009-08-07 18:47:04"
5,31087,0,"2009-08-07 18:47:06"
6,35754,0,"2009-08-07 18:47:08"
7,31087,1,"2009-08-07 18:47:11"
8,35754,1,"2009-08-07 18:47:13"
显示结果:
"Channel_ID","Start Time " , "End Time "
31087 ,"2009-08-07 18:46:56" ,"2009-08-07 18:46:58"
35754 ,"2009-08-07 18:47:01" ,"2009-08-07 18:47:04"
31087 ,"2009-08-07 18:47:06" ,"2009-08-07 18:47:11"
35754 ,"2009-08-07 18:47:08" ,"2009-08-07 18:47:13"主要是Channel_ID是可以重复的,不知道从何下手了,考虑过按时间排序后,然后程序来判断,但是这样无法实现分页了。底层数据库多个系统在使用,不能更改表结构。
各位朋友,有什么解决办法吗?谢谢
解决方案 »
- 如何在mysql中删除某一个单元格的值
- org.apache.jasper.JasperException: Cannot find any information on property 'name
- 多个select语句做连接问题.......急
- mysql用什么软件做反向建模好
- varchar和char的问题
- 如何在一行中返回两个字段名相同的值,sql句法求救,在线等......
- 请问MYSQL是个什么样的数据库?应用程序可以在MYSQL上开发吗?
- 求一个高效率的MySQL查询写法
- 求Mysql安装包,去oracle官网下载老是链接无法链接
- 数据库用My SQL 这个怎么样?可否教教怎么连接
- 怎样加快这种情况的查询速度?
- select所有记录查找和一条一条查哪个效率高
select
a.Channel_ID,
a.DateTime_Updated as Start_Time,
(select b.DateTime_Updated from tb_name b where id =(select min(id) from tb_name c where c.Channel_ID=a.Channel_ID and c.type=1 and c.id>a.id)) as End_Time
from tb_name a
where type=0
select *
from (
select Channel_ID ,min(DateTime_Updated) as Start_Time
from Feeder_Update
group by Channel_ID
having type=0
) a
left join (
select Channel_ID ,min(DateTime_Updated) as End_Time
from Feeder_Update
group by Channel_ID
having type=1
) b
on a.Channel_ID = b.Channel_ID
limit 0,10无法理解你的要求1.3.中的DateTime_Updated是指什么
是整个是Start_Time还是End_Time或者Start_Time-End_Time
"ID","Channel_ID","Type","DateTime_Updated"
1, 30837, 0, "2009-08-07 18:47:16"
2, 30837, 0, "2009-08-07 19:35:36"
3, 30837, 1, "2009-08-07 20:23:56"
第一开始的时候没有保存结束的时间。将会出现以下的结果:
"Channel_ID", "Start_Time", "End_Time"
30837, "2009-08-07 18:47:16", "2009-08-07 20:23:56"
30837, "2009-08-07 19:35:36", "2009-08-07 20:23:56"
而实际情况应该是这样的
"Channel_ID", "Start_Time", "End_Time"
30837, "2009-08-07 18:47:16", ""
30837, "2009-08-07 19:35:36", "2009-08-07 20:23:56"有没有办法解决这样的问题?还有如何求其总的数量(直接在外层count(Start_Time))?
Select Min(DateTime_Updated)
From Feeder_Update
Where Channel_ID=a.Channel_ID And type=1 And DateTime_Updated>a.DateTime_Updated
) As End_Time
from Feeder_Update a
where a.type=0
Order By Start_Time
1.3只是排序问题,原则上应该是Start_Time
可以将2楼的稍加改造select
a.Channel_ID,
a.DateTime_Updated as Start_Time,
(select if(b.Type=1,b.DateTime_Updated,null) from Feeder_Update b where id =(select min(id) from Feeder_Update c where c.Channel_ID=a.Channel_ID and c.id>a.id)) as End_Time
from Feeder_Update a
where type=0
order by a.ID
a.Channel_ID,
a.DateTime_Updated as Start_Time,
(select b.DateTime_Updated from tb_name b
where id =
(select min(c.id) from tb_name c where c.Channel_ID=a.Channel_ID and c.type=1 and c.id>a.id
and c.id<(select min(d.id) from tb_name d where d.Channel_ID=a.Channel_ID and d.type=0 and d.id>a.id)
)
) as End_Time,
count(*) as total_count
from tb_name a
where type=0
a.Channel_ID,
a.DateTime_Updated as Start_Time,
(select b.DateTime_Updated from tb_name b
where id =
(select min(c.id) from tb_name c where c.Channel_ID=a.Channel_ID and c.type=1 and c.id>a.id
and c.id<(select min(d.id) from tb_name d where d.Channel_ID=a.Channel_ID and d.type=0 and d.id>a.id)
)
) as End_Time
from tb_name a
where type=0
if()是mysql特有的,你也可以使用sql92标准里的,case when ..... end
如
select case when b.Type=1 then b.DateTime_Updated else null end from Feeder_Update
set @flag1=0;
select f.Channel_ID,f.DateTime_Updated as Start_time,f1.DateTime_Updated as Stop_Time
from
(select @flag:=@flag+1 fl,id,Channel_ID,Type,DateTime_Updated from Feeder_Update where Type=0) f,
(select @flag1:=@flag1+1 fl1 ,id,Channel_ID,Type,DateTime_Updated from Feeder_Update where Type=1) f1
where f.fl = f1.fl1
;