高难的数据分组!请指教!(30分)

L

luckchen8256

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-10

• #1

表结构如下
ID(INT,IDENTITY,PK)
CTIME(Datetime NOT NULL)
DEGREE(INT,NOT NULL)
每五分钟有一条记录,每天的时间以08:00开始,即2002-01-01 08:00:00 到
2002-01-02 08:00:00为一天
如
ID CTIME DEGREE
1 2002-01-01 08:00:00 2
2 2002-01-01 08:05:00 2
.
n 2002-01-02 08:00:00 4
n+1 2002-01-02 08:05:00 4
.
希望得到如下结果:
CDATE CTIME DEGREE
2002-01-01 2002-01-01 08:00:00 2
2002-01-01 2002-01-01 08:05:00 2
.
2002-01-01 2002-01-02 08:00:00 4
2002-01-02 2002-01-02 08:05:00 4
.

 

T

tangzwei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-10

• #2

select convert(varchar,datepart(yyyy,Ctime))+'-'+right('00'+convert(varchar,datepart(mm,Ctime)),2)+'-'+right('00'+convert(varchar,datepart(dd,Ctime)),2) as cDate,
ctime,degree from 表
OK?

 

L

luckchen8256

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #3

好像不对,从今天8:00到第二天8:00才为一天

 

Y

yeskert1

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #4

to luckchen8256：
tangzwei应该对吧？！难道……
是不是每天都要包括两头的临界点，这样有部分重复，对吧？
如果这样，你希望得到结果应该是这样的：

CDATE CTIME DEGREE
2002-01-01 2002-01-01 08:00:00 2
2002-01-01 2002-01-01 08:05:00 2
.
2002-01-01 2002-01-02 08:00:00 4
2002-01-02 2002-01-02 08:00:00 4 //重复出现的行
2002-01-02 2002-01-02 08:05:00 4

如果这样，以条语句恐怕很难实现！（否则tangzwei是对的）

 

W

wp_hammer

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #5

cDATE不对。
SELECT CASE WHEN RIGHT(CONVERT(varchar,ctime),8)>='08:00:00' THEN
LEFT(CONVERT(varchar,ctime),LEN(ctime)-9) ELSE
DATEADD(DAY,1,CONVERT(DATETIME,LEFT(CONVERT(varchar,ctime),LEN(ctime)-9)))
END as cDATE,ctime,degree from 表
ok?

 

L

luckchen8256

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #6

还是不对,我要的结果和yeskert1说的差不多,只是没有重复
CDATE CTIME DEGREE
2002-01-01 2002-01-01 08:05:00 2
2002-01-01 2002-01-01 08:10:00 2
.
2002-01-01 2002-01-02 08:00:00 4
2002-01-02 2002-01-02 08:05:00 4
2002-01-02 2002-01-02 08:10:00 3

 

刁

刁么

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #7

order by CDATE,CTIME

 

P

Pearl.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #8

select convert(varchar(10), dateadd(hh, -8,ctime), 120) as cdate, ctime, degree from tablename order by cdate, ctime

 

P

Pearl.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-11

• #9

刚发觉你8:00算前一天的呀：
select convert(varchar(10), dateadd(ss, -28801, ctime), 120) as cdate, ctime, degree from tablename

 

T

tangzwei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-12

• #10

你要什么样的结果了?

 

L

luckchen8256

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-12

• #11

Pearl:你真是太厉害了!
谢谢Pearl,谢谢大家!

 

L

luckchen8256

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-12

• #12

多人接受答案了。