Vagrant – 環境構築(旧)

2021-02-23 / tau / コメントする

概要

以降はVagrant導入後最初にインストールしたBoxの導入記録。

Vagrant CloudのCentos/7を選び、共有フォルダ－設定を試みたところ、なかなかうまく共有できなかった。

このBoxのリリースがv2004.01と結構古かったので、その後bento/Centos-7.7に変更し、比較的スムーズにフォルダー共有ができた。

Boxの導入

Boxのインストール

仮想環境のBoxをインストール。今回はVagrant Cloudから提供されているCentosOS/7を選んだ。

Vagrantからのインストールの場合は以下のコマンド。
- >vagrant box add centos/7
他のサイトからの場合、名前を指定してインストールする場合は以下のコマンド。
- >vagrant box add name url/local_file

C:\>vagrant box add centos/7
==> box: Loading metadata for box 'centos/7'
    box: URL: https://vagrantcloud.com/centos/7
This box can work with multiple providers! The providers that it
can work with are listed below. Please review the list and choose
the provider you will be working with.

1) hyperv
2) libvirt
3) virtualbox
4) vmware_desktop

Enter your choice:

C:\>vagrant box add centos/7

==> box: Loading metadata for box 'centos/7'

box: URL: https://vagrantcloud.com/centos/7

This box can work with multiple providers! The providers that it

can work with are listed below. Please review the list and choose

the provider you will be working with.

1) hyperv

2) libvirt

3) virtualbox

4) vmware_desktop

Enter your choice:

今回はVirtualBoxの仮想環境なので3を選択。以下の処理に10分オーダーの時間がかかる。

Enter your choice: 3
==> box: Adding box 'centos/7' (v2004.01) for provider: virtualbox
    box: Downloading: https://vagrantcloud.com/centos/boxes/7/versions/2004.01/providers/virtualbox.box
Download redirected to host: cloud.centos.org
    box:
    box: Calculating and comparing box checksum...
==> box: Successfully added box 'centos/7' (v2004.01) for 'virtualbox'!

Enter your choice: 3

==> box: Adding box 'centos/7' (v2004.01) for provider: virtualbox

box: Downloading: https://vagrantcloud.com/centos/boxes/7/versions/2004.01/providers/virtualbox.box

Download redirected to host: cloud.centos.org

box:

box: Calculating and comparing box checksum...

==> box: Successfully added box 'centos/7' (v2004.01) for 'virtualbox'!

インストールされたBoxの存在確認。

>vagrant box list
centos/7 (virtualbox, 2004.01)

1 2	>vagrant box list centos/7 (virtualbox, 2004.01)

この時点でSSDの容量は141 /237GB free。

仮想環境のVagrant設定

ドライブ直下にVagrantディレクトリー、その下にCentOS7のディレクトリーを作成し、そこに移動。

>mkdir \vagrant
>cd \vagrant
>mkdir centos7
>cd centos7

>mkdir \vagrant

>cd \vagrant

>mkdir centos7

>cd centos7

移動後のディレクトリー内で初期設定。ここでこのディレクトリー内にvagrantfileが作成されて、仮想環境が利用可能になる。

C:\vagrant\centos7>vagrant init centos/7
A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

C:\vagrant\centos7>vagrant init centos/7

A `Vagrantfile` has been placed in this directory. You are now

ready to `vagrant up` your first virtual environment! Please read

the comments in the Vagrantfile as well as documentation on

`vagrantup.com` for more information on using Vagrant.

この時点でSSD 141/237GB freeとなり、6～7GBを使っている。

仮想環境の利用

仮想環境の起動

vagrant upコマンドで仮想環境を起動。

vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'centos/7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'centos/7' version '2004.01' is up to date...
==> default: Setting the name of the VM: centos7_default_1613521493035_2746
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection aborted. Retrying...
    default: Warning: Connection reset. Retrying...
    default:
    default: Vagrant insecure key detected. Vagrant will automatically replace
    default: this with a newly generated keypair for better security.
    default:
    default: Inserting generated public key within guest...
    default: Removing insecure key from the guest if it's present...
    default: Key inserted! Disconnecting and reconnecting using new SSH key...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: No guest additions were detected on the base box for this VM! Guest
    default: additions are required for forwarded ports, shared folders, host only
    default: networking, and more. If SSH fails on this machine, please install
    default: the guest additions and repackage the box to continue.
    default:
    default: This is not an error message; everything may continue to work properly,
    default: in which case you may ignore this message.
==> default: Rsyncing folder: /cygdrive/c/vagrant/centos7/ => /vagrant

vagrant up

Bringing machine 'default' up with 'virtualbox' provider...

==> default: Importing base box 'centos/7'...

==> default: Matching MAC address for NAT networking...

==> default: Checking if box 'centos/7' version '2004.01' is up to date...

==> default: Setting the name of the VM: centos7_default_1613521493035_2746

==> default: Clearing any previously set network interfaces...

==> default: Preparing network interfaces based on configuration...

default: Adapter 1: nat

==> default: Forwarding ports...

default: 22 (guest) => 2222 (host) (adapter 1)

==> default: Booting VM...

==> default: Waiting for machine to boot. This may take a few minutes...

default: SSH address: 127.0.0.1:2222

default: SSH username: vagrant

default: SSH auth method: private key

default: Warning: Connection aborted. Retrying...

default: Warning: Connection reset. Retrying...

default:

default: Vagrant insecure key detected. Vagrant will automatically replace

default: this with a newly generated keypair for better security.

default:

default: Inserting generated public key within guest...

default: Removing insecure key from the guest if it's present...

default: Key inserted! Disconnecting and reconnecting using new SSH key...

==> default: Machine booted and ready!

==> default: Checking for guest additions in VM...

default: No guest additions were detected on the base box for this VM! Guest

default: additions are required for forwarded ports, shared folders, host only

default: networking, and more. If SSH fails on this machine, please install

default: the guest additions and repackage the box to continue.

default:

default: This is not an error message; everything may continue to work properly,

default: in which case you may ignore this message.

==> default: Rsyncing folder: /cygdrive/c/vagrant/centos7/ => /vagrant

仮想環境へのログイン

現バージョンでは、Windowsでもvagrant sshで接続できた。

>vagrant ssh
[vagrant@localhost ~]$

1 2	>vagrant ssh [vagrant@localhost ~]$

バージョンを確認。

[vagrant@localhost ~]$ cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)

1 2	[vagrant@localhost ~]$ cat /etc/redhat-release CentOS Linux release 7.8.2003 (Core)

実行中の仮想環境の確認

仮想マシンを立ち上げたコンソールとは別のコンソールを立ち上げ、vagrant statusで確認。

C:\vagrant\centos7>vagrant status
Current machine states:

default                   running (virtualbox)

The VM is running. To stop this VM, you can run `vagrant halt` to
shut it down forcefully, or you can run `vagrant suspend` to simply
suspend the virtual machine. In either case, to restart it again,
simply run `vagrant up`.

C:\vagrant\centos7>vagrant status

Current machine states:

default running (virtualbox)

The VM is running. To stop this VM, you can run `vagrant halt` to

shut it down forcefully, or you can run `vagrant suspend` to simply

suspend the virtual machine. In either case, to restart it again,

simply run `vagrant up`.

仮想環境からのログアウト

CentOSのexitコマンドでログアウト。

[vagrant@localhost ~]$ exit
logout
Connection to 127.0.0.1 closed.

[vagrant@localhost ~]$ exit

logout

Connection to 127.0.0.1 closed.

仮想環境の停止

Windowsプロンプトでvagran haltコマンドを実行して停止。

>vagrant halt
==> default: Attempting graceful shutdown of VM...

1 2	>vagrant halt ==> default: Attempting graceful shutdown of VM...

仮想環境停止の確認

停止後のWindows環境でvagrant statusを実行すると仮想環境の停止を確認できる。

>vagrant status
Current machine states:

default                   poweroff (virtualbox)

The VM is powered off. To restart the VM, simply run `vagrant up`

>vagrant status

Current machine states:

default poweroff (virtualbox)

The VM is powered off. To restart the VM, simply run `vagrant up`

MySQL – 日付・時刻のフォーマット

2021-02-23 / tau / コメントする

概要

日付、時刻のフォーマットにはDATE_FORMAT()、TIME_FORMAT()を使う。

DATE_FORMAT(date, format);

TIME_FORMAT(time, format);

date、timeにはDATETIME型の値を入れてもよい。

書式の雛形

以下のデータで日付・時刻の書式を確認する。

mysql> SELECT * FROM test_datetime;
+---------------------+
| datetime            |
+---------------------+
| 2020-12-31 23:59:59 |
| 2021-01-01 00:01:02 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_datetime;

+---------------------+

| datetime |

+---------------------+

| 2020-12-31 23:59:59 |

| 2021-01-01 00:01:02 |

+---------------------+

2 rows in set (0.00 sec)

日付

日付4or2桁でゼロ埋め	`%Y %m %d`
日付ゼロ埋めなし	`%y %c %e`
月名(短縮／全部)	`%b / %M`
曜日名(番号／短縮／全部)	`%w / %a / %W`

※曜日番号は日曜～土曜が0～6

mysql> SELECT
    ->   DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',
    ->   DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',
    ->   DATE_FORMAT(datetime, '%b : %M') AS 月名,
    ->   DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日
    -> FROM test_datetime;
+----------------+--------------+----------------+--------------------+
| 日付0埋め      | 日付0なし    | 月名           | 曜日               |
+----------------+--------------+----------------+--------------------+
| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |
| 2021年01月01日 | 21年1月1日   | Jan : January  | 5 : Fri : Friday   |
+----------------+--------------+----------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',

-> DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',

-> DATE_FORMAT(datetime, '%b : %M') AS 月名,

-> DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日

-> FROM test_datetime;

+----------------+--------------+----------------+--------------------+

| 日付0埋め | 日付0なし | 月名 | 曜日 |

+----------------+--------------+----------------+--------------------+

| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |

| 2021年01月01日 | 21年1月1日 | Jan : January | 5 : Fri : Friday |

+----------------+--------------+----------------+--------------------+

2 rows in set (0.00 sec)

時刻

24時間制の時刻(ゼロ埋め)	%H
24時間制の時刻(ゼロ埋めなし)	%k
12時間制の時刻	%h
分(ゼロ埋め)	%i
秒(ゼロ埋め)	%s
AM/PM	`%p`

※12時間制の時刻は0時／12時の扱いに一貫性がない。

mysql> SELECT
    ->   TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',
    ->   TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',
    ->   TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',
    ->   TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'
    -> FROM test_datetime;
+--------------+--------------+-----------------+-----------------+
| 0埋め24H     | 0なし24H     | 0埋め12H        | 0なし12H        |
+--------------+--------------+-----------------+-----------------+
| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |
| 00時01分02秒 | 0時00分02秒  | 12時01分02秒 AM | 12時01分02秒 AM |
+--------------+--------------+-----------------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',

-> TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',

-> TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',

-> TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'

-> FROM test_datetime;

+--------------+--------------+-----------------+-----------------+

| 0埋め24H | 0なし24H | 0埋め12H | 0なし12H |

+--------------+--------------+-----------------+-----------------+

| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |

| 00時01分02秒 | 0時00分02秒 | 12時01分02秒 AM | 12時01分02秒 AM |

+--------------+--------------+-----------------+-----------------+

2 rows in set (0.00 sec)

Vagrant -vagrantfile

2021-02-23 / tau / コメントする

初期状態

VagrantとvirtualBoxのCentOS7をインストールして初期実行後のvagrantfileの内容。ファイルはCentOS7の環境構築で指定したディレクトリー下にある。

