how to select last 6 months (names)

luckiejacky

1. select 'VIP' as type,  strftime('%m', 'now') AS month

複製代碼

If this month is October,
I want to select
VIP  10
VIP  9
VIP 8
VIP 7
VIP 6
VIP 5
how do I do that?
Thanks

DarkHero

select 'VIP' as type,  strftime('%m', 'now') AS month order by month desc limit 6

luckiejacky

Thanks thanks!!!
But it only selects 10

神秘二代

用between

luckiejacky

This is okay when I open up another table, but when this month is June, December will not be selected

1. where month between   strftime('%m', 'now')  AND strftime('%m', date('now','-6 month' ))

複製代碼

06 between 06 AND 12

only works

10 between 10 AND 05

The first one must be a bigger value than the second

1. select 'VIP' as type,  month
   
2. from month
   
3. 
   
4. where month between   strftime('%m', 'now')  AND strftime('%m', date('now','-6 month' ))
   
5. 
   
6. order by month desc limit 12

複製代碼

The order of the date predicate is important, but it has different results
whenever the current month is

update:
still doesn't work

1. select "VIP" as vip, month
   
2. from month
   
3.   where
   
4.     month BETWEEN   strftime('%m', date('now', '-12 month')) AND strftime('%m', 'now')
   
5.            OR month BETWEEN strftime('%m', 'now') AND strftime('%m', date('now', '-12 month'))

複製代碼

luckiejacky

1. select 'VIP' AS type, m.monthname AS dt, o.PurchaseDateTime from
   
2.                 month m , orders o
   
3.                         
   
4.                where   
   
5. 
   
6.                      m.monthname < strftime('%m', date('now'))  AND  m.monthname > strftime('%m', date('now','-12 MONTH'))

複製代碼

This is a disjoint, but -6 months is a union,
How to solve, very hairy, please help

henrywho

點解我成日都理解唔同到樓主嘅提問嘅?

justlazy

點解我成日都理解唔同樓主嘅提問嘅?
henrywho 發表於 2013-10-14 11:23

same here

Jimmy0911

回復 8# justlazy


    same here