如何寫此 SQL 查詢? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

如何寫此 SQL 查詢?

如何寫此 SQL 查詢?# Database - 数据库

i*a2010-09-30 07:09

1 楼

Day Open Close
1 08:00 06:00
2 08:00 06:00
3 08:00 06:00
4 08:00 06:00
5 08:00 06:00
6 09:00 05:00
7
so this is the hours of a shop
The goal is to get this into the following format:
Mon - Fri 08:00-06:00, Sat 09:00-05:00
How do I do this??

c*t2010-09-30 07:09

2 楼

google pivot query

【在 i****a 的大作中提到】

: Day Open Close
: 1 08:00 06:00
: 2 08:00 06:00
: 3 08:00 06:00
: 4 08:00 06:00
: 5 08:00 06:00
: 6 09:00 05:00
: 7
: so this is the hours of a shop
: The goal is to get this into the following format:

i*a2010-09-30 07:09

3 楼

it's not just a pivot
need to combine the dates. if M and T are the same, T-F same, S and S
same, for example, then show:
Mon - Tue 07:00-06:00, Tue - Fri 08:00-06:00, Sat - Sun 10:00-02:00
I couple pivot it into individual columns for MonOpen, MonClose etc and
use a whole bunch of case statements but that's ugly...

【在 c**t 的大作中提到】

: google pivot query

a92010-09-30 07:09

4 楼

用cursor应该比较方便了吧？

【在 i****a 的大作中提到】

i*a2010-09-30 07:09

5 楼

trying to avoid cursor and loops...

【在 a9 的大作中提到】

: 用cursor应该比较方便了吧？

j*n2010-09-30 07:09

6 楼

check "GROUPING function" in BOL see if it can work in your case.
sorry, no time to think it in deep yet...

b*g2010-09-30 07:09

7 楼

I don't think SQL is the best option for this functionality.
Are you sure SQL is the only way/tool provided?

a92010-09-30 07:09

8 楼

cursor and loops exists for a reason

【在 i****a 的大作中提到】

: trying to avoid cursor and loops...

f*h2010-09-30 07:09

9 楼

Try this one, in sqlserver 2008:
;with cte as (
select 1 as GroupID,DAY,opentime,closetime
from OfficeHours
where DAY=1

union all

select cte.GroupID,o.Day,o.OpenTime,o.CloseTime
from cte,
OfficeHours as o
where o.Day=cte.Day+1
and cte.CloseTime=o.CloseTime
and cte.OpenTime=o.OpenTime

union all

select cte.GroupID+1,o.Day,o.OpenTime,o.CloseTime
from cte,
OfficeHours as o
where o.Day=cte.Day+1

f*h2010-09-30 07:09

10 楼

If you are using Oracle 11g, just use "within group" method

i*a2010-09-30 07:09

11 楼

oh, did I not list the system I am using? It's SQL 2005. Thanks freshfish. I
'll look at the code you provided.

i*a2010-09-30 07:09

12 楼

Excellent, freshfish's code works! Thanks a bunch
I just need to do a row concatenation to get it into 1 line.

f*h2010-09-30 07:09

13 楼

Thanks, you just made me rich:)