# -*- mode: ruby -*-
# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure
# configures the configuration version (we support older styles for
# backwards compatibility). Please don't change it unless you know what
# you're doing.
Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  # For a complete reference, please see the online documentation at
  # https://docs.vagrantup.com.

  # Every Vagrant development environment requires a box. You can search for
  # boxes at https://vagrantcloud.com/search.
  config.vm.box = "bento/centos-7.7"

  # Disable automatic box update checking. If you disable this, then
  # boxes will only be checked for updates when the user runs
  # `vagrant box outdated`. This is not recommended.
  # config.vm.box_check_update = false

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine. In the example below,
  # accessing "localhost:8080" will access port 80 on the guest machine.
  # NOTE: This will enable public access to the opened port
  # config.vm.network "forwarded_port", guest: 80, host: 8080

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine and only allow access
  # via 127.0.0.1 to disable public access
  # config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

  # Create a private network, which allows host-only access to the machine
  # using a specific IP.
  # config.vm.network "private_network", ip: "192.168.33.10"

  # Create a public network, which generally matched to bridged network.
  # Bridged networks make the machine appear as another physical device on
  # your network.
  # config.vm.network "public_network"

  # Share an additional folder to the guest VM. The first argument is
  # the path on the host to the actual folder. The second argument is
  # the path on the guest to mount the folder. And the optional third
  # argument is a set of non-required options.
  # config.vm.synced_folder "../data", "/vagrant_data"
  config.vm.synced_folder "./share", "/home/vagrant/share" # フォルダー共有のため追加

  # Provider-specific configuration so you can fine-tune various
  # backing providers for Vagrant. These expose provider-specific options.
  # Example for VirtualBox:
  #
  # config.vm.provider "virtualbox" do |vb|
  #   # Display the VirtualBox GUI when booting the machine
  #   vb.gui = true
  #
  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"
  # end
  #
  # View the documentation for the provider you are using for more
  # information on available options.

  # Enable provisioning with a shell script. Additional provisioners such as
  # Ansible, Chef, Docker, Puppet and Salt are also available. Please see the
  # documentation for more information about their specific syntax and use.
  # config.vm.provision "shell", inline: <<-SHELL
  #   apt-get update
  #   apt-get install -y apache2
  # SHELL
end

# -*- mode: ruby -*-

# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure

# configures the configuration version (we support older styles for

# backwards compatibility). Please don't change it unless you know what

# you're doing.

Vagrant.configure("2") do |config|

# The most common configuration options are documented and commented below.

# For a complete reference, please see the online documentation at

# https://docs.vagrantup.com.

# Every Vagrant development environment requires a box. You can search for

# boxes at https://vagrantcloud.com/search.

config.vm.box = "bento/centos-7.7"

# Disable automatic box update checking. If you disable this, then

# boxes will only be checked for updates when the user runs

# `vagrant box outdated`. This is not recommended.

# config.vm.box_check_update = false

# Create a forwarded port mapping which allows access to a specific port

# within the machine from a port on the host machine. In the example below,

# accessing "localhost:8080" will access port 80 on the guest machine.

# NOTE: This will enable public access to the opened port

# config.vm.network "forwarded_port", guest: 80, host: 8080

# Create a forwarded port mapping which allows access to a specific port

# within the machine from a port on the host machine and only allow access

# via 127.0.0.1 to disable public access

# config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

# Create a private network, which allows host-only access to the machine

# using a specific IP.

# config.vm.network "private_network", ip: "192.168.33.10"

# Create a public network, which generally matched to bridged network.

# Bridged networks make the machine appear as another physical device on

# your network.

# config.vm.network "public_network"

# Share an additional folder to the guest VM. The first argument is

# the path on the host to the actual folder. The second argument is

# the path on the guest to mount the folder. And the optional third

# argument is a set of non-required options.

# config.vm.synced_folder "../data", "/vagrant_data"

config.vm.synced_folder "./share", "/home/vagrant/share" # フォルダー共有のため追加

# Provider-specific configuration so you can fine-tune various

# backing providers for Vagrant. These expose provider-specific options.

# Example for VirtualBox:

# config.vm.provider "virtualbox" do |vb|

# # Display the VirtualBox GUI when booting the machine

# vb.gui = true

# # Customize the amount of memory on the VM:

# vb.memory = "1024"

# end

# View the documentation for the provider you are using for more

# information on available options.

# Enable provisioning with a shell script. Additional provisioners such as

# Ansible, Chef, Docker, Puppet and Salt are also available. Please see the

# documentation for more information about their specific syntax and use.

# config.vm.provision "shell", inline: <<-SHELL

# apt-get update

# apt-get install -y apache2

# SHELL

end

実質的な内容は以下のとおり。

Vagrant.configure("2") do |config|
  config.vm.box = "centos/7"
end

Vagrant.configure("2") do |config|

config.vm.box = "centos/7"

end

Vagrant – メモリーとディスクの容量設定

2021-02-23 / tau / コメントする

準備

vagrantfile中、virtualBox設定の部分をコメントアウトし有効にする。

  config.vm.provider "virtualbox" do |vb|
  #   # Display the VirtualBox GUI when booting the machine
  #   vb.gui = true
  #
  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"
  end

config.vm.provider "virtualbox" do |vb|

# # Display the VirtualBox GUI when booting the machine

# vb.gui = true

# # Customize the amount of memory on the VM:

# vb.memory = "1024"

end

メモリーの設定

メモリーの割り当て容量設定は、vagrantfileに1行書くか、コメントアウトされている以下の行のコメントを外して容量を書き換えるとよい。

  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"

1 2	# # Customize the amount of memory on the VM: # vb.memory = "1024"

数値はMB単位のメモリー割当量で、上記は1024MB = 1GBを意味している。

ディスク容量の設定

仮想マシンのディスクに割り当てる容量を設定・変更する場合、プラグインをインストールした上でvagrantfileに1行追加する。

プラグインのインストール

ホスト側でvagrant-disksizeプラグインをインストールする。

>vagrant plugin install vagrant-disksize
Installing the 'vagrant-disksize' plugin. This can take a few minutes...
Fetching vagrant-disksize-0.1.3.gem
Installed the plugin 'vagrant-disksize (0.1.3)'!

>vagrant plugin install vagrant-disksize

Installing the 'vagrant-disksize' plugin. This can take a few minutes...

Fetching vagrant-disksize-0.1.3.gem

Installed the plugin 'vagrant-disksize (0.1.3)'!

