📌 ルーシーとエラを探して

🔥 in

🗨 質問する

複数の名前を指定し、名前を含むデータを検索します.

📍 私の答え

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME = "Lucy" OR
NAME = "Ella" OR
NAME = "Pickle" OR
NAME = "Rogan" OR
NAME = "Sabrina" OR
NAME = "Mitty"
ORDER BY ANIMAL_ID

📍 別の答え

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME in ("Lucy","Ella","Pickle","Rogan","Sabrina","Mitty")
ORDER BY ANIMAL_ID

📌 名称にELを含むDog

🔥 Wildcard

https://www.w3schools.com/sql/sql_wildcards.asp
http://www.tcpschool.com/mysql/mysql_operator_patternMatching

📍 答え

SELECT ANIMAL_ID,NAME FROM ANIMAL_INS
WHERE name LIKE "%EL%" AND animal_type = "Dog"
ORDER BY NAME

📌 ニュートラル化するかどうかを決定

🔥 CASE - WHEN - THEN - ELSE - END

📍 答え

SELECT ANIMAL_ID,NAME,
    CASE
    WHEN SEX_UPON_INTAKE LIKE "%Neutered Male%" OR SEX_UPON_INTAKE LIKE "%Spayed Female"
    THEN "O"
    ELSE "X"
    END 
    AS "중성화"
FROM ANIMAL_INS

📌 長期保護動物(2)

🗨 質問する

📍 同じDate type間で演算が可能です.

SELECT INS.ANIMAL_ID,INS.NAME FROM ANIMAL_INS INS
JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY (OUTS.DATETIME - INS.DATETIME) DESC
LIMIT 2

📌 DATETIMEからDATEへの変換

🔥 DATE_FORMAT(paramter,'%Y-%m-%d')

https://murra.tistory.com/157

SELECT ANIMAL_ID,NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID