-
ssh登陸服務(wù)器
-
執(zhí)行命令:
su - postgres
如果出現(xiàn)輸入密碼窗口， 輸入postgres
psql -U postgres -d 你的數(shù)據(jù)庫名
create unique index unique_index_articleno_chaptername ON t_chapter(articleno,chaptername);
create unique index unique_index_articlename_author ON t_article(articlename,author);
如果安裝過程是默認(rèn)的,， 你的數(shù)據(jù)庫名就是yidu,， 如果改了， 就到web目錄下進入WEB-INF/classes,， 在jdbc.properties中找到數(shù)據(jù)庫名 jdbc.url=jdbc:postgresql://127.0.0.1:5432/你的數(shù)據(jù)庫名
添加兩個唯一索引之前必須確保數(shù)據(jù)庫中不存在重復(fù)章節(jié),、重復(fù)小說
以下兩條sql分別查詢重復(fù)章節(jié)和重復(fù)小說
--查詢重復(fù)章節(jié)
select articleno,chapterno from t_chapter where chapterno in (
select max(chapterno) from t_chapter tc inner join (
select articleno ,chaptername from t_chapter
group by articleno,chaptername having count(1)>1
) tc1 on tc.chaptername = tc1.chaptername and tc.articleno = tc1.articleno
);
--查詢重復(fù)小說
select articleno,articlename from t_article where articleno in (
select max(articleno) from t_article tc inner join (
select articlename from t_article
group by articlename having count(1)>1
) tc1 on tc.articlename = tc1.articlename
);
查詢結(jié)果正確的話， 將select xxxx from 修改為 delete from 重新執(zhí)行即可,。
刪除重復(fù)章節(jié)
delete from t_chapter where chapterno in (
select max(chapterno) from t_chapter tc inner join (
select articleno ,chaptername from t_chapter
group by articleno,chaptername having count(1)>1
) tc1 on tc.chaptername = tc1.chaptername and tc.articleno = tc1.articleno
);
刪除重復(fù)小說
delete from t_article where articleno in (
select max(articleno) from t_article tc inner join (
select articlename from t_article
group by articlename having count(1)>1
) tc1 on tc.articlename = tc1.articlename
);
刪除指定小說全部章節(jié)
delete from t_chapter where articleno = 數(shù)字id;
--查詢重復(fù)章節(jié)
select * from (
select tc.articleno,tc.chapterno,row_number() over(partition by tc.chaptername order by tc.chapterno asc) mark
from t_chapter tc inner join (
select articleno ,chaptername from t_chapter
group by articleno,chaptername having count(1)>1
) tc1 on tc.chaptername = tc1.chaptername and tc.articleno = tc1.articleno
) temp where mark<>1;
--查詢重復(fù)小說
select * from (
select tc.articleno,tc.articlename,row_number() over(partition by tc.articlename order by articleno asc) mark
from t_article tc inner join (
select articlename from t_article
group by articlename having count(1)>1
) tc1 on tc.articlename = tc1.articlename
) temp where mark<>1;
