SQLで「あるカウントが1より大きい」行を簡単選択!WHERE句とHAVING句を使いこなそう
SQL で「あるカウントが 1 より大きい」行を選択する方法
例え:
顧客テーブルがあり、各顧客に複数の注文があるとします。この場合、注文が 1 件を超える顧客のみを取得したい場合があります。
解決策:
この問題は、WHERE
句または HAVING
句を使用して解決できます。
WHERE 句を使用する場合:
SELECT *
FROM customers
WHERE (
SELECT COUNT(*)
FROM orders
WHERE customers.id = orders.customer_id
) > 1;
このクエリは、次のことを行います。
customers
テーブルからすべての行を選択します。- 各顧客について、
orders
テーブルから注文のカウントをサブクエリで取得します。 - 注文のカウントが 1 より大きい顧客のみを選択します。
SELECT c.*, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING order_count > 1;
customers
テーブル (c
) とorders
テーブル (o
) を顧客 ID で結合します。- 各顧客について、注文のカウントを
order_count
というエイリアス付きで集計します。
WHERE
句は、よりシンプルな構文で、サブクエリを使用する必要がないため、高速で読みやすい場合があります。HAVING
句は、集計結果に対して条件を指定できるため、より柔軟なクエリを作成できます。
補足:
- 上記の例では、
customers
とorders
というテーブル名を使用していますが、実際のテーブル名に置き換えてください。 - 必要に応じて、選択する列を変更できます。
- WHERE 句と HAVING 句を組み合わせて、より複雑なクエリを作成することもできます。
顧客テーブルと注文テーブル
顧客テーブル:
列名 | データ型 | 説明 |
---|---|---|
id | int | 顧客 ID (主キー) |
name | varchar(255) | 顧客名 |
列名 | データ型 | 説明 |
---|---|---|
id | int | 注文 ID (主キー) |
customer_id | int | 顧客 ID (外部キー) |
product_id | int | 商品 ID |
quantity | int | 注文数量 |
WHERE 句を使用する場合
SELECT *
FROM customers
WHERE (
SELECT COUNT(*)
FROM orders
WHERE customers.id = orders.customer_id
) > 1;
id | name |
---|---|
1 | 田中太郎 |
2 | 佐藤花子 |
HAVING 句を使用する場合
SELECT c.*, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING order_count > 1;
id | name | order_count |
---|---|---|
1 | 田中太郎 | 2 |
2 | 佐藤花子 | 3 |
ウィンドウ関数を使用する:
この方法は、MySQL 8 以降で使用できます。
SELECT c.*,
COUNT(*) OVER (PARTITION BY c.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE order_count > 1;
id | name | order_count |
---|---|---|
1 | 田中太郎 | 2 |
2 | 佐藤花子 | 3 |
COUNT(*) OVER (PARTITION BY c.id)
というウィンドウ関数を使用して、各顧客の注文数を計算します。
CTE (Common Table Expression) を使用する:
WITH order_counts AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT c.*
FROM customers c
JOIN order_counts o ON c.id = o.customer_id
WHERE order_count > 1;
id | name | order_count |
---|---|---|
1 | 田中太郎 | 2 |
2 | 佐藤花子 | 3 |
- このクエリは、
order_counts
という CTE を作成します。この CTE は、orders
テーブルから顧客 ID と注文数を集計します。
- ウィンドウ関数は、MySQL 8 以降でのみ使用できるという制限があります。
- CTE は、より複雑なクエリを作成する場合に役立ちます。
上記以外にも、状況によっては他の方法が考えられる場合があります。
sql mysql