vagrantfileへの追加

以下の1行をvagrantfileに追加する(***に割り当てる容量をGB単位で指定)。

config.disksize.size = "***GB"

    config.disksize.size = "50GB"

1	config.disksize.size = "50GB"

設定の反映

メモリー／ディスクの設定をvagrantfileに書いた後、vagrantを起動／再起動する。

メモリーとディスクの容量のみ設定したvagrantfileの有効部分は以下のようになる。

Vagrant.configure("2") do |config|
  config.vm.provider "virtualbox" do |vb|
    vb.memory = "2048"
    config.disksize.size = "50GB"
  end
end

Vagrant.configure("2") do |config|

config.vm.provider "virtualbox" do |vb|

vb.memory = "2048"

config.disksize.size = "50GB"

end

メモリー容量の確認

[vagrant@localhost ~]$ free m
              total        used        free      shared  buff/cache   available
Mem:        1882092       94880     1590960        8632      196252     1639440
Swap:       2097148           0     2097148

[vagrant@localhost ~]$ free m

total used free shared buff/cache available

Mem: 1882092 94880 1590960 8632 196252 1639440

Swap: 2097148 0 2097148

ディスク容量の確認

[vagrant@localhost ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        912M     0  912M   0% /dev
tmpfs           919M     0  919M   0% /dev/shm
tmpfs           919M  8.5M  911M   1% /run
tmpfs           919M     0  919M   0% /sys/fs/cgroup
/dev/sda1        40G  3.2G   37G   8% /
tmpfs           184M     0  184M   0% /run/user/1000

[vagrant@localhost ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 912M 0 912M 0% /dev

tmpfs 919M 0 919M 0% /dev/shm

tmpfs 919M 8.5M 911M 1% /run

tmpfs 919M 0 919M 0% /sys/fs/cgroup

/dev/sda1 40G 3.2G 37G 8% /

tmpfs 184M 0 184M 0% /run/user/1000

MySQL – DISTINCTによる重複の排除

2021-02-23 / tau / コメントする

概要

DISTINCTはフィールドの重複を除いた結果を返す。

確認に使用するデータ

以下のデータを使う。日時ごとのパスタのメニューの注文票で、パスタはソースと麺の種類の組み合わせ。

mysql> SELECT * FROM pasta_order;
+---------------------+----------+----------------+-------+
| order_time          | source   | pasta          | price |
+---------------------+----------+----------------+-------+
| 2021-02-01 10:30:00 | トマト   | スパゲッティー |   900 |
| 2021-02-01 11:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-01 12:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-01 12:30:00 | クリーム | リングイネ     |   850 |
| 2021-02-01 13:00:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 11:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-02 12:00:00 | クリーム | スパゲッティー |   850 |
| 2021-02-02 12:30:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 13:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-03 10:30:00 | クリーム | スパゲッティー |   850 |
| 2021-02-03 11:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-03 11:30:00 | オイル   | フィットチーネ |   800 |
| 2021-02-03 12:00:00 | クリーム | リングイネ     |   850 |
| 2021-02-03 12:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-03 13:00:00 | オイル   | スパゲッティー |   800 |
+---------------------+----------+----------------+-------+
15 rows in set (0.00 sec)

mysql> SELECT * FROM pasta_order;

+---------------------+----------+----------------+-------+

+---------------------+----------+----------------+-------+

| 2021-02-01 10:30:00 | トマト | スパゲッティー | 900 |

| 2021-02-01 11:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-01 12:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-01 12:30:00 | クリーム | リングイネ | 850 |

| 2021-02-01 13:00:00 | トマト | リングイネ | 900 |

| 2021-02-02 11:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-02 12:00:00 | クリーム | スパゲッティー | 850 |

| 2021-02-02 12:30:00 | トマト | リングイネ | 900 |

| 2021-02-02 13:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-03 10:30:00 | クリーム | スパゲッティー | 850 |

| 2021-02-03 11:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-03 11:30:00 | オイル | フィットチーネ | 800 |

| 2021-02-03 12:00:00 | クリーム | リングイネ | 850 |

| 2021-02-03 12:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-03 13:00:00 | オイル | スパゲッティー | 800 |

+---------------------+----------+----------------+-------+

15 rows in set (0.00 sec)

フィールドの重複を除いた抽出

まずソース、麺それぞれのフィールドから重複を除いた結果を抽出。ソースの種類、麺の種類は3種類のため、それぞれ3つのレコードが返される。

mysql> SELECT
    ->   DISTINCT source
    -> FROM pasta_order;
+----------+
| source   |
+----------+
| トマト   |
| オイル   |
| クリーム |
+----------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   DISTINCT pasta
    -> FROM pasta_order;
+----------------+
| pasta          |
+----------------+
| スパゲッティー |
| フィットチーネ |
| リングイネ     |
+----------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT source

-> FROM pasta_order;

+----------+

| source |

+----------+

| トマト |

| オイル |

| クリーム |

+----------+

3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT pasta

-> FROM pasta_order;

+----------------+

| pasta |

+----------------+

| スパゲッティー |

| フィットチーネ |

| リングイネ |

+----------------+

3 rows in set (0.00 sec)

日付による集計

日付単位の表示

DATETIMEで登録されているフィールドを日付のみの表現に変更する。その結果、同じ日付で複数のソースと麺の組み合わせのレコードになる。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   source,
    ->   pasta
    -> FROM pasta_order;
+------------+----------+----------------+
| date       | source   | pasta          |
+------------+----------+----------------+
| 2021-02-01 | トマト   | スパゲッティー |
| 2021-02-01 | トマト   | フィットチーネ |
| 2021-02-01 | オイル   | スパゲッティー |
| 2021-02-01 | クリーム | リングイネ     |
| 2021-02-01 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-02 | クリーム | スパゲッティー |
| 2021-02-02 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-03 | クリーム | スパゲッティー |
| 2021-02-03 | オイル   | スパゲッティー |
| 2021-02-03 | オイル   | フィットチーネ |
| 2021-02-03 | クリーム | リングイネ     |
| 2021-02-03 | トマト   | フィットチーネ |
| 2021-02-03 | オイル   | スパゲッティー |
+------------+----------+----------------+
15 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> source,

-> pasta

-> FROM pasta_order;

+------------+----------+----------------+

| date | source | pasta |

+------------+----------+----------------+

| 2021-02-01 | トマト | スパゲッティー |

| 2021-02-01 | トマト | フィットチーネ |

| 2021-02-01 | オイル | スパゲッティー |

| 2021-02-01 | クリーム | リングイネ |

| 2021-02-01 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-02 | クリーム | スパゲッティー |

| 2021-02-02 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-03 | クリーム | スパゲッティー |

| 2021-02-03 | オイル | スパゲッティー |

| 2021-02-03 | オイル | フィットチーネ |

| 2021-02-03 | クリーム | リングイネ |

| 2021-02-03 | トマト | フィットチーネ |

| 2021-02-03 | オイル | スパゲッティー |

+------------+----------+----------------+

15 rows in set (0.00 sec)

日付ごとのユニークデータの数

単一のフィールド

単一フィールド中のユニークなデータの数は以下のようにしてカウントする。

COUNT(DISTINCT col)

以下は、日付ごとにグルーピングし、各日付で重複を除いたソースの数をカウントしている。2/2はクリームとトマトの2種類だけというのが反映されている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT source)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+------------------------+
| date       | COUNT(DISTINCT source) |
+------------+------------------------+
| 2021-02-01 |                      3 |
| 2021-02-02 |                      2 |
| 2021-02-03 |                      3 |
+------------+------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT source)

