basic_memo

잠깐 저장 .. sql문

오동순이 2023. 4. 3. 14:27

security_test_db

create database security_test_db;
use security_test_db;

create table member_info (
	mi_seq int not null auto_increment primary key,
	mi_id varchar(20) not null,
	mi_pwd varchar(128) not null,
	mi_name varchar(20) not null,
	mi_nickname varchar(20) not null,
	mi_staus int not null default 1,
	mi_reg_dt datetime not null default current_timestamp,
	mi_role varchar(10) not null default 'USER'
);

 

second_project

create table member_info (
mi_seq	bigint	not null	auto_increment	primary key,
mi_email	varchar(255)	null		,
mi_pwd	varchar(255)	null		,
mi_token	varchar(255)	null		,
mi_sns_type	varchar(255)	null		,
mi_target_amount	int	null		
);

create table target_area_info (
tai_seq	bigint	not null	auto_increment	primary key,
tai_name	varchar(255)	not null	,	
tai_min_cost	int	not null		,
tai_max_cost	int	not null		
);

create table category_info (
cate_seq	bigint	not null	auto_increment	primary key,
cate_name	varchar(50)	not null		
);

create table comment_info (
ci_seq	bigint	not null	auto_increment	primary key,
ci_mi_seq	int	not null,
ci_content	text	not null	,	
ci_reg_dt	datetime	not null	default current_timestamp	,
ci_edit_dt	datetime	null	,	
ci_ci_seq	bigint	null
);

-- create table expenses_detail (
-- ed_seq	bigint	not null	auto_increment	primary key,
-- ed_mi_seq	bigint	not null,
-- ed_title	varchar(100)	not null	,	
-- ed_ci_seq	bigint	not null,
-- ed_date	date not null ,
-- ed_amount	int	not null	,	
-- ed_commnet	int	null		
-- );

-- 회원 더미 데이터
INSERT INTO second_project.member_info
(mi_email, mi_pwd, mi_token, mi_sns_type, mi_target_amount)
VALUES
('user1@green.com', '123456', null, null, 450000),
('user2@green.com', '123456', null, null, 150000),
('user3@green.com', '123456', null, null, 1450000),
('user4@green.com', '123456', null, null, 950000),
('user5@green.com', '123456', null, null, 2450000);

-- 카테고리 더미 데이터
INSERT INTO second_project.category_info
(cate_name)
VALUES
('이름'),('식비'),('공과금'),('뷰티/미용'),('의류'),
('반려동물'),('교육'),('보험/금융'),('경조사/회비'),('의료/건강'),
('문화/예술'),('교통/차량'),('기타');

INSERT INTO second_project.expenses_detail
(ed_mi_seq, ed_title, ed_cate_seq, ed_date, ed_amount, ed_commnet)
VALUES
(1, '식비지출', 2, '2022-01-03', 23000, null),
(1, '공과금', 3, '2022-01-03', 26000, '공과금많이나옴'),
(1, '화장품', 4, '2022-01-03', 2500, '화장솜 삼'),
(2, '식비지출', 2, '2022-01-03', 8000, '돈까스먹음'),
(2, '티셔스구입', 5, '2022-01-03', 8000, null),
(2, '식비지출', 2, '2022-01-03', 8000, '국밥'),
(3, '식비', 2, '2022-01-03', 85000, null),
(3, '식비', 2, '2022-01-03', 8000, '햄버거 먹음'),
(3, '경조사비', 9, '2022-01-03', 100000, '결혼식축의금'),
(4, '식비지출', 2, '2022-01-03', 8000, '스시먹움'),
(4, '의료비', 10, '2022-01-03', 80000, null),
(4, '영화비', 11, '2022-01-03', 99000, '영화봄'),
(5, '영화비', 11, '2022-01-03', 99000, '여자친구랑 영화봄'),
(5, '교통비', 12, '2022-01-03', 33000, '차기름값'),
(5, '기타', 13, '2022-01-03', 15000, null);

-- alter table member_info add mi_status int not null default 1;

