sql文におけるGROUP BYとHAVINGの使用count()
4068 ワード
GROUP BYとHAVING句を紹介する前に、SUM、COUNT、MAX、AVGなど、sql言語の特殊な関数について話さなければなりません.これらの関数と他の関数の根本的な違いは、複数のレコードに一般的に作用することです.SELECT SUM(population)FROM bbcここでのSUMは、すべての戻り記録のpopulationフィールドに作用し、その結果、クエリは1つの結果、すなわちすべての国の総人口数だけを返すことになる.
GROUP BY句を使用することで、SUMやCOUNTといった関数を一組のデータに役立てることができます.GROUP BY regionを指定すると、同じregion(地域)に属するデータのセットは1行の値しか返されません.つまり、テーブル内のregion(地域)以外のすべてのフィールドはSUMのみで返されます.COUNTなどの集約関数の演算後に1つの値を返します.HAVING句はグループ化後の各グループのデータをスクリーニングします.WHERE句は集約前に記録をスクリーニングします.つまりGROUP BY句とHAVING句の前に作用します.HAVING句は集約後にグループ記録をスクリーニングします.GROUP BYとHAVING句を具体的な例で理解し,第3節で紹介したbbc表も採用する.SQL例:一、地域ごとの総人口と総面積を表示する.SELECT region,SUM(population),SUM(area)FROM bbc#`&e 4 k'X*n 1 v%?+|GROUP BY regionはまずregionでリターンレコードを複数のグループに分けている.これがGROUP BYの文字通りの意味である.グループを分割した後、各グループの異なるフィールド(1つ以上のレコード)を集約関数で演算します.B*i'z`,}*S,E 5 i二は,地域ごとの総人口数と総面積を示す.それらの面積が1000000を超える地域のみを示す. SELECT region, SUM(population), SUM(area)7 ]; Z& I! t%iFROM bbc 8 F 4 w 2 v(P-fGROUP BY regionHAVING SUM(area)>100000#y"Pz.O 7 D 9`#Xここでは、表にこのような記録が存在しないため、whereで100000を超える地域をフィルタリングすることはできません.逆に、HAVING句はグループ化後の各グループのデータをフィルタリングすることができます
三、CUSTOMERとORDER表のユーザーの注文数を問い合わせる
select c.name, count(order_number) as count from orders o,customer c where c.id=o.customer_id group by customer_id;
+----+----+|name|count|+----+----+|d|9||cc|6||菩提子|1||cccc|2|+------+++
HAVINGフィルタを追加
select c.name, count(order_number) as count from orders o,customer c where c.id=o.customer_id group by customer_id having count(order_number)>5;
+------+-------+ | name | count | +------+-------+ | d | 9 | | cc | 6 | +------+-------+
四、私はいくつかの例を挙げています.
SQL> select * from sc;
SNO PNO GRADE---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 YW 89 2 SX 91 2 YY 92 3 YW 85 3 SX 88 3 YY 96 4 YW 95 4 SX 89
SNO PNO GRADE---------- ----- ---------- 4 YY 88
この表は、SNO(学生番号)、PNO(カリキュラム名)、GRADE(成績)の4人の学生が各科目の学習成績に対応した記録を示している.
1、90点以上の学生の課程名と成績を表示する
//簡単なクエリーで、グループクエリーは使用されていません
SQL> select sno,pno,grade from sc where grade>=90;
SNO PNO GRADE---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 SX 91 2 YY 92 3 YY 96 4 YW 95
7行を選択しました.
2、学生一人当たりの成績が90点以上の成績がどれだけあるかを示す
//グループ表示を行い、where条件に従ってカウントする
SQL> select sno,count(*) from sc where grade>=90 group by sno;
SNO COUNT(*)---------- ---------- 1 3 2 2 4 1 3 1
3、ここではhaving文を使っていません.次に、3つの学生を選ぶには、少なくとも2つの課程が90点以上でなければ資格がありません.資格のある学生番号と90点以上の課程数をリストします.
//パケット表示を行い、where条件に従ってカウントし、having句に従ってパケットをフィルタする
SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2;
SNO COUNT(*)---------- ---------- 1 3 2 2
この結果は私たちが望んでいるもので、3つの学生を選ぶ資格を持つ学生番号をリストし、1つの例と比較した後、グループ化したサブクエリであることがわかりました.
4、学校は先進的な学生を選んで、平均成績が90点より大きい学生はすべて資格があることを要求して、しかも国語の授業は95点以上でなければならなくて、資格のある学生をリストしてください
//実際、このクエリはまず国語が95点より大きい学生番号を抽出し、その後平均値を求め、グループ表示後having文に基づいて平均成績が90より大きいものを選択する
SQL> select sno,avg(grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno having avg(grade)>=90;
SNO AVG(GRADE)---------- ---------- 1 94.3333333 4 90.6666667
5、平均成績より少なくとも学生番号が3の平均成績より高い学生番号と平均点数を調べる
///having句の比較とサブクエリ
SQL> select sno,avg(grade) from sc group by sno having avg(grade) > (select avg(grade) from sc where sno=3);
having （group by） ，
where GROUP BY句を使用することで、SUMやCOUNTといった関数を一組のデータに役立てることができます.GROUP BY regionを指定すると、同じregion(地域)に属するデータのセットは1行の値しか返されません.つまり、テーブル内のregion(地域)以外のすべてのフィールドはSUMのみで返されます.COUNTなどの集約関数の演算後に1つの値を返します.HAVING句はグループ化後の各グループのデータをスクリーニングします.WHERE句は集約前に記録をスクリーニングします.つまりGROUP BY句とHAVING句の前に作用します.HAVING句は集約後にグループ記録をスクリーニングします.GROUP BYとHAVING句を具体的な例で理解し,第3節で紹介したbbc表も採用する.SQL例:一、地域ごとの総人口と総面積を表示する.SELECT region,SUM(population),SUM(area)FROM bbc#`&e 4 k'X*n 1 v%?+|GROUP BY regionはまずregionでリターンレコードを複数のグループに分けている.これがGROUP BYの文字通りの意味である.グループを分割した後、各グループの異なるフィールド(1つ以上のレコード)を集約関数で演算します.B*i'z`,}*S,E 5 i二は,地域ごとの総人口数と総面積を示す.それらの面積が1000000を超える地域のみを示す. SELECT region, SUM(population), SUM(area)7 ]; Z& I! t%iFROM bbc 8 F 4 w 2 v(P-fGROUP BY regionHAVING SUM(area)>100000#y"Pz.O 7 D 9`#Xここでは、表にこのような記録が存在しないため、whereで100000を超える地域をフィルタリングすることはできません.逆に、HAVING句はグループ化後の各グループのデータをフィルタリングすることができます
三、CUSTOMERとORDER表のユーザーの注文数を問い合わせる
select c.name, count(order_number) as count from orders o,customer c where c.id=o.customer_id group by customer_id;
+----+----+|name|count|+----+----+|d|9||cc|6||菩提子|1||cccc|2|+------+++
HAVINGフィルタを追加
select c.name, count(order_number) as count from orders o,customer c where c.id=o.customer_id group by customer_id having count(order_number)>5;
+------+-------+ | name | count | +------+-------+ | d | 9 | | cc | 6 | +------+-------+
四、私はいくつかの例を挙げています.
SQL> select * from sc;
SNO PNO GRADE---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 YW 89 2 SX 91 2 YY 92 3 YW 85 3 SX 88 3 YY 96 4 YW 95 4 SX 89
SNO PNO GRADE---------- ----- ---------- 4 YY 88
この表は、SNO(学生番号)、PNO(カリキュラム名)、GRADE(成績)の4人の学生が各科目の学習成績に対応した記録を示している.
1、90点以上の学生の課程名と成績を表示する
//簡単なクエリーで、グループクエリーは使用されていません
SQL> select sno,pno,grade from sc where grade>=90;
SNO PNO GRADE---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 SX 91 2 YY 92 3 YY 96 4 YW 95
7行を選択しました.
2、学生一人当たりの成績が90点以上の成績がどれだけあるかを示す
//グループ表示を行い、where条件に従ってカウントする
SQL> select sno,count(*) from sc where grade>=90 group by sno;
SNO COUNT(*)---------- ---------- 1 3 2 2 4 1 3 1
3、ここではhaving文を使っていません.次に、3つの学生を選ぶには、少なくとも2つの課程が90点以上でなければ資格がありません.資格のある学生番号と90点以上の課程数をリストします.
//パケット表示を行い、where条件に従ってカウントし、having句に従ってパケットをフィルタする
SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2;
SNO COUNT(*)---------- ---------- 1 3 2 2
この結果は私たちが望んでいるもので、3つの学生を選ぶ資格を持つ学生番号をリストし、1つの例と比較した後、グループ化したサブクエリであることがわかりました.
4、学校は先進的な学生を選んで、平均成績が90点より大きい学生はすべて資格があることを要求して、しかも国語の授業は95点以上でなければならなくて、資格のある学生をリストしてください
//実際、このクエリはまず国語が95点より大きい学生番号を抽出し、その後平均値を求め、グループ表示後having文に基づいて平均成績が90より大きいものを選択する
SQL> select sno,avg(grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno having avg(grade)>=90;
SNO AVG(GRADE)---------- ---------- 1 94.3333333 4 90.6666667
5、平均成績より少なくとも学生番号が3の平均成績より高い学生番号と平均点数を調べる
///having句の比較とサブクエリ
SQL> select sno,avg(grade) from sc group by sno having avg(grade) > (select avg(grade) from sc where sno=3);