浏览每一个Familymember的address(按照actid号顺序),
选出从home开始、经过别的地方、且最后回到home的相关记录行。
但如果家——外边——家 不是同一个人完成的,不算。
出发地不在家,最后没有回到家,或者中途没有到home以外地方的,也不算。。拜谢啊
数据:
Family
member ActID Address Action
D-01-1 1 Home 1-0
D-01-1 2 Home 10-0
D-01-1 3 Home 9-0
D-01-1 4 Home 1-12
D-01-1 5 Resort 16-0
D-01-1 6 Home 3-0
D-01-1 7 Home 9-14
D-01-1 8 Home 1-0
D-01-2 1 Home 1-0
D-01-2 2 Home 10-0
D-01-2 3 Workplace 5-0
D-01-2 4 Workplace 3-0
D-01-2 5 Workplace 5-0
D-01-2 6 Home 3-0
D-01-2 7 Home 9-14
D-01-2 8 Home 1-0
D-02-1 1 Home 1-0
D-02-1 2 Home 10-3
D-02-1 3 Workplace 5-0
D-02-1 4 Workplace 3-0
D-02-1 5 Workplace 5-0
D-02-1 6 Home 2-0
D-02-1 7 Home 3-0
D-02-1 8 Home 14-0
D-02-1 9 Home 1-0
D-59-2 1 Home 1-0
D-59-2 2 Home 8-0
D-59-2 3 Home 9-0
D-59-2 4 Home 14-0
D-59-2 5 Home 1-0
D-59-2 6 Home 12-0
D-59-2 7 Home 3-0
D-59-2 8 Home 9-0
D-59-2 9 Home 1-0
D-60-1 1 Home 1-0
D-60-1 2 Home 10-0
D-60-1 3 Home 9-0
D-60-1 4 Home 3-0
D-60-1 5 Home 9-0
D-60-1 6 Restaurant 12-0
D-60-1 7 School 11-0
D-60-1 8 Home 2-0
D-60-1 9 Home 9-0
D-60-1 10 Home 3-0
D-60-1 11 Home 9-0
D-60-1 12 Home 1-0
D-57-1 3 Workplace 5-0
D-57-1 4 Home 7-0
D-57-1 5 Home 1-0
D-58-1 1 Home 1-0
D-58-1 2 Home 10-0
D-58-1 3 Workplace 5-0
D-58-1 4 Workplace 3-0
D-58-1 5 Workplace 5-0
D-58-1 6 Home 3-0
D-58-1 7 Home 5-0
D-58-1 8 Home 10-0
D-58-2 1 Home 1-0
D-58-2 2 Home 10-0
D-58-2 3 Home 3-0
D-58-2 4 Home 7-0
D-58-2 5 Home 3-0
D-58-2 6 Home 7-0
D-58-2 7 Home 1-0
D-58-2 8 Home 7-0
D-58-2 9 Home 10-0
D-58-2 10 Home 1-0
结果:
Family
member ActID Address Action
D-01-1 4 Home 1-12
D-01-1 5 Resort 16-0
D-01-1 6 Home 3-0
D-01-2 2 Home 10-0
D-01-2 3 Workplace 5-0
D-01-2 4 Workplace 3-0
D-01-2 5 Workplace 5-0
D-01-2 6 Home 3-0
D-02-1 2 Home 10-3
D-02-1 3 Workplace 5-0
D-02-1 4 Workplace 3-0
D-02-1 5 Workplace 5-0
D-02-1 6 Home 2-0
D-60-1 5 Home 9-0
D-60-1 6 Restaurant 12-0
D-60-1 7 School 11-0
D-60-1 8 Home 2-0
D-60-1 12 Home 1-0
D-58-1 2 Home 10-0
D-58-1 3 Workplace 5-0
D-58-1 4 Workplace 3-0
D-58-1 5 Workplace 5-0
D-58-1 6 Home 3-0
选出从home开始、经过别的地方、且最后回到home的相关记录行。
但如果家——外边——家 不是同一个人完成的,不算。
出发地不在家,最后没有回到家,或者中途没有到home以外地方的,也不算。。拜谢啊
数据:
Family
member ActID Address Action
D-01-1 1 Home 1-0
D-01-1 2 Home 10-0
D-01-1 3 Home 9-0
D-01-1 4 Home 1-12
D-01-1 5 Resort 16-0
D-01-1 6 Home 3-0
D-01-1 7 Home 9-14
D-01-1 8 Home 1-0
D-01-2 1 Home 1-0
D-01-2 2 Home 10-0
D-01-2 3 Workplace 5-0
D-01-2 4 Workplace 3-0
D-01-2 5 Workplace 5-0
D-01-2 6 Home 3-0
D-01-2 7 Home 9-14
D-01-2 8 Home 1-0
D-02-1 1 Home 1-0
D-02-1 2 Home 10-3
D-02-1 3 Workplace 5-0
D-02-1 4 Workplace 3-0
D-02-1 5 Workplace 5-0
D-02-1 6 Home 2-0
D-02-1 7 Home 3-0
D-02-1 8 Home 14-0
D-02-1 9 Home 1-0
D-59-2 1 Home 1-0
D-59-2 2 Home 8-0
D-59-2 3 Home 9-0
D-59-2 4 Home 14-0
D-59-2 5 Home 1-0
D-59-2 6 Home 12-0
D-59-2 7 Home 3-0
D-59-2 8 Home 9-0
D-59-2 9 Home 1-0
D-60-1 1 Home 1-0
D-60-1 2 Home 10-0
D-60-1 3 Home 9-0
D-60-1 4 Home 3-0
D-60-1 5 Home 9-0
D-60-1 6 Restaurant 12-0
D-60-1 7 School 11-0
D-60-1 8 Home 2-0
D-60-1 9 Home 9-0
D-60-1 10 Home 3-0
D-60-1 11 Home 9-0
D-60-1 12 Home 1-0
D-57-1 3 Workplace 5-0
D-57-1 4 Home 7-0
D-57-1 5 Home 1-0
D-58-1 1 Home 1-0
D-58-1 2 Home 10-0
D-58-1 3 Workplace 5-0
D-58-1 4 Workplace 3-0
D-58-1 5 Workplace 5-0
D-58-1 6 Home 3-0
D-58-1 7 Home 5-0
D-58-1 8 Home 10-0
D-58-2 1 Home 1-0
D-58-2 2 Home 10-0
D-58-2 3 Home 3-0
D-58-2 4 Home 7-0
D-58-2 5 Home 3-0
D-58-2 6 Home 7-0
D-58-2 7 Home 1-0
D-58-2 8 Home 7-0
D-58-2 9 Home 10-0
D-58-2 10 Home 1-0
结果:
Family
member ActID Address Action
D-01-1 4 Home 1-12
D-01-1 5 Resort 16-0
D-01-1 6 Home 3-0
D-01-2 2 Home 10-0
D-01-2 3 Workplace 5-0
D-01-2 4 Workplace 3-0
D-01-2 5 Workplace 5-0
D-01-2 6 Home 3-0
D-02-1 2 Home 10-3
D-02-1 3 Workplace 5-0
D-02-1 4 Workplace 3-0
D-02-1 5 Workplace 5-0
D-02-1 6 Home 2-0
D-60-1 5 Home 9-0
D-60-1 6 Restaurant 12-0
D-60-1 7 School 11-0
D-60-1 8 Home 2-0
D-60-1 12 Home 1-0
D-58-1 2 Home 10-0
D-58-1 3 Workplace 5-0
D-58-1 4 Workplace 3-0
D-58-1 5 Workplace 5-0
D-58-1 6 Home 3-0
study
select *
from Family f
inner join
(
select member,
count(case when address='Home' then 1 else null end) Home_count,
count(Address) Total_count
from family
group by member
having count(case when address='Home' then 1 else null end) >=2 and count(Address)>2
) t on f.member=t.member