分组后只能查询分组的列和聚合列

GROUP BY 按照什么分组

运行顺序

运行顺序从高到低

HAVING 相当于 WHERE 只是运行顺序不一样

练习

-- 查询员工分布的居住地，以及每个居住地有多少名员工
-- 天府三街 3
SELECT location, count(id) as empnumber
FROM employee
GROUP BY location
HAVING empnumber>=40

-- 查询所有薪水在10000以上的员工的分布的居住地，然后仅得到聚集地大于30的结果
SELECT location, count(id) as empnumber
FROM employee
WHERE salary>=10000
GROUP BY location
HAVING count(id)>=30

子查询

练习题第三题第六题都用到啦子查询

什么是子查询

子查询就是将上次查询结果作为表继续查询

示例

-- 查询所有公司10年内入职的居住在万家湾的女员工数量
SELECT
c.`name`,
CASE
    WHEN r.number IS NULL THEN
        0
    ELSE
        1
END number
FROM
    company AS c
    LEFT JOIN (
    SELECT
        c.id,
        c.`name`,
        COUNT( e.id ) AS number 
    FROM
        employee AS e
        INNER JOIN department AS d ON d.id = e.deptId
        INNER JOIN company AS c ON c.id = d.companyId 
    WHERE
        TIMESTAMPDIFF(
            YEAR,
            e.joinDate,
        CURDATE()) <= 8 
        AND e.location LIKE '%万家湾%' 
        AND e.ismale = 0 
    GROUP BY
        c.id,
    c.`name` 
    ) AS r ON c.id = r.id

中的子查询为

  -- 查询 入职10年内且居住万家湾的女员工的数量然后按照公司名分组
(
    SELECT
        c.id,
        c.`name`,
        COUNT( e.id ) AS number 
    FROM
        employee AS e
        INNER JOIN department AS d ON d.id = e.deptId
        INNER JOIN company AS c ON c.id = d.companyId 
    WHERE
        TIMESTAMPDIFF(
            YEAR,
            e.joinDate,
        CURDATE()) <= 8 
        AND e.location LIKE '%万家湾%' 
        AND e.ismale = 0 
    GROUP BY
        c.id,
    c.`name` 
    ) AS r

只显示出有员工数量的，没有员工达到要求的没有显示，题意要求的所有公司，那吗就要在这个查询的基础上进行左连接

练习题

-- 查询所有薪水在10000以上的员工的分布的居住地，然后仅得到聚集地大于30的结果
SELECT location, count(id) as empnumber
FROM employee
WHERE salary>=10000
GROUP BY location
HAVING count(id)>=30

-- 2. 查询每个公司的员工数量
SELECT c.`name`, COUNT(e.id) as number
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY c.id, c.`name`

-- 查询所有公司10年内入职的居住在万家湾的女员工数量
SELECT
c.`name`,
CASE
    WHEN r.number IS NULL THEN
        0
    ELSE
        1
END number
FROM
    company AS c
    LEFT JOIN (
    SELECT
        c.id,
        c.`name`,
        COUNT( e.id ) AS number 
    FROM
        employee AS e
        INNER JOIN department AS d ON d.id = e.deptId
        INNER JOIN company AS c ON c.id = d.companyId 
    WHERE
        TIMESTAMPDIFF(
            YEAR,
            e.joinDate,
        CURDATE()) <= 8 
        AND e.location LIKE '%万家湾%' 
        AND e.ismale = 0 
    GROUP BY
        c.id,
    c.`name` 
    ) AS r ON c.id = r.id

-- 4. 查询渡一所有员工分布在哪些居住地，每个居住地的数量
SELECT e.location, count(e.id) as empnumber
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE c.`name` LIKE '%渡一%'
GROUP BY e.location

-- 5. 查询员工人数大于200的公司信息
SELECT * FROM company
WHERE id in (
SELECT c.id
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY c.id, c.`name`
HAVING count(e.id)>=200
)

-- 6. 查询渡一公司里比它平均工资高的员工
SELECT e.* 
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE c.`name` LIKE '%渡一%' AND
e.salary>(
-- 查询渡一的平均薪资
SELECT avg(e.salary)
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE c.`name` LIKE '%渡一%'
)

-- 7. 查询渡一所有名字为两个字和三个字的员工对应人数
SELECT CHAR_LENGTH(e.`name`) as 姓名长度, COUNT(E.ID) as 员工数量
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE c.`name` LIKE '%渡一%'
GROUP BY CHAR_LENGTH(e.`name`)
HAVING 姓名长度 in (2,3)

-- 8. 查询每个公司每个月的总支出薪水，并按照从低到高排序
SELECT c.`name`, SUM(e.salary) as sumofsalary
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY c.id, c.`name`
ORDER BY sumofsalary