-- 해당 테이블 컬럼 삭제
--  ALTER TABLE member_info  DROP mi_phone;
--  ALTER TABLE expenses_detail DROP ed_commnet;

-- 월별  지출 내역
select ed_title , ed_amount, date_format(ed_date, '%y-%m-%d') 
from expenses_detail 
where ed_mi_seq = 1; 

-- 월별 지출 내역 2
select date_format(ed_date, '%y-%m') m, sum(ed_amount) 
from expenses_detail ed where ed_mi_seq = 1 
group by m order by m desc;

-- 월별 지출 내역 2
select mi.mi_seq ,mi.mi_nickname, mi.mi_email, date_format(ed_date, '%y-%m') m, sum(ed_amount) 
from expenses_detail ed 
left join member_info mi on mi.mi_seq = ed.ed_mi_seq 
where ed_mi_seq = 1 
group by m order by m desc;

SELECT * FROM expenses_detail e join  member_info m on m.mi_seq = e.ed_mi_seq  
join expenses_detail ed  c on 
join e.edCateSeq s WHERE e.edMiSeq = :member AND e.edSeq = :edSeq AND e.edCateSeq = :cate

-- 회원의 한달단위 지출 내역 리스트 출력
-- 1차 시기
select ed.ed_seq, ed.ed_title , ed.ed_cate_seq, ci.cate_name, ed.ed_amount , date_format(ed_date, '%y-%m-%d') m 
from expenses_detail ed
join category_info ci on ci.cate_seq = ed.ed_cate_seq
where ed.ed_mi_seq = 1 
group by m order by m desc;

-- SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL + 2 MONTH), '%m' ),
--        DATE_FORMAT( DATE_ADD(NOW(), INTERVAL + 1 MONTH), '%m' ) 
--        DATE_FORMAT( NOW(), '%m' )
-- 2차 시기
select date_format(date_add(now(), interval + 2 month), '%m'),
date_format(now(), '%m');

-- 이거를 월별 리스트에 넣어야 함.
select ed.ed_seq ,ed_mi_seq ,ed.ed_title ,ci.cate_seq ,ci.cate_name,ed.ed_amount ,ed_date ,date_format(ed_date, '%m') m  from expenses_detail ed join category_info ci on ci.cate_seq = ed.ed_cate_seq 
where ed.ed_mi_seq = 1 and date_format(date_add(ed_date, interval + 0 month), '%m')= 12 order by m desc;

select * from expenses_detail ed where ed.ed_mi_seq =1 and ed.ed_date between '2023-01-01' and '2023-01-31' order by ed_date desc ;
-- 회원의 최근 지출내역 3개만 출력

-- 날짜범위 내 지출 리스트 출력
select * from expenses_detail ed where ed_date >= '2023-02-01' and ed_date <= '2023-02-28'
-- 해당 카테고리에 지출을 등록한 회원이 몇명이 있는지 계산 (중복제거)
select count(distinct(ed_mi_seq)) from expenses_detail ed where ed_cate_seq =1 and ed_date between '2023-02-01' and '2023-02-28'
-- 1번 회원이 사용한 지출내역에서 2023-02-01~2023-02-28 사이의 내역 중 지출날짜와 카테고리가 동일한 내역을 하나로 묶어서 합계와 날짜, 카테고리를 출력
select sum(ed_amount), ed_date, ed_cate_seq from expenses_detail ed where ed_mi_seq =1 and ed_date between '2023-02-01' and '2023-02-28' group by ed_date , ed_cate_seq


select * from expenses_detail ed where ed.ed_mi_seq =1 and ed.ed_date between '2023-01-01' and '2023-01-31' order by ed_date desc limit 3 -- top3
findTop3ByMiSeqAndEdDateBetweenOrderByEdDateDESC(member)

select * from comment_info where ci_bi_seq =3;

select * from expenses_detail where ed_mi_seq = 1 and ed_seq = 872;

create table payment_info (
	pi_seq int not null auto_increment primary key,
	pi_name varchar(100) not null
);

update expenses_detail set ed_sat=6