-> FROM pasta_order

-> GROUP BY date;

+------------+------------------------+

| date | COUNT(DISTINCT source) |

+------------+------------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+------------------------+

3 rows in set (0.00 sec)

また、日付ごとの麺の種類の数もカウントしてみる。2/2がスパゲッティーとリングイネの2種類になっている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT pasta)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+-----------------------+
| date       | COUNT(DISTINCT pasta) |
+------------+-----------------------+
| 2021-02-01 |                     3 |
| 2021-02-02 |                     2 |
| 2021-02-03 |                     3 |
+------------+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT pasta)

-> FROM pasta_order

-> GROUP BY date;

+------------+-----------------------+

| date | COUNT(DISTINCT pasta) |

+------------+-----------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+-----------------------+

3 rows in set (0.00 sec)

複数フィールド

複数フィールドの組み合わせがユニークなものの数のカウントは以下のようにする。

COUNT(DISTINCT CONCAT(col1, col2, ...))

以下の例では、ソースとパスタの組み合わせがユニークなものの数を日付ごとに集計する。2/2はトマトスパゲッティーが2回、2/3はオイルスパゲッティーがあり、重複を除いてカウントしている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT CONCAT(source, pasta))
    -> FROM pasta_order
    -> GROUP BY date;
+------------+---------------------------------------+
| date       | COUNT(DISTINCT CONCAT(source, pasta)) |
+------------+---------------------------------------+
| 2021-02-01 |                                     5 |
| 2021-02-02 |                                     3 |
| 2021-02-03 |                                     5 |
+------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT CONCAT(source, pasta))

-> FROM pasta_order

-> GROUP BY date;

+------------+---------------------------------------+

| date | COUNT(DISTINCT CONCAT(source, pasta)) |

+------------+---------------------------------------+

| 2021-02-01 | 5 |

| 2021-02-02 | 3 |

| 2021-02-03 | 5 |

+------------+---------------------------------------+

3 rows in set (0.00 sec)

MySQL – グループごとの最大値のレコード

2021-02-23 / tau / コメントする

概要

インラインビューとグルーピングを使って、レコード中のある列の値が最大値／最小値となるレコードをグループごとに抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは各科目ごとの最高点をとった学生と教科を抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

手順

グループごとの最大値

科目によってグルーピングし、各科目の最高得点を得る。

SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

1	SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;
+---------+------------+
| subject | MAX(score) |
+---------+------------+
| 数学    |         85 |
| 物理    |         90 |
| 英語    |         85 |
| 文学    |         95 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

+---------+------------+

| subject | MAX(score) |

+---------+------------+

| 数学 | 85 |

| 物理 | 90 |

| 英語 | 85 |

| 文学 | 95 |

+---------+------------+

4 rows in set (0.00 sec)

インラインビューによる最高得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最高スコアの列が追加される。

SELECT name, subject, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
;

SELECT name, subject, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

;

mysql> SELECT name, subject, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> ;
+------+---------+---------+---------+
| name | subject | max_sub | max_scr |
+------+---------+---------+---------+
| 江藤 | 文学    | 数学    |      85 |
| 江藤 | 物理    | 数学    |      85 |
| 江藤 | 数学    | 数学    |      85 |
| 宇藤 | 文学    | 数学    |      85 |
| 宇藤 | 英語    | 数学    |      85 |
| 宇藤 | 物理    | 数学    |      85 |
| 伊藤 | 文学    | 数学    |      85 |
| 伊藤 | 数学    | 数学    |      85 |
| 安藤 | 英語    | 数学    |      85 |
| 安藤 | 物理    | 数学    |      85 |
| 安藤 | 数学    | 数学    |      85 |
| 江藤 | 文学    | 物理    |      90 |
| 江藤 | 物理    | 物理    |      90 |
| 江藤 | 数学    | 物理    |      90 |
| 宇藤 | 文学    | 物理    |      90 |
.....

mysql> SELECT name, subject, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> ;

+------+---------+---------+---------+

+------+---------+---------+---------+

| 江藤 | 文学 | 数学 | 85 |

| 江藤 | 物理 | 数学 | 85 |

| 江藤 | 数学 | 数学 | 85 |

| 宇藤 | 文学 | 数学 | 85 |

| 宇藤 | 英語 | 数学 | 85 |

| 宇藤 | 物理 | 数学 | 85 |

| 伊藤 | 文学 | 数学 | 85 |

| 伊藤 | 数学 | 数学 | 85 |

| 安藤 | 英語 | 数学 | 85 |

