请教一个mysql的sql语句的写法

JohnnyZz 发布于 2014/10/20 12:34
阅读 286
收藏 1
"rd";"nopd"
"2014-07-14";"0"
"2014-07-15";"0"
"2014-07-18";"0"
"2014-07-22";"0"
"2014-07-24";"0"
"2014-07-30";"0"
"2014-08-04";"0"
"2014-08-06";"0"
"2014-08-07";"0"
"2014-08-11";"0"
"2014-08-15";"1"
"2014-08-16";"0"
"2014-08-19";"0"
"2014-08-21";"0"
"2014-08-22";"0"
"2014-08-23";"0"
"2014-08-24";"0"
"2014-08-25";"0"
"2014-08-26";"0"
"2014-08-28";"0"
"2014-08-29";"0"
"2014-09-01";"1"
"2014-09-02";"0"
"2014-09-03";"0"
"2014-09-04";"0"
"2014-09-05";"0"
"2014-09-06";"0"
"2014-09-08";"0"
"2014-09-09";"0"

我有这样一些数据,顺序不能变,我想去掉第一个1之前所有为0的数据,最后一个1之后所有为0的数据,请问怎么写sql语句。
加载中
0
Tedd
Tedd
可能得自己写mysql函数了,普通的sql语句应该没这功能
0
realanan
realanan
delete from table where rd < (select rd from table where nopd = 1 order by rd asc limit 1) or rd > (select rd from table where nopd = 1 order by rd desc limit 1)
试试吧,不保证对
realanan
realanan
回复 @JohnnyZz : 那也一样的啊 select * from table where rd >= (select rd from table where nopd = 1 order by rd asc limit 1) and rd <= (select rd from table where nopd = 1 order by rd desc limit 1)
JohnnyZz
JohnnyZz
我是想写select语句,不是delete语句
0
Shazi199
Shazi199
如果前面有主键ID区分每一行,那就简单了,取第一个1的IDa,取最后一个1的IDb,取IDa和IDb之间的行,完成
0
Fanxme
Fanxme
select (@rowNo := @rowNo + 1) rn,* 
from 
   table a,
   (SELECT rn,min(rd) num FROM (select (@rowNo := @rowNo + 1) rn,* from table) where nopd=1 group by rn) b,
   (SELECT rn,max(rd) num FROM (select (@rowNo := @rowNo + 1) rn,* from table) where nopd=1 group by rn) c,
where a.rn > b.rn 
and a.rn < c.rn 
0
JohnnyZz
JohnnyZz

引用来自“航航”的评论

select (@rowNo := @rowNo + 1) rn,* 
from 
   table a,
   (SELECT rn,min(rd) num FROM (select (@rowNo := @rowNo + 1) rn,* from table) where nopd=1 group by rn) b,
   (SELECT rn,max(rd) num FROM (select (@rowNo := @rowNo + 1) rn,* from table) where nopd=1 group by rn) c,
where a.rn > b.rn 
and a.rn < c.rn 

上面的数据是这个sql的查询结果

SELECT t.rd, COUNT(c) AS nopd FROM ( SELECT b.id, DATE_FORMAT(r.repayment_date,'%Y-%m-%d') AS rd, IF(DATEDIFF(r.repayment_date, DATE_ADD(b.borrow_date, INTERVAL 1 MONTH))>0,1, NULL) AS c FROM h_repayment r LEFT JOIN h_borrow b ON b.id = r.borrow_id WHERE b.status = 109 AND r.status = 4 ORDER BY r.repayment_date ) t GROUP BY t.rd 

Fanxme
Fanxme
都这么复杂了,还指望一条sql语句搞定么?
返回顶部
顶部