select * from board_image bi where bimg_uri = 'ec9a92e377e47d1b78dd06a1cd9bf933_res';

 

lms_db

use lms_db;

create table member_basic	(
mb_seq	bigint	not null	auto_increment	primary key,
mb_id	varchar(255)	not null	,
mb_pwd	varchar(255)	not null	,
mb_name	varchar(255)	not null	,
mb_email	varchar(255)	not null	,
mb_type	varchar(10)	not null
);

create table student (
stu_subject	varchar(255)	not null,
stu_grade	int	not null
);

create table professor (
prof_subject	varchar(255)	not null
);

create table staff (
staff_work	varchar(255)	not null
);

-- 교수-강의-학생리스트 조회
select * from professor p ; -- mb_seq 6
select * from professor p 
join lecture_info lec on p.mb_seq = lec.li_mb_seq; -- 강의 

select * from class_register cr ;

-- 기준:강의 교수, 강의 이름, 학생이름
-- INSERT INTO lms_db.class_register
-- (cr_li_seq, cr_mb_seq, cr_fg_seq)
-- VALUES(1, 6, null); 교수 넣음

select mb_Seq, prof_subject from professor p;

select *  
from professor p 
join class_register cr on p.mb_seq = cr.cr_mb_seq
left outer join student stu on cr.cr_mb_seq = stu.mb_seq;
where p.mb_seq = 6;

-- jpql ok jpa x native ok, view ok
select * from
(
	select * from
	class_register where cr_li_seq = 
	(
		select cr_li_seq from class_register a 
		join professor b on a.cr_mb_seq = b.mb_seq
		join member_basic c on b.mb_seq = c.mb_seq
	)
) a inner join member_basic b on a.cr_mb_seq = b.mb_seq
where mb_type = 'S';

-- view 만들기 
-- create view lectureStudent  as
-- select * from
-- (
-- 	select * from
-- 	class_register where cr_li_seq = 
-- 	(
-- 		select cr_li_seq from class_register a 
-- 		join professor b on a.cr_mb_seq = b.mb_seq
-- 		join member_basic c on b.mb_seq = c.mb_seq
-- 	)
-- ) a inner join member_basic b on a.cr_mb_seq = b.mb_seq
-- where mb_type = 'S';

-- 서브쿼리에서는 중심이 되는 테이블을 기준으로 적용시킨다.
select * from class_register cr ;

-- 교수에 해당하는 강의 번호 찾기

select cr_li_seq from class_register cr
join professor p on p.mb_seq = cr.cr_mb_seq 
join member_basic mb on p.mb_seq = mb.mb_seq; 

-- li_seq 가 같은 번호만 찾기
select * from class_register where cr_li_seq =
	(
	select cr_li_seq from class_register cr
	join professor p on p.mb_seq = cr.cr_mb_seq 
	join member_basic mb on p.mb_seq = mb.mb_seq
	);


-- 내가 원하는 정보만 나오게 하기! 근데 mb_seq는 안나옴!
-- cr_li_seq, mb_seq, mb_id, mb_name, stu_subject, stu_grade
select cr_li_seq, mb_id, mb_name, stu_subject, stu_grade from
(
	select * from (
		select *  from class_register where cr_li_seq =
			(
			select cr_li_seq from class_register cr
			join professor p on p.mb_seq = cr.cr_mb_seq 
			join member_basic mb on p.mb_seq = mb.mb_seq
			)
	) a inner join member_basic b on a.cr_mb_seq = b.mb_seq 
	where mb_type = 'S'
) a join student s on s.mb_seq = a.cr_mb_seq;

-- 요기 지금 날라감

select li_seq, li_name, mb.mb_name  from lecture_info li 
join member_basic mb on li.li_mb_seq = mb.mb_seq where mb.mb_type ='P'
join class_register cr on cr.cr_li_seq = li.li_seq ;

-- 강의-교수
select li_seq, li_name, mb.mb_name, cr.cr_li_seq from lecture_info li 
join member_basic mb on li.li_mb_seq = mb.mb_seq 
join class_register cr on cr.cr_li_seq = li.li_seq 
;

