数据库内容如下:要求 将每一个成员(member)作为一列,在此列下按顺序打印出该人一天内所有发生的work活动的时间(包括起始和终止时间)
想用循环语句,问问如何才能按照“每个member”(不是family)实现上述的遍历?Family Member actionID activity start/end time
-------------------------------------------
A-01 1 1 sleeping 0:00-6:30
A-01 1 2 shower 6:30-6:45
A-01 1 3 work 8:00-12:00
A-01 1 4 .....
...
A-01 2 1 sleeping 0:00-6:30
A-01 2 2 shower 6:30-6:45
A-01 2 3 work 8:00-12:00
A-01 2 4 .....
...
A-01 3 1 sleeping 0:00-6:30
A-01 3 2 shower 6:30-6:45
A-01 3 3 work 8:00-12:00
A-01 3 4 .....
...
A-01 3 18 sleeping 23:00-24:00
A-02 1 1 sleeping 0:00-6:30
A-01 1 2 shower 6:30-6:45
A-01 1 3 work 8:00-12:00
A-01 1 4 .....
想用循环语句,问问如何才能按照“每个member”(不是family)实现上述的遍历?Family Member actionID activity start/end time
-------------------------------------------
A-01 1 1 sleeping 0:00-6:30
A-01 1 2 shower 6:30-6:45
A-01 1 3 work 8:00-12:00
A-01 1 4 .....
...
A-01 2 1 sleeping 0:00-6:30
A-01 2 2 shower 6:30-6:45
A-01 2 3 work 8:00-12:00
A-01 2 4 .....
...
A-01 3 1 sleeping 0:00-6:30
A-01 3 2 shower 6:30-6:45
A-01 3 3 work 8:00-12:00
A-01 3 4 .....
...
A-01 3 18 sleeping 23:00-24:00
A-02 1 1 sleeping 0:00-6:30
A-01 1 2 shower 6:30-6:45
A-01 1 3 work 8:00-12:00
A-01 1 4 .....
-------------------------------------------
A-01 1 1 sleeping 0:00-6:30
A-01 1 2 shower 6:30-6:45
A-01 1 3 work 8:00-12:00
A-01 1 4 .....
...
A-01 2 1 sleeping 0:00-6:30
A-01 2 2 shower 6:30-6:45
A-01 2 3 work 8:00-12:00
A-01 2 4 .....
...
A-01 3 1 sleeping 0:00-6:30
A-01 3 2 shower 6:30-6:45
A-01 3 3 work 8:00-12:00
A-01 3 4 .....
...
A-01 3 18 sleeping 23:00-24:00
A-02 1 1 sleeping 0:00-6:30
A-02 1 2 shower 6:30-6:45
A-02 1 3 work 8:00-12:00
A-02 1 4 ..... Family:每个家庭
Member:该家庭内的不同成员
actionID:该成员活动发生的顺序号
activity: 活动内容
数据有了变化,更正一下,要求是这样的
Family Member activity starttime endtime
-------------------------------------------
A-01 1 sleeping 0:00 6:30
A-01 1 shower 6:30 6:45
A-01 1 work 8:00 12:00
A-01 1 read 12.00 24.00
A-01 2 sleeping 0:00-6:30
A-01 2 shower 6:30-6:45
A-01 2 work 8:00-12:00
A-01 2 eat 12.00-13.00
A-01 2 work 13.00-20.00
A-02 1 sleeping 0:00-6:30
A-02 1 shower 6:30-6:45
A-02 1 work 8:00-12:00
A-02 1 .....
希望得到结果;
hour Familyid memberid activity
6.00 A-01 1 sleeping
7.00 A-01 1 《null》
8.00 A-01 1 work
9.00 A-01 1 work
10.00 A-01 1 work
11.00 A-01 1 work
(…………一直到24.00)
即根据数据 按每个整点查询大家都在干什么。
多谢高手指点一下编程思路!
时间有限也没有环境这只是个思路我也不知道对不对,希望对你有帮助
或是while语句
-- Author: happyflsytone
-- Date:2008-10-12 19:19:35
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(Family NVARCHAR(4),Member INT,activity NVARCHAR(8),starttime varchar(10),endtime varchar(10))
Go
INSERT INTO ta
SELECT 'A-01',1,'sleeping','0:00','6:30' UNION ALL
SELECT 'A-01',1,'shower','6:30','6:45' UNION ALL
SELECT 'A-01',1,'work','8:00','12:00' UNION ALL
SELECT 'A-01',1,'read','12:00','23:59' UNION ALL
SELECT 'A-01',2,'sleeping','0:00','6:30' UNION ALL
SELECT 'A-01',2,'shower','6:30','6:45' UNION ALL
SELECT 'A-01',2,'work','8:00','12:00' UNION ALL
SELECT 'A-01',2,'eat','12:00','13:00' UNION ALL
SELECT 'A-01',2,'work','13:00','20:00' UNION ALL
SELECT 'A-02',1,'sleeping','0:00','6:30' UNION ALL
SELECT 'A-02',1,'shower','6:30','6:45' UNION ALL
SELECT 'A-02',1,'work','8:00','12:00'
GO
--Start
SELECT ltrim(rowid)+'.00' as [hour],family,member,
(select top 1 activity from ta where family = a.family and cast( '2008-01-01 '+ltrim(rowid)+':01' as datetime) between dateadd(mi,1,cast('2008-01-01 '+starttime as datetime)) and
cast('2008-01-01 '+endtime as datetime)) as activity
FROM (select distinct family ,member from ta) a
cross join (select rowid = row_number() over (order by object_id)
from sys.objects
) b
where rowid between 6 and 23
order by family,member
--Result:
/*hour family member activity
--------------------------- ------ ----------- --------
6.00 A-01 1 sleeping
7.00 A-01 1 NULL
8.00 A-01 1 work
9.00 A-01 1 work
10.00 A-01 1 work
11.00 A-01 1 work
12.00 A-01 1 read
13.00 A-01 1 read
14.00 A-01 1 read
15.00 A-01 1 read
16.00 A-01 1 read
17.00 A-01 1 read
18.00 A-01 1 read
19.00 A-01 1 read
20.00 A-01 1 read
21.00 A-01 1 read
22.00 A-01 1 read
23.00 A-01 1 read
6.00 A-01 2 sleeping
7.00 A-01 2 NULL
8.00 A-01 2 work
9.00 A-01 2 work
10.00 A-01 2 work
11.00 A-01 2 work
12.00 A-01 2 read
13.00 A-01 2 read
14.00 A-01 2 read
15.00 A-01 2 read
16.00 A-01 2 read
17.00 A-01 2 read
18.00 A-01 2 read
19.00 A-01 2 read
20.00 A-01 2 read
21.00 A-01 2 read
22.00 A-01 2 read
23.00 A-01 2 read
6.00 A-02 1 sleeping
7.00 A-02 1 NULL
8.00 A-02 1 work
9.00 A-02 1 work
10.00 A-02 1 work
11.00 A-02 1 work
12.00 A-02 1 NULL
13.00 A-02 1 NULL
14.00 A-02 1 NULL
15.00 A-02 1 NULL
16.00 A-02 1 NULL
17.00 A-02 1 NULL
18.00 A-02 1 NULL
19.00 A-02 1 NULL
20.00 A-02 1 NULL
21.00 A-02 1 NULL
22.00 A-02 1 NULL
23.00 A-02 1 NULL(54 行受影响)
*/
--End
执行程序时出错(sql2000查询分析器)????
服务器: 消息 195,级别 15,状态 10,行 6
'row_number' 不是可以识别的 函数名。
(select top 1 activity from ta where family = a.family 是不是缺少了member = a.member?