| 安藤 | 物理 | 数学 | 85 |

| 安藤 | 数学 | 数学 | 85 |

| 江藤 | 文学 | 物理 | 90 |

| 江藤 | 物理 | 物理 | 90 |

| 江藤 | 数学 | 物理 | 90 |

| 宇藤 | 文学 | 物理 | 90 |

.....

最高得点のレコードの追加

各学生・科目のレコードのうち、スコアが最高スコアに等しいレコードを抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

mysql> SELECT name, subject, score, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> WHERE subject = max_sub AND score = max_scr
    -> ;
+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

mysql> SELECT name, subject, score, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> WHERE subject = max_sub AND score = max_scr

-> ;

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

MySQL – シェアの計算

2021-02-23 / tau / コメントする

概要

インラインビューを使って、各レコードのある列の値のシェアを計算できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM components;
+-----------+----------+
| component | quantity |
+-----------+----------+
| 成分A     |       60 |
| 成分B     |       35 |
| 成分C     |       15 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM components;

+-----------+----------+

| component | quantity |

+-----------+----------+

| 成分A | 60 |

| 成分B | 35 |

| 成分C | 15 |

+-----------+----------+

3 rows in set (0.00 sec)

ここでは各成分の量quantityのシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

手順

合計値の計算

合計値はSUM()関数で計算。

SELECT SUM(quantity) FROM components;

1	SELECT SUM(quantity) FROM components;

mysql> SELECT SUM(quantity) FROM components;
+---------------+
| SUM(quantity) |
+---------------+
|           110 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(quantity) FROM components;

+---------------+

| SUM(quantity) |

+---------------+

| 110 |

+---------------+

1 row in set (0.00 sec)

インラインビューによる合計列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな合計値の列が追加される。

SELECT component, quantity, total
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT component, quantity, total

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT component, quantity, total
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+
| component | quantity | total |
+-----------+----------+-------+
| 成分A     |       60 |   110 |
| 成分B     |       35 |   110 |
| 成分C     |       15 |   110 |
+-----------+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT component, quantity, total

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+

| component | quantity | total |

+-----------+----------+-------+

| 成分A | 60 | 110 |

| 成分B | 35 | 110 |

| 成分C | 15 | 110 |

+-----------+----------+-------+

3 rows in set (0.00 sec)

各レコードのシェアの計算

各レコードのquantityを合計値で割ってシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

MySQL – 最小値・最大値のレコードの抽出

2021-02-23 / tau / コメントする

概要

インラインビューを使って、レコード中のある列が最大値／最小値となるレコードを抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは、全学生・全教科のうち、最低得点とその得点を取った学生と科目を表示させる。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

手順

最低得点の取得

まず、全体の最低得点は以下で得られる。

SELECT MIN(score) AS min_score FROM student_scores;

1	SELECT MIN(score) AS min_score FROM student_scores;

mysql> SELECT MIN(score) AS min_score FROM student_scores;
+-----------+
| min_score |
+-----------+
|        65 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MIN(score) AS min_score FROM student_scores;

+-----------+

| min_score |

+-----------+

| 65 |

+-----------+

1 row in set (0.00 sec)

インラインビューによる最低得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最低スコアの列が追加される。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 安藤 | 数学    |    80 |        65 |
| 安藤 | 物理    |    90 |        65 |
| 安藤 | 英語    |    75 |        65 |
| 伊藤 | 数学    |    65 |        65 |
| 伊藤 | 文学    |    85 |        65 |
| 宇藤 | 物理    |    70 |        65 |
| 宇藤 | 英語    |    85 |        65 |
| 宇藤 | 文学    |    95 |        65 |
| 江藤 | 数学    |    85 |        65 |
| 江藤 | 物理    |    80 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
11 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 安藤 | 数学 | 80 | 65 |

| 安藤 | 物理 | 90 | 65 |

| 安藤 | 英語 | 75 | 65 |

| 伊藤 | 数学 | 65 | 65 |

| 伊藤 | 文学 | 85 | 65 |

| 宇藤 | 物理 | 70 | 65 |

| 宇藤 | 英語 | 85 | 65 |

| 宇藤 | 文学 | 95 | 65 |

| 江藤 | 数学 | 85 | 65 |

| 江藤 | 物理 | 80 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

11 rows in set (0.00 sec)

最低得点レコードの抽出

各学生のレコードのうち、スコアが最低スコアに等しいレコードを抽出する。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

MySQL – インラインビュー

2021-02-23 / tau / コメントする

確認用データ

以下のデータを使う。

mysql> SELECT * FROM subject_scores;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM subject_scores;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

基本形

FROM句の中にSELECTクエリーを書いてエイリアスをつけると、それがテーブルとして参照される。

SELECT *
FROM
  (SELECT subject, score FROM subject_scores) as view
;

SELECT *

FROM

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

インラインビューにはエイリアスを付けないとエラーになる。

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores)
    -> ;
ERROR 1248 (42000): Every derived table must have its own alias

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores)

-> ;

ERROR 1248 (42000): Every derived table must have its own alias

テーブルとインラインビューの併記

エイリアスによる修飾

テーブルとインラインビューを並べて使える。不明確さをなくすために、テーブル名またはエイリアスで列名を修飾する。

SELECT org.subject, org.score, view.subject, view.score
FROM
  subject_scores as org,
  (SELECT subject, score FROM subject_scores) as view
;

SELECT org.subject, org.score, view.subject, view.score

FROM

subject_scores as org,

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT org.subject, org.score, view.subject, view.score
    -> FROM
    ->   subject_scores as org,
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+-------------+-------+
| subject     | score | subject     | score |
+-------------+-------+-------------+-------+
| philosophy  |    80 | mathematics |    85 |
| literature  |  NULL | mathematics |    85 |
| geology     |    70 | mathematics |    85 |
| chemistry   |    80 | mathematics |    85 |
| physics     |    95 | mathematics |    85 |
| mathematics |    85 | mathematics |    85 |
| philosophy  |    80 | physics     |    95 |
| literature  |  NULL | physics     |    95 |
| geology     |    70 | physics     |    95 |
| chemistry   |    80 | physics     |    95 |
| physics     |    95 | physics     |    95 |
| mathematics |    85 | physics     |    95 |
| philosophy  |    80 | chemistry   |    80 |
| literature  |  NULL | chemistry   |    80 |
| geology     |    70 | chemistry   |    80 |
| chemistry   |    80 | chemistry   |    80 |
| physics     |    95 | chemistry   |    80 |
| mathematics |    85 | chemistry   |    80 |
| philosophy  |    80 | geology     |    70 |
| literature  |  NULL | geology     |    70 |
| geology     |    70 | geology     |    70 |
| chemistry   |    80 | geology     |    70 |
| physics     |    95 | geology     |    70 |
| mathematics |    85 | geology     |    70 |
| philosophy  |    80 | literature  |  NULL |
| literature  |  NULL | literature  |  NULL |
| geology     |    70 | literature  |  NULL |
| chemistry   |    80 | literature  |  NULL |
| physics     |    95 | literature  |  NULL |
| mathematics |    85 | literature  |  NULL |
| philosophy  |    80 | philosophy  |    80 |
| literature  |  NULL | philosophy  |    80 |
| geology     |    70 | philosophy  |    80 |
| chemistry   |    80 | philosophy  |    80 |
| physics     |    95 | philosophy  |    80 |
| mathematics |    85 | philosophy  |    80 |
+-------------+-------+-------------+-------+
36 rows in set (0.00 sec)

mysql> SELECT org.subject, org.score, view.subject, view.score

-> FROM

-> subject_scores as org,

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+-------------+-------+

+-------------+-------+-------------+-------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

| geology | 70 | physics | 95 |

| chemistry | 80 | physics | 95 |

| physics | 95 | physics | 95 |

| mathematics | 85 | physics | 95 |

| philosophy | 80 | chemistry | 80 |

| geology | 70 | chemistry | 80 |

| chemistry | 80 | chemistry | 80 |

| physics | 95 | chemistry | 80 |

| mathematics | 85 | chemistry | 80 |

| philosophy | 80 | geology | 70 |

| geology | 70 | geology | 70 |

| chemistry | 80 | geology | 70 |

| physics | 95 | geology | 70 |

| mathematics | 85 | geology | 70 |

| philosophy | 80 | philosophy | 80 |

| geology | 70 | philosophy | 80 |

| chemistry | 80 | philosophy | 80 |

| physics | 95 | philosophy | 80 |

| mathematics | 85 | philosophy | 80 |

+-------------+-------+-------------+-------+

36 rows in set (0.00 sec)

列名のエイリアス

インラインビューの列名にエイリアスを定義してもよい。