-- 수강-학생
select cr.cr_li_seq , cr.cr_mb_seq, mb.mb_name , mb.mb_type, s.stu_subject , s.stu_grade  from class_register cr
join member_basic mb on mb.mb_seq = cr.cr_mb_seq 
join student s on s.mb_seq = mb.mb_seq 
;

-- 두개 합치기

-- 수강-학생-강의
select li.li_name , li.li_mb_seq , cr.cr_li_seq , cr.cr_mb_seq, mb.mb_name , mb.mb_type, s.stu_subject , s.stu_grade  from class_register cr
join member_basic mb on mb.mb_seq = cr.cr_mb_seq 
join student s on s.mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
;

-- 수강-학생-강의-교수
select mb2.mb_name , li.li_name , li.li_mb_seq , cr.cr_li_seq , cr.cr_mb_seq, mb.mb_name , mb.mb_type, s.stu_subject , s.stu_grade  from class_register cr
join member_basic mb on mb.mb_seq = cr.cr_mb_seq 
join student s on s.mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
join member_basic mb2 on li.li_mb_seq = mb2.mb_seq
where cr.cr_li_seq = 1
;

select mb2.mb_seq as pro_seq,mb2.mb_name as pro , li.li_name , li.li_mb_seq , li.li_code , cr.cr_li_seq , mb.mb_seq as stu_seq, mb.mb_name as stu, mb.mb_id ,s.stu_subject , s.stu_grade  from class_register cr
join member_basic mb on mb.mb_seq = cr.cr_mb_seq 
join student s on s.mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
join member_basic mb2 on li.li_mb_seq = mb2.mb_seq
;
-- 조회는 컬럼 중복 괜찮은데

-- view는 컬럼중복 불가 이기 때문에 as로 겹치는 곳 수정하고, entity로 만들때 as 의 이름을 사용해야 한다
create view lectureStudentDAO as
select mb2.mb_seq as pro_seq,mb2.mb_name as pro , li.li_name , li.li_mb_seq , li.li_code , cr.cr_li_seq as li_seq , mb.mb_seq, mb.mb_name as stu, mb.mb_id ,s.stu_subject , s.stu_grade  from class_register cr
join member_basic mb on mb.mb_seq = cr.cr_mb_seq 
join student s on s.mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
join member_basic mb2 on li.li_mb_seq = mb2.mb_seq
;
drop view lecturestudentdao;

-- stuId20201025
-- stuName김그린
-- stuGrade2
-- stuSubject컴퓨터공학과
-- list
-- scoreCateSeq1
-- scoreCateName출석
-- attendCount10
-- attendCountTotal30

--  
-- 내 강의 수강생 성적 조회 : 출석/기말/중간/과제/[최종성적]
select ss.ss_seq ,ss.ss_li_seq,li.li_name  from score_standard ss 
join lecture_info li on li.li_seq = ss.ss_li_seq 
group by li_seq;

-- * 중요
-- 
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select ss.sstu_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq , mb.mb_name , sstan.ss_li_seq 
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq 
where mb.mb_seq = 1 ;

-- * 중요2- fg-name추가
-- 
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select ss.sstu_seq, ss.sstu_score , sm.smas_name , sm.smas_score ,mb.mb_seq , mb.mb_name , sstan.ss_li_seq  , li.li_name , sc.sc_name , sc.sc_seq 
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
where mb.mb_seq = 1 ;

-- 발전 시키기 필요없는 부분 삭제, 스코어 카테 번호, 카테이름,점수, 최종점수
-- 최종성적 테이블
select * from final_grade fg;
-- 학번 이름 학년 학과
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select distinct sm.smas_name , sm.smas_date , sm.smas_score , mb.mb_name , ss.sstu_score 
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join score_standard sstan on sstan.ss_seq = sm.smas_ss_seq  
join lecture_info li on sstan.ss_li_seq = li.li_seq 
join class_register cr on li.li_seq = cr.cr_li_seq 
join member_basic mb on cr.cr_mb_seq = mb.mb_seq
where ss.sstu_mb_seq = 1 ;

