電腦領域 HKEPC Hardware - Powered by Discuz! Board

標題: how to select last 6 months (names) [打印本頁]

作者: luckiejacky 時間: 2013-10-12 16:25 標題: how to select last 6 months (names)

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 時間: 2013-10-13 02:13

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

作者: luckiejacky 時間: 2013-10-13 11:37

本帖最後由 luckiejacky 於 2013-10-13 11:42 編輯

Thanks thanks!!!
But it only selects 10

作者: 神秘二代 時間: 2013-10-13 23:56

用between

作者: luckiejacky 時間: 2013-10-14 04:56

本帖最後由 luckiejacky 於 2013-10-14 08:17 編輯

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

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

select 'VIP' as type, month
from month
where month between strftime('%m', 'now') AND strftime('%m', date('now','-6 month' ))
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

select "VIP" as vip, month
from month
where
month BETWEEN strftime('%m', date('now', '-12 month')) AND strftime('%m', 'now')
OR month BETWEEN strftime('%m', 'now') AND strftime('%m', date('now', '-12 month'))

複製代碼

作者: luckiejacky 時間: 2013-10-14 10:21

select 'VIP' AS type, m.monthname AS dt, o.PurchaseDateTime from
month m , orders o
where
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 時間: 2013-10-14 11:23

本帖最後由 henrywho 於 2013-10-14 21:11 編輯

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

作者: justlazy 時間: 2013-10-14 16:12

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

same here

作者: Jimmy0911 時間: 2013-10-14 16:15

回復 8# justlazy

same here

作者: Flower 時間: 2013-10-14 16:18

提示: 作者被禁止或刪除內容自動屏蔽

作者: 杜龍 時間: 2013-10-14 16:41

支持樓主出糧比我地！

作者: snoopy11hk 時間: 2013-10-14 18:10

支持樓主出糧比我地！
杜龍發表於 2013-10-14 16:41

consulting service

作者: Flower 時間: 2013-10-14 19:55

提示: 作者被禁止或刪除內容自動屏蔽

作者: henrywho 時間: 2013-10-14 21:14

可能樓主真係做到呢....
Flower 發表於 2013-10-14 16:18

其實我只係識答 SQL 問題, 同埋我真係想幫吓手㗎
不過係睇唔明吖嘛 .......

作者: luckiejacky 時間: 2013-10-14 21:48

你地晌度挖苦緊我啫...

作者: DLeung2008 時間: 2013-10-15 01:11

我起初明少少, 但再睇落去就亂晒籠.

作者: 神秘二代 時間: 2013-10-15 02:33

本帖最後由神秘二代於 2013-10-15 02:39 編輯

你地晌度挖苦緊我啫...
luckiejacky 發表於 2013-10-14 21:48

你用中文講下你想點
Because we don't know what you say in english......
Is that you want to select which VIPs are registed last 6 months?

作者: Flower 時間: 2013-10-15 10:17

提示: 作者被禁止或刪除內容自動屏蔽

作者: ThomasYau 時間: 2013-10-15 12:18

本帖最後由 ThomasYau 於 2013-10-15 13:05 編輯

樓主, SHOW 個 TABLE STRUCTURE 出黎睇睇?
month field stores integer from 1 to 12 ? 無 year field ? 咁 2012-10 都係 VIP ?