Question

我提出以下询问:

$data = mysql_fetch_array(mysql_query("SELECT 
c.`id` AS cid,
p.`id` AS pid,p.`email`

FROM `coupons` AS c 
LEFT JOIN `coupons_partners` AS cp ON cp.`cid` = c.`id`
LEFT JOIN `partners` AS p ON p.`id` = cp.`pid`
LEFT JOIN `bills` AS b ON b.`pid` = p.`id`

WHERE 
( 
    CURRENT_DATE() BETWEEN c.`expires` AND ADDDATE(c.`expires`, INTERVAL 1 MONTH) 
)
OR
( 
    CURRENT_DATE() NOT BETWEEN c.`expires` AND ADDDATE(c.`expires`, INTERVAL 1 MONTH) 
    AND 
    CURRENT_DATE() BETWEEN b.`date` AND ADDDATE(b.`date`, INTERVAL 1 MONTH)
)
ORDER BY b.`id` DESC"));

It s kinda messy. I want to do a cronjob and create bills automatically. They should only be created after 1 month a coupon expires (c.expires is DATETIME) or if the LAST bill (b.date) was created 1 month ago. The thing also is that I don t want to create any bill if the coupon didn t started yet. And this is possible, because I create coupons that ll start in maybe 3 months. So I guess the "Between-Solution" doesn t fit here?

我试图把它说得当,但现在我会感谢任何帮助。

如果有人能帮助我,我会高兴和感谢。

最佳方面,

* A/63/150。

Answer 1

森林论坛

SELECT DATEDIFF(month,  2000-02-10 , 2000-01-10 );

产出1

<>月可改为<>日,以获得天数。

参考:rel=“nofollow” http://www.1keydata.com/sql/sql-datediff.html。

UPDATE: In MSQL you could use DATEDIFF because it can be passed month as an argument, the MySQL version only returns days. In MySQL you could instead use PERIOD_DIFF. Although it doesn t know about the exact number of days in each month.

PERIOD_DIFF(P1,P2)

“P1至P2、P1和P2之间的月数应采用YYMM或YYYYMM格式。请注意,P1和P2这两个期间的论点不是日值......

mysql> SELECT PERIOD_DIFF(200802,200703);
        -> 11

If you need to know if exactly how many months differ you need to make a multistep calculation. To find out the number of days in a month you could use the LAST_DAY function and from the results extract the day part and use as the basis for further calculations.

 LAST_DAY( 2003-02-05 );
        ->  2003-02-28

友情链接