-- 주영님 수정
select ss2.sstu_seq, ss2.sstu_score , sm.smas_name , sm.smas_score ,mb.mb_seq , mb.mb_name , ss.ss_li_seq  , li.li_name  ,fg.fg_name 
from member_basic mb
join class_register cr on cr.cr_mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
join score_standard ss on ss.ss_li_seq = li.li_seq 
join score_master sm on sm.smas_ss_seq = ss.ss_seq 
join score_student ss2 on ss2.sstu_mas_seq = sm.smas_seq 
left join final_grade fg on fg.fg_seq = cr.cr_fg_seq 
where mb.mb_seq = 1 and ss2.sstu_mb_seq = 1;

-- 주영님 수정2 - 현주가 수정중
select ss2.sstu_seq, ss.ss_li_seq, sm.smas_seq , sm.smas_name , ss2.sstu_score ,sm.smas_score ,mb.mb_seq
from member_basic mb
join class_register cr on cr.cr_mb_seq = mb.mb_seq 
join lecture_info li on li.li_seq = cr.cr_li_seq 
join score_standard ss on ss.ss_li_seq = li.li_seq 
join score_master sm on sm.smas_ss_seq = ss.ss_seq 
join score_student ss2 on ss2.sstu_mas_seq = sm.smas_seq
where mb.mb_seq = 1 and ss2.sstu_mb_seq = 1;

 ,fg.fg_name 
 join class_register cr on cr.cr_mb_seq = mb.mb_seq 
join final_grade fg on fg.fg_seq = cr.cr_fg_seq 
 

group by sc_seq
where mb.mb_seq = 1 ;

select * from final_grade fg;


-- 중간,기말,출석,과제(?) 이건 잘 모르겠음
select ss.sstu_seq, ss.sstu_score , sm.smas_name , sm.smas_score ,mb.mb_seq , mb.mb_name , sstan.ss_li_seq  , li.li_name , sc.sc_name , sc.sc_seq 
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
where mb.mb_seq = 1
group by sc_seq;

select * from attend_info_master aim ;

select * from attend_info_student ;

select * from semester_detail_info;

select * from semester_info;


select * from attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join attend_info_master aim on aim.amas_seq = ais.astu_mas_seq 
join lecture_info li on li.li_seq =aim.amas_li_seq 
where mb_seq=1;

-- group by
-- sc_seq 가 여러개인? group by sc_seq
-- 과제1+과제2+과제3 = SUM/과제의 개수(count) -> avg


-- 강의의 학생의 출석한 날짜
select *,count(*) from lecture_info li 
join attend_info_master aim on li.li_seq  =aim.amas_li_seq 
join attend_info_student ais on ais.astu_mas_seq = aim.amas_seq 
join student s on s.mb_seq = ais.astu_mb_seq 
where ais.astu_status = 1 and s.mb_seq = 1;

-- 1번 학생이 출석해야 하는 날짜
select *, count(*) from  attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join member_basic mb  on mb.mb_seq = s.mb_seq 
join attend_info_master aim on ais.astu_mas_seq = aim.amas_seq 
join lecture_info li on li.li_seq = aim.amas_li_seq 
where s.mb_seq =1;

-- 1번 학생이 출석 한 날짜
select ais.astu_seq , mb.mb_id, mb.mb_name , s.stu_subject , s.stu_grade , count(*)as student_attend ,li.li_code from  attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join member_basic mb  on mb.mb_seq = s.mb_seq 
join attend_info_master aim on ais.astu_mas_seq = aim.amas_seq 
join lecture_info li on li.li_seq = aim.amas_li_seq 
where s.mb_seq =1 and astu_status =1;

