按日期显示周 [英] displaying week as per date
本文介绍了按日期显示周的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
以下是我有的
date
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2012-02-01
2012-02-03
2012-02-05
2012-02-07
2012-02-08
2012 -02-10
2012-02-14
2012-02-15
2012-02-17
2012-02-17
2012-03-01
2012-03-03
2012-03-05
2012-03-07
我想要的是如下
week |日期
++++++++++++++++++++++
1 | 2012-02-01
1 | 2012-02-03
1 | 2012-02-05
1 | 2012-02-07
2 | 2012-02-08
2 | 2012-02-10
2 | 2012-02-14
3 | 2012-02-15
3 | 2012-02-17
3 | 2012-02-17
4 | 2012-03-01
4 | 2012-03-03
4 | 2012-03-05
4 | 2012-03-07
我尝试过
SELECT WEEKDAY（date）AS week，date
FROM myTable
WHERE date BETWEEN start_date AND end_date
ORDER BY week
下面是我得到的
周|日期
++++++++++++++++++++++
1 | 2012-02-01
1 | 2012-02-03
1 | 2012-02-05
1 | 2012-02-07
1 | 2012-03-01
1 | 2012-03-03
1 | 2012-03-05
1 | 2012-03-07
2 | 2012-02-08
2 | 2012-02-10
2 | 2012-02-14
3 | 2012-02-15
3 | 2012-02-17
3 | 2012-02-17
这是不正确的，因为 2012-03-01 向上显示为1，应该是4 ...让我知道我错了什么...
提前感谢!!!
此问题与这个
解决方案
我找到了解决方案。
以下将给我我想要的...
SELECT（SELECT COUNT（distinct u2.myDate）FROM myTable u2
WHERE
u2.myDate> u1.myDate）+ 1 AS周，myDate FROM myTable u1
WHERE myDate between startDate AND endDate
ordeR BY week
更新1：
以下是我的
myDate
++++++++++++ +++++++++
2012-02-01 12:12:12
2012-02-01 12:12:12
2012-02-01 12:12 ：12
2012-02-03 12:12:12
2012-02-0 5 12:12:12
2012-02-07 12:12:12
2012-02-08 12:12:12
2012-02-08 12:12:12
2012-02-10 12:12:12
2012-02-14 12:12:12
2012-02-15 12:12:12
2012-02-17 12 ：12：12
2012-02-17 12:12:12
2012-03-01 12:12:12
2012-03-03 12:12:12
2012-03-05 12:12:12
2012-03-07 12:12:12
使用上面的查询，下面是我得到的。
week | myDate
+++++ ++++++++++++++++++++++++++++
1 | 2012-03-07 12:12:12
2 | 2012-03-05 12 ：12：12
3 | 2012-03-03 12:12:12
4 | 2012-03-01 12:12:12
5 | 2012-02-17 12:12 ：12
5 | 2012-02-17 12:12:12
6 | 2012-02-15 12:12:12
7 | 2012-02-14 12:12:12
8 | 2012-02-10 12:12:12
9 | 2012-02-08 12:12:12
9 | 2012-02-08 12:12:12
10 | 2012-02-07 12:12:12
11 | 2012-02-05 12:12:12
12 | 2012-02-03 12:12:12
13 | 2012-02-01 12:12:12
13 | 2012-02-01 12:12:12
13 | 2012-02-01 12:12:12
Below is what I have
date
+++++++++++++
2012-02-01
2012-02-03
2012-02-05
2012-02-07
2012-02-08
2012-02-10
2012-02-14
2012-02-15
2012-02-17
2012-02-17
2012-03-01
2012-03-03
2012-03-05
2012-03-07
What I want is as below
week| date
++++++++++++++++++
1 | 2012-02-01
1 | 2012-02-03
1 | 2012-02-05
1 | 2012-02-07
2 | 2012-02-08
2 | 2012-02-10
2 | 2012-02-14
3 | 2012-02-15
3 | 2012-02-17
3 | 2012-02-17
4 | 2012-03-01
4 | 2012-03-03
4 | 2012-03-05
4 | 2012-03-07
I tried
SELECT WEEKDAY(date) AS week, date
FROM myTable
WHERE date BETWEEN start_date AND end_date
ORDER BY week
And below is what I get
week| date
++++++++++++++++++
1 | 2012-02-01
1 | 2012-02-03
1 | 2012-02-05
1 | 2012-02-07
1 | 2012-03-01
1 | 2012-03-03
1 | 2012-03-05
1 | 2012-03-07
2 | 2012-02-08
2 | 2012-02-10
2 | 2012-02-14
3 | 2012-02-15
3 | 2012-02-17
3 | 2012-02-17
which is incorrect as 2012-03-01 onwards shows as 1 which should be 4... Let me know where I am wrong...
Thanks in advance!!!
This question is related to this
解决方案
I found solution...
Below will give me what I want...
SELECT (SELECT COUNT(distinct u2.myDate) FROM myTable u2
WHERE
u2.myDate > u1.myDate) + 1 AS week, myDate FROM myTable u1
WHERE myDate between startDate AND endDate
ordeR BY week
Update 1:
Below is what I have
myDate
+++++++++++++++++++++
2012-02-01 12:12:12
2012-02-01 12:12:12
2012-02-01 12:12:12
2012-02-03 12:12:12
2012-02-05 12:12:12
2012-02-07 12:12:12
2012-02-08 12:12:12
2012-02-08 12:12:12
2012-02-10 12:12:12
2012-02-14 12:12:12
2012-02-15 12:12:12
2012-02-17 12:12:12
2012-02-17 12:12:12
2012-03-01 12:12:12
2012-03-03 12:12:12
2012-03-05 12:12:12
2012-03-07 12:12:12
Using above query, below is what I get..
week |myDate
+++++++++++++++++++++++++++
1 |2012-03-07 12:12:12
2 |2012-03-05 12:12:12
3 |2012-03-03 12:12:12
4 |2012-03-01 12:12:12
5 |2012-02-17 12:12:12
5 |2012-02-17 12:12:12
6 |2012-02-15 12:12:12
7 |2012-02-14 12:12:12
8 |2012-02-10 12:12:12
9 |2012-02-08 12:12:12
9 |2012-02-08 12:12:12
10 |2012-02-07 12:12:12
11 |2012-02-05 12:12:12
12 |2012-02-03 12:12:12
13 |2012-02-01 12:12:12
13 |2012-02-01 12:12:12
13 |2012-02-01 12:12:12
这篇关于按日期显示周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
