Main Page

Main Page 에서 사용할 쿼리

신규 프로젝트

한방 쿼리

SELECT ai_project_id 
        ,f_project_name 
        ,f_thumbnail 
        ,SUM(f_spon + f_invest) 
        ,TRUNCATE((SUM(f_spon + f_invest)/f_donate_limit)*100,2) AS 펀딩퍼센트 
        ,f_etp_name 
        ,DATEDIFF(f_date_limit,CURDATE()) AS f_daysleft
FROM tbl_project
        LEFT JOIN tbl_img 
                ON tbl_project.ai_project_id = tbl_img.sys_project_id
        LEFT JOIN tbl_enterprise 
                ON tbl_project.ai_project_id = tbl_enterprise.sys_project_id
        LEFT JOIN tbl_payment 
                ON tbl_payment.sys_project_id = tbl_project.ai_project_id
WHERE DATEDIFF(f_date_limit,CURDATE()) > 0
GROUP BY ai_project_id
ORDER BY auto_date DESC

Image

SELECT f_thumbnail 
        ,f_screenshot 
        ,f_gamepv 
        ,f_artworks 
FROM tbl_project
        LEFT JOIN tbl_img 
                ON tbl_project.ai_project_id = tbl_img.sys_project_id

Enterprise

SELECT ai_project_id 
        ,f_etp_name 
        ,f_etp_value 
        ,f_etp_desc 
        ,f_etp_logo 
        ,f_etp_url
FROM tbl_project
        LEFT JOIN tbl_enterprise 
                ON tbl_project.ai_project_id = tbl_enterprise.sys_project_id

Recently Project , Days Left

최근 등록 프로젝트 , 남은일수

SELECT ai_project_id 
        ,DATEDIFF(f_date_limit,CURDATE()) AS 남은일수 
        ,auto_date 
FROM tbl_project
ORDER BY auto_date desc

Percent of Funding

SELECT ai_project_id 
        ,TRUNCATE((SUM(f_spon + f_invest)/f_donate_limit)*100,2) AS 펀딩퍼센트  
        ,SUM(f_money) 
        ,f_donate_limit
FROM tbl_project
        LEFT JOIN tbl_payment 
                ON tbl_payment.sys_project_id = tbl_project.ai_project_id
GROUP BY ai_project_id

프로젝트 골라보기

한방 쿼리

SELECT j_link.sys_project_id 
      ,j_link.sys_category_id
      ,j_category.f_category_name
      ,j_project_Sum.f_project_name
      ,j_project_Sum.f_projectTotal
      ,j_project_Sum.f_percent
     	,j_project_Sum.f_etp_name
     	,j_project_Sum.f_daysleft
     	,j_project_Sum.f_thum
     
      
  FROM tbl_pj_category as j_link
       Left join tbl_category AS j_category 
		 	 ON j_link.sys_category_id = j_category.ai_category
       LEFT JOIN (
       
		           SELECT j_project.ai_project_id
							      ,j_project.f_project_name
							      ,sum(j_payment.f_spon +j_payment.f_invest) AS f_projectTotal
							      ,TRUNCATE((sum(j_payment.f_spon +j_payment.f_invest)/j_project.f_donate_limit)*100,2) AS f_percent
							      ,j_etp.f_etp_name AS f_etp_name
							      ,DATEDIFF(f_date_limit,CURDATE()) AS f_daysleft
							      ,j_img.f_thumbnail AS f_thum
							      ,j_project.f_div
							  FROM tbl_project AS j_project 
							     LEFT JOIN tbl_payment AS j_payment 
									 	ON j_project.ai_project_id = j_payment.sys_project_id  
									 LEFT JOIN tbl_enterprise AS j_etp 
									 	ON j_project.ai_project_id = j_etp.sys_project_id
									 LEFT JOIN tbl_img AS j_img 
									 	ON j_project.ai_project_id = j_img.sys_project_id  	    
							 		 GROUP BY j_project.ai_project_id,j_project.f_project_name
								
							 		  
							 
					   ) AS j_project_Sum
				   ON j_project_Sum.ai_project_id = j_link.sys_project_id
		WHERE j_project_Sum.f_daysleft > 0 	AND j_link.sys_category_id = 4	   

한 project 당 여러개의 category를 가져서 ,funding money 랑 ,percent ....등등 중복되서 뜸

Category

SELECT ai_project_id
        ,sys_category_id
        ,f_category_name
FROM tbl_project
        LEFT JOIN tbl_pj_category 
                ON tbl_project.ai_project_id = tbl_pj_category.sys_project_id
        LEFT JOIN tbl_category 
                ON tbl_pj_category.sys_category_id = tbl_category.ai_category