-- * 중요
--  **** 영은이 JPQL 변환했는거
-- 1번 학생이 출석 한 날짜 + 전체 날짜 *JPQL로 만든 거 parameter값 주의 . entity주의(엔티디에 조인이 걸려있으면 타고 들어가야함)
select ais.astu_seq ,li.li_code, mb.mb_id, mb.mb_name , s.stu_subject , s.stu_grade , count(*)as student_attend ,
(
	select count(*) from lecture_info li 
	join attend_info_master aim on li.li_seq = aim.amas_li_seq
) as total_attend
from  attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join member_basic mb  on mb.mb_seq = s.mb_seq 
join attend_info_master aim on ais.astu_mas_seq = aim.amas_seq 
join lecture_info li on li.li_seq = aim.amas_li_seq
where s.mb_seq =2 and astu_status =1;
-- where s.mb_seq =1 and astu_status =1;

-- * 중요
-- 
-- 1번 학생이 출석 한 날짜 + 전체 날짜 *JPQL로 만든 거 parameter값 주의 . entity주의(엔티디에 조인이 걸려있으면 타고 들어가야함)
select ais.astu_seq , mb.mb_id, mb.mb_name , s.stu_subject , s.stu_grade , count(*)as student_attend ,
(
	select count(*) from lecture_info li 
	join attend_info_master aim on li.li_seq = aim.amas_li_seq
) as total_attend
from  attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join member_basic mb  on mb.mb_seq = s.mb_seq 
join attend_info_master aim on ais.astu_mas_seq = aim.amas_seq 
join lecture_info li on li.li_seq = aim.amas_li_seq
where s.mb_seq =1 and astu_status =1;
-- where s.mb_seq =1 and astu_status =1;

-- 강의의 전체 날짜
select count(*) as total_attend from lecture_info li 
join attend_info_master aim on li.li_seq = aim.amas_li_seq ;

-- 강의의 학생의 출석한 날짜/강의의 전체 날짜 => 출석최종점수

-- 

-- * 중요 / 나오지만
-- 
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select ss.sstu_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq , mb.mb_name , sstan.ss_li_seq , fg.fg_name 
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq 
join class_register cr on cr.cr_mb_seq = mb.mb_seq 
join final_grade fg on fg.fg_seq = cr.cr_fg_seq 
where mb.mb_seq = 1 ;


-- * 중요
-- 
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select sm.smas_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq ,li.li_seq
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq
where mb.mb_seq = 1 and li.li_seq = 1;

create view LectureStudentCateListScoreVoVIEW as
select sm.smas_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq ,li.li_seq
from score_student ss 
join score_master sm on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq;
where mb.mb_seq = 1 and li.li_seq = 1;
-- * 최종?
-- 
--         // @Query(value = "select * from table_name where case_1=:case_1 and date='2017-04-04' ", nativeQuery=true)
--         @Query(value = "select sm.smas_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq ,li.li_seq "
--         +"from score_master sm "
--         +"join score_student ss on sm.smas_seq = ss.sstu_mas_seq"
--         +"join member_basic mb on mb.mb_seq = ss.sstu_mb_seq "
--         +"join score_standard sstan on sstan.ss_seq = sm.smas_ss_seq "
--         +"join lecture_info li on li.li_seq = sstan.ss_li_seq "
--         +"join score_cate sc on sc.sc_seq = sstan.ss_sc_seq "
--         +"where mb.mb_seq =:stuSeq and li.li_seq =:liSeq", nativeQuery=true)
--         List<ScoreMasterEntity> findByStuAndLecScore(@Param("liSeq") Long liSeq, @Param("stuSeq")Long stuSeq);
-- 중간,기말,출석, 과제1,2,3 이 다 나옴 1번학생의
select sm.smas_seq, sm.smas_name ,ss.sstu_score , sm.smas_score ,mb.mb_seq ,li.li_seq
from score_master sm  
join score_student ss on sm.smas_seq = ss.sstu_mas_seq  
join member_basic mb on mb.mb_seq = ss.sstu_mb_seq 
join score_standard sstan on sstan.ss_seq  = sm.smas_ss_seq 
join lecture_info li on li.li_seq = sstan.ss_li_seq 
join score_cate sc on sc.sc_seq = sstan.ss_sc_seq
where mb.mb_seq = 1 and li.li_seq = 1;

