-
ssh登陸服務(wù)器
-
執(zhí)行命令:
su - postgres
如果出現(xiàn)輸入密碼窗口,， 輸入postgres
psql -U postgres -d 你的數(shù)據(jù)庫(kù)名
create unique index unique_index_articleno_chaptername ON t_chapter(articleno,chaptername);
create unique index unique_index_articlename_author ON t_article(articlename,author);
如果安裝過(guò)程是默認(rèn)的,， 你的數(shù)據(jù)庫(kù)名就是yidu,， 如果改了， 就到web目錄下進(jìn)入WEB-INF/classes,， 在jdbc.properties中找到數(shù)據(jù)庫(kù)名 jdbc.url=jdbc:postgresql://127.0.0.1:5432/你的數(shù)據(jù)庫(kù)名
添加兩個(gè)唯一索引之前必須確保數(shù)據(jù)庫(kù)中不存在重復(fù)章節(jié),、重復(fù)小說(shuō)
以下兩條sql分別查詢重復(fù)章節(jié)和重復(fù)小說(shuō)
--查詢重復(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ù)小說(shuō)
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ù)小說(shuō)
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
);
刪除指定小說(shuō)全部章節(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ù)小說(shuō)
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;