추천 프로젝트

한방 쿼리

SELECT ai_project_id 
        ,f_project_name 
        ,f_thumbnail 
        ,SUM(f_spon + f_invest) 
        ,TRUNCATE((SUM(f_spon + f_invest)/f_donate_limit)*100,2) AS f_per
        ,DATEDIFF(f_date_limit,CURDATE()) AS f_daysleft 
        ,f_etp_name 
FROM tbl_project
        LEFT JOIN tbl_img 
                ON tbl_project.ai_project_id = tbl_img.sys_project_id
        LEFT JOIN tbl_enterprise 
                ON tbl_project.ai_project_id = tbl_enterprise.sys_project_id
        LEFT JOIN tbl_payment 
                ON tbl_payment.sys_project_id = tbl_project.ai_project_id
WHERE tbl_project.f_div = 'Y' AND DATEDIFF(f_date_limit,CURDATE()) > 0
GROUP BY ai_project_id
ORDER BY f_per DESC

Order by Funding Amount DESC

펀딩 금액 높은순

SELECT ai_project_id 
        ,TRUNCATE((SUM(f_spon + f_invest)/f_donate_limit)*100,2) AS 펀딩퍼센트  
        ,SUM(f_spon + f_invest) 
        ,f_donate_limit
FROM tbl_project
        LEFT JOIN tbl_payment 
                ON tbl_payment.sys_project_id = tbl_project.ai_project_id
GROUP BY ai_project_id
ORDER BY SUM(f_spon + f_invest) DESC

사용자가 좋아할만한 프로젝트

한방 쿼리

user_id가 1인 사용자 Bookmark한 Project중에서 가장 비율이 많은 Category순

LIMIT 1은 가장 비율이 높은 Category 하나만 산출하기 위해 추가함 (빼도 됨)

SELECT j_book.sys_user_id
      ,j_pj_cate.sys_category_id
      ,COUNT(*) AS f_cnt
  FROM tbl_pj_bookmark AS j_book
       LEFT JOIN tbl_pj_category AS j_pj_cate
              ON j_book.sys_project_id = j_pj_cate.sys_project_id
  WHERE j_book.sys_user_id = 1  
  GROUP BY j_book.sys_user_id   , j_pj_cate.sys_category_id 
  ORDER BY f_cnt DESC
  LIMIT 1    

사용자가 특정문구로 프로젝트 검색

한방 쿼리

Like 문 안에 사용자가 입력한 문구 INSERT

SELECT j_link.sys_project_id 
      ,j_link.sys_category_id
      ,j_category.f_category_name
      ,j_project_Sum.f_project_name
      ,j_project_Sum.f_projectTotal
      ,j_project_Sum.f_percent
     	,j_project_Sum.f_etp_name
     	,j_project_Sum.f_daysleft
     	,j_project_Sum.f_thum
     
      
  FROM tbl_pj_category as j_link
       Left join tbl_category AS j_category 
		 	 ON j_link.sys_category_id = j_category.ai_category
       LEFT JOIN (
       
		           SELECT j_project.ai_project_id
							      ,j_project.f_project_name
							      ,sum(j_payment.f_spon + j_payment.f_invest) AS f_projectTotal
							      ,TRUNCATE((sum(j_payment.f_spon + j_payment.f_invest)/j_project.f_donate_limit)*100,2) AS f_percent
							      ,j_etp.f_etp_name AS f_etp_name
							      ,DATEDIFF(f_date_limit,CURDATE()) AS f_daysleft
							      ,j_img.f_thumbnail AS f_thum
							      ,j_project.f_div
							  FROM tbl_project AS j_project 
							     LEFT JOIN tbl_payment AS j_payment 
									 	ON j_project.ai_project_id = j_payment.sys_project_id  
									 LEFT JOIN tbl_enterprise AS j_etp 
									 	ON j_project.ai_project_id = j_etp.sys_project_id
									 LEFT JOIN tbl_img AS j_img 
									 	ON j_project.ai_project_id = j_img.sys_project_id  	    
							 		 GROUP BY j_project.ai_project_id,j_project.f_project_name
								
							 		  
							 
					   ) AS j_project_Sum
				   ON j_project_Sum.ai_project_id = j_link.sys_project_id
	WHERE	concat(j_project_Sum.f_project_name,',',j_project_Sum.f_etp_name,',',j_category.f_category_name)  LIKE '%No genre%' AND j_project_Sum.f_daysleft > 0 AND j_project_Sum.f_div = 'Y' 
	GROUP BY j_project_Sum.ai_project_id,j_project_Sum.f_project_name