-- 최종성적만 반출
select * from class_register cr ;
-- * 중요!
-- 최종성적만 반출
-- 1번 학생의 최종성적 /단 미입력은 미입력이라고 나오게하기! /final_final
select cr.cr_seq, mb.mb_seq ,fg.fg_seq , fg.fg_name 
from class_register cr 
join member_basic mb on mb.mb_seq = cr.cr_mb_seq
left outer join final_grade fg on fg.fg_seq  = cr.cr_fg_seq 
where mb.mb_seq = 1 and cr.cr_li_seq = 1;
-- * 중요!2
-- 최종성적만 반출
-- 1번 학생의 최종성적 /단 미입력은 미입력이라고 나오게하기! /final_final
select cr.cr_seq, mb.mb_seq ,cr.cr_fg_seq, fg.fg_seq , fg.fg_name 
from class_register cr 
join member_basic mb on mb.mb_seq = cr.cr_mb_seq
left join final_grade fg on fg.fg_seq  = cr.cr_fg_seq 
where mb.mb_seq = 1 and cr.cr_li_seq = 1;
-- 최종쿼리문 작성
--     @Query(value = "select cr.cr_seq, cr.cr_mb_seq , fg.fg_seq as cr_fg_seq, fg.fg_name, cr.cr_li_seq from class_register cr join member_basic mb on mb.mb_seq = cr.cr_mb_seq left join final_grade fg on fg.fg_seq  = cr.cr_fg_seq where mb.mb_seq =:mbSeq and cr.cr_li_seq =:liSeq", nativeQuery=true)
--     ClassRegisterEntity findByfinalName(@Param("liSeq") Long liSeq, @Param("mbSeq")Long mbSeq);

-- 1번 학생의 최종성적 /단 미입력은 미입력이라고 나오게하기!
select cr.cr_seq, mb.mb_seq ,fg.fg_seq , fg.fg_name 
from class_register cr 
join member_basic mb on mb.mb_seq = cr.cr_mb_seq
join final_grade fg on fg.fg_seq  = cr.cr_fg_seq 
where mb.mb_seq = 1;

-- * 중요
-- 
-- 1번 학생이 출석 한 날짜 + 전체 날짜 
select ais.astu_seq , mb.mb_seq, count(*)as student_attend ,
(
	select count(*) from lecture_info li 
	join attend_info_master aim on li.li_seq = aim.amas_li_seq
) as total_attend
from  attend_info_student ais
join student s on s.mb_seq = ais.astu_mb_seq 
join member_basic mb  on mb.mb_seq = s.mb_seq 
join attend_info_master aim on ais.astu_mas_seq = aim.amas_seq 
join lecture_info li on li.li_seq = aim.amas_li_seq
where s.mb_seq =1 and astu_status =1 ;

and li_seq =1; -- 강의번호 안넣어도 나오네 ??


-- 내 강의 학생 과제1,2,3 의 점수  




-- 최종 출석 카운트
select li2.li_seq , s.mb_seq, 
( 
	select count(*) from attend_info_student a 
	join attend_info_master b on a.astu_mas_seq = b.amas_seq 
	join student c on c.mb_seq = a.astu_mb_seq 
	join member_basic d on d.mb_seq = c.mb_seq 
	join lecture_info e on e.li_seq = b.amas_li_seq 
	where e.li_seq = 1 and c.mb_seq = 1 and a.astu_status = 1
) as student_attend,
( select count(*) from lecture_info li join attend_info_master aim on li.li_seq = 1 and aim.amas_li_seq = 1) as total_attend 
from lecture_info li2
join class_register cr on cr.cr_li_seq = li2.li_seq 
join student s on s.mb_seq = cr.cr_mb_seq 
join member_basic mb on mb.mb_seq = s.mb_seq 
where s.mb_seq = 1 and li2.li_seq = 1;

