SQL系列2-分组数据

sql系列2-分组数据✅

```sql
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
```

编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行排序


在这个例子中，使用 GROUP BY 子句按订单号（order_num 列）分组数据，以便 COUNT(*)函数能够返回每个订单中的物品数目。HAVING 子句过滤数据，使得只返回包含三个或更多物品的订单。最后，用 ORDER BY子句排序输出。

 

```sql
SELECT vend_id, min(prod_price) as cheapest_item FROM products
group by vend_id
order by cheapest_item,vend_id ;
```


确定最佳顾客非常重要，请编写 SQL 语句，返回至少含 100 项的所有订单的订单号（OrderItems 表中的 order_num）

```sql
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num;
```


 

HAVING 子句是用来过滤特定的组的

提示：不要忘记 **ORDER BY**

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保

证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

```xml
<select id="pageUserData" resultType="com.shsajt.common.dto.UserDataPageDTO">
select
wap.user_id AS userId,
count(case when cw.work_type_id = 8 then cw.id end) AS checkCount,
count(case when cw.work_type_id = 9 then cw.id end) AS trainCount,
count(case when cw.work_type_id = 10 then cw.id end) AS guideCount,
count(case when cw.work_type_id = 11 then cw.id end) AS serveCount,
count(case when cw.work_type_id = 12 then cw.id end) AS meetCount,
count(case when cw.work_type_id = 13 then cw.id end) AS reportCount,
count(case when cw.work_type_id = 14 then cw.id end) AS otherCount
from
work_and_person wap
join
contract_work cw
on
wap.work_id = cw.id
<where>
cw.deleted = 0 and wap.deleted = 0
and cw.office_id = #{vo.officeId}
<if test="vo.beginCreate != null">
and cw.gmt_create <![CDATA[ >= ]]> #{vo.beginCreate}
</if>
<if test="vo.endCreate != null">
and cw.gmt_create <![CDATA[ <= ]]> #{vo.endCreate}
</if>
</where>
group by wap.user_id
order by cw.gmt_create desc
</select>
```

 

分组关键字 group by加上去之后，count(case when cw.work_type_id = 8 then cw.id end) AS checkCount 就是对分完组的每个小组的cw.work_type_id进行操作了而不是整个cw表的work_type_id。