17,140
社区成员




create table mdsets_bbs_users
(
user_id varchar2(8) not null, --pk
user_name varchar2(45) ,--pk
pass_word varchar2(20) ,
email varchar2(30) ,
name varchar2(30) ,
phone_no varchar2(20) ,
person_info varchar2(900) ,
sex varchar2(1) default 'O' , --F╧ M O:玂盞
birth_day date null,
web_site varchar2(50) ,
QQ varchar2(20) ,
MSN varchar2(50) ,
group_id varchar2(1) default 1 , --default:1炊硄ノめ23恨瞶
last_login_date date default sysdate ,--Ω祅嘲丁
last_password_date date default sysdate ,--Ωэ盞絏丁
post_qty number default 0 ,--祇┇计
post_pretty_qty number default 0 ,
score number default 0 ,--緔 mdsets_bbs_score_rule
psw varchar2(32) ,--盞盞絏
user_photo varchar2(20) ,
creator varchar2(20) ,--default user_name not null,--承
creation_date date default sysdate ,
updater varchar2(20) not null,--default user_name not null,--э
update_date date default sysdate not null, --эら戳
post_ip varchar2(30) not null, --爹ip
constraint mdsets_bbs_users_pk primary key(user_id,user_name) --羛龄粂猭
)
create table mdsets_bbs_plate
(
plate_group number(1) default 1 , --plate竤舱 纐粄1
plate_id number not null,
plate_name varchar2(90),
enabled varchar2(1) , --Y OR N
creator varchar2(20),
creation_date date ,
updater varchar2(20),
update_date date ,
constraint mdsets_bbs_plate_pk primary key(plate_id)
)
create table mdsets_bbs_sub_plate
(
plate_id number,
sub_plate_id number not null,
sub_plate_name varchar2(90),
sub_plate_description varchar2(240),
--todat_qty number default 0 not null,
post_qty number default 0 ,
reply_qty number default 0 ,
last_content_id number,
enabled varchar2(1) ,-- Y OR N
creator varchar2(20) ,
creation_date date,
updater varchar2(20) ,
update_date date ,
constraint mdsets_bbs_sub_plate_pk primary key(sub_plate_id)
-- constraint mdsets_bbs_sub_plate_fk foreign key(plate_id) references mdsets_bbs_plate(plate_id)
)
create table mdsets_bbs_plate_boss
(
sub_plate_id number ,
user_id varchar2(8),
creator varchar2(20) ,
creation_date date,
updater varchar2(20) ,
update_date date
--constraint mdsets_bbs_plate_boss_fk primary key(user_id)
)
create table mdsets_bbs_content
(
sub_plate_id number not null,
content_id number,
sequences number(1) default 2, --纔1:top,2:common
mood varchar2(10),
topic varchar2(240) , --程80簙
bbs_category varchar2(90) ,
bbs_type varchar2(90),
content clob ,
attachment varchar2(90) null,
browse_qty number default 0 ,
reply_qty number default 0 ,
up_qty number default 0 ,
down_qty number default 0 ,
last_reply_id number,
enabled varchar2(1), --y or n ,
creator varchar2(20),
creation_date date ,
updater varchar2(20),
update_date date ,
post_ip varchar2(30) ,
top_date date, --竚郴ら
top_date_qty date ,----纐粄竚郴ら戳程竚郴计10
is_pretty varchar2(1) default 'N' ,
constraint mdsets_bbs_content_pk primary key(sub_plate_id,content_id)
)
select
a.plate_name ,
b.sub_plate_name,
b.sub_plate_description,
b.post_qty,
b.reply_qty,
c.topic,
c.updater,
c.update_date,
d.* ,
e.版主字段名字
from
mdsets_bbs_plate a
left join
mdsets_bbs_sub_plate b on a.plate_id = b.plate_id
inner join
mdsets_bbs_content c on b.sub_plate_id = c.sub_plate_id
inner join
mdsets_bbs_plate_boss d on c.sub_plate_id=d.sub_plate_id
inner join mdsets_bbs_users e on d.user_id = e.user_id
where e.group_id=2