-- * 오류 astu_mas_seq 리포지토리에서 못찾아서 뷰로 만듬
--     @Query(value = "select li2.li_seq , s.mb_seq, "
--     +"( select count(*) from attend_info_student a join attend_info_master b on a.astu_mas_seq = b.amas_seq join student c on c.mb_seq = a.astu_mb_seq join member_basic d on d.mb_seq = c.mb_seq join lecture_info e on e.li_seq = b.amas_li_seq where e.li_seq =:liSeq and c.mb_seq =:stuSeq and a.astu_status = 1) as student_attend, "
--     +"( select count(*) from lecture_info li join attend_info_master aim on li.li_seq =:liSeq and aim.amas_li_seq =:liSeq )as total_attend "
--     +"from lecture_info li2 "
--     +"join class_register cr on cr.cr_li_seq = li2.li_seq "
--     +"join student s on s.mb_seq = cr.cr_mb_seq "
--     +"join member_basic mb on mb.mb_seq = s.mb_seq "
--     +"where s.mb_seq =:stuSeq and li2.li_seq =:liSeq", nativeQuery=true)
--     List<AttendInfoStudentEntity> findByStuAndLec(@Param("stuSeq")Long stuSeq,@Param("liSeq") Long liSeq);

-- 최종 출석 카운트
create view LectureStudentAttendVoVIEW as
select li2.li_seq as li_seq, s.mb_seq as mb_seq, 
( 
	select count(*) from attend_info_student a 
	join attend_info_master b on a.astu_mas_seq = b.amas_seq 
	join student c on c.mb_seq = a.astu_mb_seq 
	join member_basic d on d.mb_seq = c.mb_seq 
	join lecture_info e on e.li_seq = b.amas_li_seq 
	where e.li_seq = 1 and c.mb_seq = 1 and a.astu_status = 1
) as student_attend,
( select count(*) from lecture_info li join attend_info_master aim on li.li_seq = 1 and aim.amas_li_seq = 1) as total_attend 
from lecture_info li2
join class_register cr on cr.cr_li_seq = li2.li_seq 
join student s on s.mb_seq = cr.cr_mb_seq 
join member_basic mb on mb.mb_seq = s.mb_seq;
where s.mb_seq = 1 and li2.li_seq = 1;

 

flo_db

create view album_summary_info_view as
select 
	ai_seq as asiv_seq,
	ai_img as asiv_cover,
	ai_name as asiv_title,
	ai_pub_dt as asiv_pub_dt,
	ai_foreign as asiv_country,
	agi_name as asiv_grp_name,
	music_cnt as asiv_music_cnt
from album_info a left outer join 
artist_group_info b on a.ai_agi_seq = b.agi_seq
left outer join
(
	select count(*) as music_cnt, music_ai_seq from music_info group by music_ai_seq
) c on a.ai_seq = c.music_ai_seq;


drop view album_summary_info_view;
select * from album_summary_info_view;

-- 한번에 날리면 중복이 발생할수 있음
select * from album_info a 
inner join music_info b
on a.ai_seq = b.music_ai_seq ;

-- 두번 날리는게 더 좋음
select * from album_info where ai_seq = 1;

-- drop view music_info_view;

create view music_info_view as
select 
	music_seq , music_name , music_order ,music_is_title, genre_name,
	mfile_name, music_ai_seq as artist_no
from music_info a left outer join genre_info b 
on a.music_genre_seq = b.genre_seq
inner join music_file_info c on a.music_seq = c.mfile_music_seq 
where music_ai_seq = 1;

-- 요렇게 두번 되겠지 최종
select * from album_info where ai_seq = 1;

select * from music_info_view
where artistNo =1 order by music_order asc ;

 

저작자표시

'basic_memo' 카테고리의 다른 글

자바 ORM 표준 JPA 프로그래밍_김영한님 gitbook  (0) 2023.04.03
test_code 블럭 변경 중  (0) 2023.03.29
Git ) Git을 사용하여 협력 - Git 깃허브 Github 사용  (0) 2023.03.29

'basic_memo'의 다른글

  • 현재글잠깐 저장 .. sql문

관련글

댓글

티스토리툴바