"DDBC convert sql"의 두 판 사이의 차이
DH 교육용 위키
잔글 (Hiblue 사용자가 DDBCconvert.sql 문서를 DDBC convert.sql 문서로 옮겼습니다) |
|
(차이 없음)
| |
2018년 8월 10일 (금) 14:38 판
use hiblue
select * from t_month order by last
--dhtime JDDate
--ddbc Month
--month time:after month
select distinct '<http://authority.dlia.edu.tw/time#mon'+a.id+'>' as source, '<http://www.w3.org/2006/time#after>' as relation, '<http://authority.dlia.edu.tw/time#mon'+b.id+'>' as target from t_month a, t_month b where a.last+1=b.first order by source
--month time:before month
select distinct '<http://authority.dlia.edu.tw/time#mon'+a.id+'>' as source, '<http://www.w3.org/2006/time#after>' as relation, '<http://authority.dlia.edu.tw/time#mon'+b.id+'>' as target from t_month a, t_month b where a.last+1=b.first order by source
--month label
select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+month_name+'"'+'@hanja' from t_month --질문!! 한자가 맞는가?
select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+month+'"^^xsd:nonNegativeInteger' from t_month
--month hasBeginning day
select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+first+'>' from t_month
--month hasEnd day
select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+last+'>' from t_month
-- month inside day (inverse of 'month') (예상)
select distinct '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>' from t_month, t_day where t_month.first <= t_day.id and t_month.last >= t_day.id
--select * from t_month where id in (10005, 10009, 10010, 10011, 10012, 10013)
-- month days (월일수)
select '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://www.w3.org/2006/time#days>','"'+cast(cast(last as int)-cast(first as int) as nvarchar)+'"^^xsd:nonNegativeInteger' from t_month
-- month leap (윤달)
select '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#leap>','"'+cast(leap_month as nvarchar)+'"^^xsd:nonNegativeInteger' from t_month
--ddbc Year
--create t_year
create table t_year (id INT IDENTITY(1,1) not null, eraNumber int, era_id int, ganzhi nvarchar(20), first int, last int)
insert into t_year select year as eraNumber, era_id, ganzhi, min(first) as first, max(last) as last from t_month group by year, era_id, ganzhi order by first
--connect year month
update t_month set year_id=a.year_id from (select t_month.id as month_id, t_year.id as year_id from t_year,t_month where t_month.year=t_year.eraNumber and t_month.era_id=t_year.era_id and t_month.ganzhi=t_year.ganzhi) a where a.month_id=t_month.id
--select year, era_id, ganzhi, first, last, cast(last as int) - cast(first as int) from t_year
--select * from t_month where id in (83069, 83070, 83071, 83068, 83067)
--year hasBeginning day
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(t_year.first as nvarchar)+'>' from t_year
--year hasEnd day
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(t_year.last as nvarchar)+'>' from t_year
--year inside day(inverse of 'year') (예상)
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>' from t_year, t_day where t_day.id<=t_year.last and t_day.id>=t_year.first
select '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#year>', '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>' from t_year, t_day where t_day.id<=t_year.last and t_day.id>=t_year.first
--year days (연일수)
select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(t_year.last-t_year.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from t_year
--year hasBeginning month
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_year.first=t_month.first
--year hasEnd month
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_year.last=t_month.last
--year-inside-month (inverse of year)
select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_month.year_id=t_year.id
--year before year
select '<http://authority.dlia.edu.tw/time#year'+cast(a.id as nvarchar)+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from t_year a, t_year b where a.first-1=b.last
--year-after-year
select '<http://authority.dlia.edu.tw/time#year'+cast(a.id as nvarchar)+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from t_year a, t_year b where a.last+1=b.first
--year - era Era
select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#era>','<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>' from t_year
--year - eraNumber
select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#eraNum>','"'+cast(eraNumber as nvarchar)+'"^^xsd:nonNegativeInteger' from t_year
--year ganzhi
select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>','"'+ganzhi+'"^^xsd:string@hanja' from t_year
--ddbc era
update t_era_names set name=ltrim(rtrim(name))
--era-label
select '<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_era_names, c_languages where t_era_names.language_id=c_languages.id
--왕년/시연호 = 연호를 쓰지 않는 경우! 그대로 둘것인가 삭제?? 고민여부 - 번역원은 값을 넣지 않음
--select distinct eraName, emperor.name from era, emperor where eraName like '%(%' and era.emperor_id=emperor.emperor_id
--era- hasBeginning day
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(a.first as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a order by source
--era hasEnd day
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(a.last as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a order by source
--era inside day (inverse of era)
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_day where t_era.first <= t_day.id and t_era.last >= t_day.id
--era days (연호일수)
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a,
--era hasBeginning year
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
--era hasEnd year
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.last=b.last and a.era_id=b.era_id
--era inside year (inverse of era)
select '<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>' from t_year
--era Beginning month
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(b.id as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
--era hasEnd month
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(b.id as nvarchar)+'>' from
(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
--era inside month (inverse of era)
select '<http://authority.dlia.edu.tw/time#era'+t_era.id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
t_month,t_year,t_era where t_month.year_id=t_year.id and t_year.era_id=t_era.id
--era before era
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#era'+b.era_id+'>' from (select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, (select era_id, min(first) as first, max(last) as last from t_month group by era_id) b where a.first-1=b.last
--era after era
select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#era'+b.era_id+'>' from (select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, (select era_id, min(first) as first, max(last) as last from t_month group by era_id) b where a.last=b.first+1
--emporor
--emperor label
select '<http://authority.dlia.edu.tw/time#emperor'+cast(emperor_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_emperor_names, c_languages where t_emperor_names.language_id=c_languages.id
--emperor hasbeginning day
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(a.first as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a --685
--select emperor_id from t_year,t_era where t_year.era_id=t_era.id group by emperor_id 매칭갯수확인 685
--emperor hasEnd day
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(a.last as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a
--emperor inside day (inverse of emperor)
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a , t_day where t_day.id<=a.last and t_day.id>=a.first
--emperor days(재위일수)
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a
--emperor hasbeginning month
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emperor hasEnd month
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emporor inside month (inverse of emperor)
select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from t_era, t_month where t_month.era_id=t_era.id
--emperor hasBeginning year
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emporor hasEnd year
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emperor inside year (inverse of emperor)
select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from t_era, t_month where t_month.era_id=t_era.id
--emperor hasBeginning era
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emperor hasEnd era
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from
(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
--emperor inside era (inverse of emperor)
select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from t_era
--emperor before emperor
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#emperor'+b.id+'>' from (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) a, (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) b where a.first-1+1=b.last and a.dynasty_id=b.dynasty_id
--emperor after emperor
select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#emperor'+b.id+'>' from (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) a, (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) b where a.last+1=b.first and a.dynasty_id=b.dynasty_id
--dynasty
--dynasty label
select '<http://authority.dlia.edu.tw/time#dyansty'+cast(dynasty_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_dynasty_names, c_languages where t_dynasty_names.language_id=c_languages.id
--dynasty nation
select '<http://authority.dlia.edu.tw/time#dyansty'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#nation>', '"'+type+'"^^xsd:string' from t_dynasty
--dynasty hasbeginning day
--create view t_rel as select distinct t_month.id as month_id,t_year.id as year_id, emperor_id, dynasty_id, t_month.first, t_month.last from t_year,t_era,t_emperor,t_month where t_year.era_id=t_era.id and t_emperor.id=t_era.emperor_id and t_month.year_id=t_year.id
select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(min(first) as nvarchar)+'>' from t_rel group by dynasty_id
--dynasty hasEnd day
select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(max(last) as nvarchar)+'>' from t_rel group by dynasty_id
--dynasty inside day
select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_day where t_day.id<=a.last and t_day.id>=a.first
--dynasty days
select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a
--dynasty hasBeginning month
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#month'+cast(b.month_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
--dynasty hasEnd month
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#month'+cast(b.month_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
--dyansty insdie month
select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#month'+cast(month_id as nvarchar)+'>' from t_rel
--dynasty hasBeginning year
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(b.year_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
--dynasty hasEnd year
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(b.year_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
--dyansty inside year
select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(year_id as varchar)+'>' from t_rel
--dynasty hasBeginning era
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(b.era_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
--dynasty hasEnd era
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(b.era_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
--dyansty inside era
select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(era_id as varchar)+'>' from t_rel
--dynasty hasBeginning emperor
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(b.emperor_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
--dynasty hasEnd emperor
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(b.emperor_id as nvarchar)+'>' from
(select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
--dyansty inside emperor
select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(emperor_id as varchar)+'>' from t_rel
--dynasty before dynasty
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#dyansty'+b.dynasty_id+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) b where a.first-1=b.last
--dynasty after dynasty
select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#dyansty'+b.dynasty_id+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) b where a.last+1=b.first
--따로 노는 왕조들;;;
--select * from t_dynasty_names where dynasty_id in (0,1,2,3,4,5,6,7,8,9,20,21,23,24,25,26,27,30,33,38,77,80,84,94,95,97,98,99,100,101,102,107,120)
--class정의
--day class Day
select '<http://authority.dlia.edu.tw/time#day'+id+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://authority.dlia.edu.tw/time#Day>' from t_day
--month class Month
select '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Month>' from t_month
--year class Year
select '<http://authority.dlia.edu.tw/time#year'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Year>' from t_year
--emperor class Emperor
select '<http://authority.dlia.edu.tw/time#emperor'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Emperor>' from t_emperor
--era class Era
select '<http://authority.dlia.edu.tw/time#era'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Era>' from t_era
--dynasty class Dynasty
select '<http://authority.dlia.edu.tw/time#dynasty'+a.id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#month>' from t_dynasty