SELECT subject, score, view_subject, view_score
FROM
  subject_scores,
  (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
;

SELECT subject, score, view_subject, view_score

FROM

subject_scores,

(SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

;

mysql> SELECT subject, score, view_subject, view_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
    -> ;
+-------------+-------+--------------+------------+
| subject     | score | view_subject | view_score |
+-------------+-------+--------------+------------+
| philosophy  |    80 | mathematics  |         85 |
| literature  |  NULL | mathematics  |         85 |
| geology     |    70 | mathematics  |         85 |
| chemistry   |    80 | mathematics  |         85 |
| physics     |    95 | mathematics  |         85 |
| mathematics |    85 | mathematics  |         85 |
| philosophy  |    80 | physics      |         95 |
| literature  |  NULL | physics      |         95 |
.....

mysql> SELECT subject, score, view_subject, view_score

-> FROM

-> subject_scores,

-> (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

-> ;

+-------------+-------+--------------+------------+

+-------------+-------+--------------+------------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

.....

集約関数の利用

インラインビューで集約関数を使うと威力を発揮。以下の例では、全レコード中の最高得点と最低得点を各レコードに付加している。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> ;
+-------------+-------+-----------+-----------+
| subject     | score | max_score | min_score |
+-------------+-------+-----------+-----------+
| mathematics |    85 |        95 |        70 |
| physics     |    95 |        95 |        70 |
| chemistry   |    80 |        95 |        70 |
| geology     |    70 |        95 |        70 |
| literature  |  NULL |        95 |        70 |
| philosophy  |    80 |        95 |        70 |
+-------------+-------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> ;

+-------------+-------+-----------+-----------+

+-------------+-------+-----------+-----------+

| mathematics | 85 | 95 | 70 |

| physics | 95 | 95 | 70 |

| chemistry | 80 | 95 | 70 |

| geology | 70 | 95 | 70 |

| literature | NULL | 95 | 70 |

| philosophy | 80 | 95 | 70 |

+-------------+-------+-----------+-----------+

6 rows in set (0.00 sec)

このようにすると、最高得点や最低得点のレコードを抽出することができる。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
WHERE
  score = max_score
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

WHERE

score = max_score

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> WHERE
    ->   score = max_score
    -> ;
+---------+-------+-----------+-----------+
| subject | score | max_score | min_score |
+---------+-------+-----------+-----------+
| physics |    95 |        95 |        70 |
+---------+-------+-----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> WHERE

-> score = max_score

-> ;

+---------+-------+-----------+-----------+

+---------+-------+-----------+-----------+

| physics | 95 | 95 | 70 |

+---------+-------+-----------+-----------+

1 row in set (0.00 sec)

MySQL – グループ化と集約～GROUP BY

2021-02-23 / tau / コメントする

概要

グループ化はGROUP BY文でグループ化する列を指定する。ほとんどの場合、以下のように集約関数を適用する。

SELECT FUNC(col) FROM table GROUP BY col;

上記の分では、table中の列colによってグループ化し、そのグループごとに集約関数FUNC()を適用した結果を返す。

実行例

以下のデータを使う。学生ごとの履修科目と得点のデータのイメージ。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

COUNTの例

以下の例では、学生でグルーピングして科目数をカウントしている。これにより、各学生の履修科目数が得られる。

mysql> SELECT name, COUNT(subject) FROM student_scores
    -> GROUP BY name;
+------+----------------+
| name | COUNT(subject) |
+------+----------------+
| 安藤 |              3 |
| 伊藤 |              2 |
| 宇藤 |              3 |
| 江藤 |              3 |
+------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT name, COUNT(subject) FROM student_scores

-> GROUP BY name;

+------+----------------+

| name | COUNT(subject) |

+------+----------------+

| 安藤 | 3 |

| 伊藤 | 2 |

| 宇藤 | 3 |

| 江藤 | 3 |

+------+----------------+

4 rows in set (0.00 sec)

以下の例では、科目でグルーピングして科目数をカウントしている。これにより、各科目の被履修数が得られる。

mysql> SELECT subject, COUNT(subject) FROM student_scores
    -> GROUP BY subject;
+---------+----------------+
| subject | COUNT(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 英語    |              2 |
| 文学    |              3 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, COUNT(subject) FROM student_scores

-> GROUP BY subject;

+---------+----------------+

| subject | COUNT(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 英語 | 2 |

| 文学 | 3 |

+---------+----------------+

4 rows in set (0.00 sec)

MIN/MAXの例

以下の例では、科目でグルーピングして点数の最小値と最大値を表示させている。

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores
    -> GROUP BY subject;
+---------+------------+------------+
| subject | MIN(score) | MAX(score) |
+---------+------------+------------+
| 数学    |         65 |         85 |
| 物理    |         70 |         90 |
| 英語    |         75 |         85 |
| 文学    |         65 |         95 |
+---------+------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores

-> GROUP BY subject;

+---------+------------+------------+

| subject | MIN(score) | MAX(score) |

+---------+------------+------------+

| 数学 | 65 | 85 |

| 物理 | 70 | 90 |

| 英語 | 75 | 85 |

| 文学 | 65 | 95 |

+---------+------------+------------+

4 rows in set (0.00 sec)

SELECTでsubjectも表示させている。上記の例ではsubjectでグルーピングしているので適切に表示されるが、subjectの値に使われるレコードは不定なので不適切に使うと以下のようになる。

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores;
+---------+------------+------------+
| subject | MIN(score) | MAX(score) |
+---------+------------+------------+
| 数学    |         65 |         95 |
+---------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores;

+---------+------------+------------+

| subject | MIN(score) | MAX(score) |

+---------+------------+------------+

| 数学 | 65 | 95 |

+---------+------------+------------+

1 row in set (0.00 sec)

SUM/AVGの例

以下の例では、学生ごとの合計点数と教科ごとの平均点数をグルーピングにより計算している。

mysql> SELECT name, SUM(score) FROM student_scores
    -> GROUP BY name;
+------+------------+
| name | SUM(score) |
+------+------------+
| 安藤 |        245 |
| 伊藤 |        150 |
| 宇藤 |        250 |
| 江藤 |        230 |
+------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, AVG(score) FROM student_scores
    -> GROUP BY subject;
+---------+------------+
| subject | AVG(score) |
+---------+------------+
| 数学    |    76.6667 |
| 物理    |    80.0000 |
| 英語    |    80.0000 |
| 文学    |    81.6667 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT name, SUM(score) FROM student_scores

-> GROUP BY name;

+------+------------+

| name | SUM(score) |

+------+------------+

| 安藤 | 245 |

| 伊藤 | 150 |

| 宇藤 | 250 |

| 江藤 | 230 |

+------+------------+

4 rows in set (0.00 sec)

mysql> SELECT subject, AVG(score) FROM student_scores

-> GROUP BY subject;

+---------+------------+

| subject | AVG(score) |

+---------+------------+

| 数学 | 76.6667 |

| 物理 | 80.0000 |

| 英語 | 80.0000 |

| 文学 | 81.6667 |

+---------+------------+

4 rows in set (0.00 sec)

HAVING

各科目が履修された数は以下のようだった。

mysql> SELECT subject, COUNT(subject) FROM student_scores
    -> GROUP BY subject;
+---------+----------------+
| subject | COUNT(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 英語    |              2 |
| 文学    |              3 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, COUNT(subject) FROM student_scores

-> GROUP BY subject;

+---------+----------------+

| subject | COUNT(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 英語 | 2 |

| 文学 | 3 |

+---------+----------------+

4 rows in set (0.00 sec)

ここで、履修数が2よりも多い科目だけを抽出したいとする。

WHERE句でできそうだがエラーになる。

mysql> SELECT subject, count(subject) FROM student_scores
    -> GROUP BY subject
    -> WHERE count(subject) > 2;
ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count(subject) > 2' at line 3

mysql> SELECT subject, count(subject) FROM student_scores

-> GROUP BY subject

-> WHERE count(subject) > 2;

ERROR 1064 (42000): You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count(subject) > 2' at line 3

集約結果によって抽出したい場合は、HAVING句を使う。

mysql> SELECT subject, count(subject) FROM student_scores
    -> GROUP BY subject
    -> HAVING count(subject) > 2;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 文学    |              3 |
+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> GROUP BY subject

-> HAVING count(subject) > 2;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 文学 | 3 |

+---------+----------------+

3 rows in set (0.00 sec)

WHERE句との組み合わせの例。scoreが80以上の履修数を科目ごとにカウント。

mysql> SELECT subject, count(subject) FROM student_scores
    -> WHERE score >= 80
    -> GROUP BY subject;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              2 |
| 物理    |              2 |
| 文学    |              2 |
| 英語    |              1 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> WHERE score >= 80

-> GROUP BY subject;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 2 |

| 物理 | 2 |

| 文学 | 2 |

| 英語 | 1 |

+---------+----------------+

4 rows in set (0.00 sec)

さらに、scoreが80以上の数が1より大きい科目のみHAVING句で取り出す。

mysql> SELECT subject, count(subject) FROM student_scores
    -> WHERE score >= 80
    -> GROUP BY subject
    -> HAVING count(subject) > 1;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              2 |
| 物理    |              2 |
| 文学    |              2 |
+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> WHERE score >= 80

-> GROUP BY subject

-> HAVING count(subject) > 1;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 2 |

| 物理 | 2 |

| 文学 | 2 |

+---------+----------------+

3 rows in set (0.00 sec)