mysql - 我如何获取mysql中的下一个数据

Question

真的不知道你是否可以，但我需要 DATE VENC 等于“2013-02-02”。date_pay 列的值:

1-2013-01-01
2-2013-02-02
3-0000-00-00
4-0000-00-00

这是我的查询:

   SELECT s.id,
     s.name,
     s.nro_s,
     ts.cat,
     SUM( ts.pryce ) AS deuda, 
     SUM( ts.pryce ) DIV ts.pryce AS c_p,
    date_venc = (select max(date_pay) from c  ) // the date in question
    FROM s
    INNER JOIN c
    INNER JOIN ts
    WHERE s.id =  '123'
    AND c.id =  '123'
    AND c.date_pay =  '0000-00-00'
    AND s.ts = ts.id_ts

抱歉我的英语很基础。问候。

Answer 1

假设 date_venc 是 DATE 是一个可能的解决方案

 select * 
    from s 
    where s.date_venc=
    (select max(cast(SUBSTRING_INDEX(date_pay,'-',-3)as DATE))from c);

also check out sqlfiddle
http://sqlfiddle.com/#!2/64197/1

您的查询可能应该修改为，

SELECT s.id,
     s.name,
     s.nro_s,
     ts.cat,
     SUM( ts.pryce ) AS deuda, 
     SUM( ts.pryce ) DIV ts.pryce AS c_p,
    date_venc
    FROM s
    INNER JOIN c
    INNER JOIN ts
    WHERE s.id =  '123'
    AND c.id =  '123'
    AND c.date_pay =  '0000-00-00'
    AND s.ts = ts.id_ts
    AND date_venc = (select max(cast(SUBSTRING_INDEX(date_pay,'-',-3)as DATE)) from c  ) // the date in question