nose딩

SELECT PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM (
    SELECT FLOOR(PRICE/10000)*10000 AS PRICE_GROUP
    FROM PRODUCT
)
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

import java.util.HashSet;
class Solution {
    static HashSet<Integer> primes;
    static boolean[] visited;
    static int N;
    public int solution(String numbers) {
        N = numbers.length();
        primes = new HashSet<>();
        visited = new boolean[N];
        makeNumber("",numbers);
        return primes.size();
    }
    static void makeNumber(String s,String numbers){
        if(s.length()>N) return;
        if(s.length()!=0){
            int num = Integer.parseInt(s);
            if(isPrime(num)) primes.add(num);
        }
        
        for(int i=0; i<N; i++){
            if(visited[i]==false){
                visited[i]=true;
                makeNumber(s+numbers.charAt(i),numbers);
                visited[i]=false;
            }
        }
    }
    static boolean isPrime(int n){
        if(n<=1) return false;
        for(int i=2; i<=Math.sqrt(n); i++){
            if(n%i==0) return false;
        }
        return true;
    }
}

class Solution {
    static boolean[] visited;
    static int N;
    static int answer=0;
    public int solution(int k, int[][] dungeons) {
        N = dungeons.length;
        visited = new boolean[N];
        exploreDungeons(k, dungeons,0);
        return answer;
    }
    static void exploreDungeons(int k, int[][] dungeons,int count){
        if(k<0 || count>N) return;
        answer = Math.max(answer,count);
        for(int i=0; i<N; i++){
            if(k>=dungeons[i][0] && visited[i]==false){
                visited[i]=true;
                exploreDungeons(k-dungeons[i][1],dungeons,count+1);
                visited[i]=false;
            }
        }   
    }
}

던전을 차례대로 돌며 탐험할 수 있으면 탐험해준다.

던전을 탐험 할 수도 있고 안할 수도 있기 때문에 이후 false로 바꿔준다.

import java.util.HashSet;
import java.util.List;
import java.util.ArrayList;
import java.util.Collections;
class Solution {
    static HashSet<String> dictionary;
    public int solution(String word) {
        int answer = 0;
        String[] words = {"A","E","I","O","U"};
        dictionary = new HashSet<>();
        makeDictionary("",words);
     
        List<String> dict = new ArrayList<>(dictionary);
        Collections.sort(dict);
        
        for(int i=0; i<dict.size(); i++){
            if(dict.get(i).equals(word)){
                 answer=i;
                 break;
            }
        }
        return answer;
    }
    
    static void makeDictionary(String s,String[] words){
        if(s.length()>5) return;
        
        dictionary.add(s);
        for(int i=0; i<5; i++){
            makeDictionary(s+words[i],words);
        }
    }
}

중복이 없는 HashSet을 이용한 후 ArrayList를 통해 정렬

/*OUTDATE가 5월 1일 이전이면 출고완료, 이후는 출고대기, NULL이면 출고미정*/
SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE,'YYYY-MM-DD') OUT_DATE,
CASE 
    WHEN OUT_DATE IS NULL THEN '출고미정'
    WHEN TO_CHAR(OUT_DATE,'YYYY-MM-DD') <= '2022-05-01' THEN '출고완료'
    ELSE '출고대기' END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID;

case 조건 여러개

case

when 조건 then ~

when 조건 then ~

...

ELSE ~

END

SELECT PRODUCT_ID, PRODUCT_NAME, SUM(PRICE*AMOUNT) AS TOTAL_SALES
FROM (
    SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE, O.AMOUNT
    FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
    ON P.PRODUCT_ID = O.PRODUCT_ID
    WHERE TO_CHAR(O.PRODUCT_DATE,'YYYY-MM') = '2022-05'
)
GROUP BY PRODUCT_ID, PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

SELECT F.CATEGORY, F.PRICE AS MAX_PRICE, F.PRODUCT_NAME
FROM FOOD_PRODUCT F JOIN (
    SELECT CATEGORY, MAX(PRICE) AS PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
    HAVING CATEGORY IN ('과자','국','김치','식용유')
) M
ON F.CATEGORY=M.CATEGORY AND F.PRICE=M.PRICE
ORDER BY F.PRICE DESC;

즐겨찾기 문제랑 비슷한 문제 ⭐

SELECT I.FOOD_TYPE,I.REST_ID,I.REST_NAME,I.FAVORITES
FROM REST_INFO I JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) R
ON I.FAVORITES = R.FAVORITES AND I.FOOD_TYPE = R.FOOD_TYPE
ORDER BY I.FOOD_TYPE DESC;

음식 종류 별 즐겨찾기 max 값이 있는 테이블을 만들어

음식 종류와 즐겨찾기 수를 가지고 JOIN 해주었다. 

🌱