プログラマーSQL-(SELECT)、(SUM、MAX、MIN)、(GROUP BY)

3356 ワード
sql テキストリンク
-すべてのレコードを表示:簡単すぎる
SELECT * from ANIMAL_INS as ani_i
ORDER BY ani_i.ANIMAL_ID
逆の順序で
  • :簡単すぎる
    • SELECT NAME, DATETIME from ANIMAL_INS as ani_i
ORDER BY ani_i.ANIMAL_ID desc
  • 病気の動物を探す:簡単すぎる
    • SELECT ANIMAL_ID, NAME from ANIMAL_INS as ani_i
WHERE 1=1
AND INTAKE_CONDITION ='Sick'
ORDER BY ANIMAL_ID asc;
  • 小動物を探しています:簡単すぎます(どうして簡単な問題しかありませんか?)
    • SELECT ANIMAL_ID, NAME from ANIMAL_INS as ani_i
WHERE 1=1
AND INTAKE_CONDITION !='Aged'
ORDER BY ANIMAL_ID asc;
  • 動物のIDと名前:簡単すぎる(おかしい...)
    • SELECT ANIMAL_ID, NAME from ANIMAL_INS as ani_i
WHERE 1=1
ORDER BY ANIMAL_ID asc;
    複数の基準で
  • をソート:簡単すぎる
    • SELECT ANIMAL_ID, NAME, DATETIME from ANIMAL_INS as ani_i
WHERE 1=1
ORDER BY NAME asc, DATETIME desc;
  • 上位n名記録:容易すぎる
    • SELECT NAME from ANIMAL_INS as ani_i
WHERE 1=1
ORDER BY DATETIME asc
LIMIT 1;
  • 最大値を求めます:あまりに容易な
    • SELECT DATETIME from ANIMAL_INS as ani_i
WHERE 1=1
ORDER BY DATETIME desc
LIMIT 1;
    (出題意図に合った回答)
    SELECT max(datetime) as '시간' from animal_ins;
  • 最高価格の
    • を求めます
    SELECT min(datetime) from animal_ins;
  • の動物の数を得る:簡単すぎる...問題はどうしてこんなに質が低いのですか.
    • SELECT count(ANIMAL_ID) from ANIMAL_INS as ani_i
  • 重複データ消去:私が解いたのは出題のためではないようです...
    • SELECT count(*) from (
    SELECT NAME, count(ANIMAL_ID) from ANIMAL_INS as ani_i
    WHERE 1=1
    AND ani_i.NAME != 'NULL'
    GROUP BY ani_i.NAME
    ) as c;
    (出題意図に合った答え:簡単にDISTINCTを…ㅠ)
    SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS;
  • 猫と犬はいくらあります:とても簡単です
    • SELECT ANIMAL_TYPE, count(ANIMAL_ID) from ANIMAL_INS as ani_i
group by ani_i.ANIMAL_TYPE
order by ani_i.ANIMAL_TYPE asc;
  • 同名動物の数を探しています:考えてみました.サブクエリを使用する必要がありますか?groupbyでagg(count)クエリを行った結果ではhaveセクションを使用します.
    • select NAME, count(*) from ANIMAL_INS
where 1=1
AND NAME != 'NULL'
group by NAME
HAVING count(*) > 1
order by NAME asc
  • 養子縁組の視点を求める1:HOUR(DATETIME)asHOURにはAS後のas HOUR部分が設けられていなかったので、Have節でHOURを条件にした時に苦労した.
    • select HOUR(DATETIME) as HOUR, count(*) from ANIMAL_OUTS
group by HOUR
HAVING 1=1
AND HOUR > 8
AND HOUR < 20
order by HOUR asc;
  • 養子縁組の視点を探す2:Very Hard(答えられない)また、実際のSQLでの動作もこの動作を繰り返します.
    • SELECT HOUR, COUNT(OUTS.DATETIME) AS COUNT
FROM 
    (
        WITH RECURSIVE CTE AS
            (
                 SELECT 0 AS HOUR 
                 UNION ALL
                 SELECT 1 + HOUR AS HOUR 
                 FROM CTE
                 WHERE HOUR < 23
            )SELECT * FROM CTE 
    ) TIME
LEFT JOIN ANIMAL_OUTS OUTS ON HOUR(OUTS.DATETIME) = TIME.HOUR
GROUP BY HOUR
ORDER BY HOUR ASC
    TIL、7週目木曜日
    Baek Junアルゴリズム第8393題