浅浅_、 发表于 2013-10-12 12:28:29

求教一条Mysql，查询大于等于3条连续的重复记录，急急急！！！！

源数据
http://imgsrc.baidu.com/forum/w%3D580%3Bcp%3Dtieba%2C10%2C371%3Bap%3Dmysql%B0%C9%2C90%2C379/sign=07ac1655af345982c58ae59a3ccf52dd/4fdd8c1001e93901b14e0f1f79ec54e736d19643.jpg

用orcl过滤出的结果集，想改成mysql语句
http://imgsrc.baidu.com/forum/w%3D580%3Bcp%3Dtieba%2C10%2C369%3Bap%3Dmysql%B0%C9%2C90%2C377/sign=a0dc7cfe74094b36db921be593f71fa9/be1400e93901213f297fccfd56e736d12f2e9543.jpg
求 连续的3条或3条以上 if_alarm=1记录 的 起始时间，终止时间 和记录个数 orcale 写出来了，但是mysql真心不会，求大神指教

with tt as (SELECT t.*,
                row_number()over(order by t.time) sort_num1,
               row_number() over(partition by if_alarm order by t.time) sort_num2
          FROM MD_OP_MONITOR_INFO t
         order by t.time),
ts as (select a.*, a.sort_num1 - a.sort_num2 sc from tt a where a.if_alarm = 1)
selectmin(time), max(time),count(x.sc) from ts x group by x.sc having count(x.sc) >= 3

-- withas 和 row_number()over这两个函数在mysql里可以用什么代替，麻烦哪位大牛指导下？

浅浅_、 发表于 2013-10-12 12:30:06

--下面是orcl建表语句


create table MD_OP_MONITOR_INFO
(
MONITOR_ID VARCHAR2(40) not null,
VERSION VARCHAR2(40),
TIME DATE,
BASE_DEPTH NUMBER,
CUR_DEPTH NUMBER,
REAL_DEPTH NUMBER,
TEMPERATURE NUMBER,
IF_ALARM NUMBER default 0,
EQUP_NAME VARCHAR2(40)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on table MD_OP_MONITOR_INFO
is '监测信息';
comment on column MD_OP_MONITOR_INFO.MONITOR_ID
is '监测信息ID';
comment on column MD_OP_MONITOR_INFO.VERSION
is '版本号';
comment on column MD_OP_MONITOR_INFO.TIME
is '数据入库时间';
comment on column MD_OP_MONITOR_INFO.BASE_DEPTH
is '绝对深度';
comment on column MD_OP_MONITOR_INFO.CUR_DEPTH
is '测量深度';
comment on column MD_OP_MONITOR_INFO.REAL_DEPTH
is '液位深度';
comment on column MD_OP_MONITOR_INFO.TEMPERATURE
is '设备温度';
comment on column MD_OP_MONITOR_INFO.IF_ALARM
is '是否警报';
comment on column MD_OP_MONITOR_INFO.EQUP_NAME
is '设备名称';
alter table MD_OP_MONITOR_INFO
add constraint PK_MONITOR_ID primary key (MONITOR_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

alter table MD_OP_MONITOR_INFO disable all triggers;
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464', '1', to_date('10-10-2013 19:01:00', 'dd-mm-yyyy hh24:mi:ss'), 12, 10, 11, 30, 0, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('4545646a4', '1', to_date('10-10-2013 19:02:00', 'dd-mm-yyyy hh24:mi:ss'), 10, 11, 11, 29, 1, '哈尔滨监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464s', '1', to_date('10-10-2013 19:03:00', 'dd-mm-yyyy hh24:mi:ss'), 11, 12, 11, 10, 1, '哈南监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464d', '1', to_date('10-10-2013 19:04:00', 'dd-mm-yyyy hh24:mi:ss'), 17, 16, 15, 16, 1, '佳木斯监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464f', '1', to_date('10-10-2013 19:05:00', 'dd-mm-yyyy hh24:mi:ss'), 12, 10, 9, 30, 0, '牡丹江监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464g', '1', to_date('10-10-2013 19:06:00', 'dd-mm-yyyy hh24:mi:ss'), 16, 10, 8, 7, 1, '齐齐哈尔监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464h', '1', to_date('10-10-2013 19:07:00', 'dd-mm-yyyy hh24:mi:ss'), 15, 10, 11, 24, 1, '海拉尔监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464j', '1', to_date('10-10-2013 19:08:00', 'dd-mm-yyyy hh24:mi:ss'), 12, 11, 11, 37, 1, '加格达奇监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464k', '1', to_date('10-10-2013 19:09:00', 'dd-mm-yyyy hh24:mi:ss'), 22, 17, 15, 28, 1, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464l', '1', to_date('10-10-2013 19:10:00', 'dd-mm-yyyy hh24:mi:ss'), 17, 10, 11, 21, 0, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464m', '1', to_date('10-10-2013 19:11:00', 'dd-mm-yyyy hh24:mi:ss'), 12, 9, 11, 22, 1, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464n', '1', to_date('10-10-2013 19:12:00', 'dd-mm-yyyy hh24:mi:ss'), 15, 11, 11, 24, 1, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464b', '1', to_date('10-10-2013 19:13:00', 'dd-mm-yyyy hh24:mi:ss'), 20, 10, 11, 23, 1, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464v', '1', to_date('10-10-2013 19:15:00', 'dd-mm-yyyy hh24:mi:ss'), 19, 12, 12, 26, 0, '哈西监测器');
insert into MD_OP_MONITOR_INFO (MONITOR_ID, VERSION, TIME, BASE_DEPTH, CUR_DEPTH, REAL_DEPTH, TEMPERATURE, IF_ALARM, EQUP_NAME)
values ('45456464bb', '1', to_date('10-10-2013 19:16:00', 'dd-mm-yyyy hh24:mi:ss'), 22, 12, 13, 26, 1, '哈西监测器');
commit;
alter table MD_OP_MONITOR_INFO enable all triggers;

kider 发表于 2013-10-12 16:51:09

图看不到
MySQL不擅长这么复杂的SQL，最好梳理业务和逻辑，看看能不能优化和简单话
至于那两个函数，不太清楚，MySQL中也应该就是row_number() 和over()

独步古今 发表于 2013-10-23 16:34:21

需要补充两个问题：
1.图片不能正常显示。
2.连续的3条或3条以上 if_alarm=1记录，这句话有疑义。如果是连续的话，则要按照某些条件进行排序，才有连续之说。所以，楼主的意思是说，按照某个条件进行分组，然后只要达到3条或3条以上即可？

winie 发表于 2014-1-14 09:44:54


你也可这样做！

SELECT@row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber
       ,o.Customer
       ,o.OrderDate
       ,o.Amount
       ,@prev_value := o.Customer
FROM Orders o,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
ORDER BY o.Customer, o.OrderDate DESC


http://www.explodybits.com/2011/11/mysql-row-number/
页: [1]
查看完整版本: 求教一条Mysql，查询大于等于3条连续的重复记录，急急急！！！！