sql ----------group by

来源：互联网发布：现在软件学什么好编辑：程序博客网时间：2024/06/14 23:02

看结果，果断搞懂group by || group by having

最终要的结果是：每个班年龄大于20岁的男性同学各是多少

create TABLE Table1

    (
        ID int identity(1,1) primary key NOT NULL,
        classid int,
        sex varchar(10),
        age int,
    )
     Insert into Table1 values(1,'男',20)
    Insert into Table1 values(2,'女',22)
    Insert into Table1 values(3,'男',23)
    Insert into Table1 values(4,'男',22)
    Insert into Table1 values(1,'男',24)
    Insert into Table1 values(2,'女',19)
    Insert into Table1 values(4,'男',26)
    Insert into Table1 values(1,'男',24)
    Insert into Table1 values(1,'男',20)
    Insert into Table1 values(2,'女',22)
    Insert into Table1 values(3,'男',23)
    Insert into Table1 values(4,'男',22)
    Insert into Table1 values(1,'男',24)
    Insert into Table1 values(2,'女',19)

SELECT classid , COUNT(*)as renshu FROM Table1 WHEREsex='男' group by classid ,age HAVING age>20

执行结果：

去掉age 再执行，就会报错：HAVING 子句中的列 'Table1.age' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

SELECT b.classid, SUM(b.renshu) FROM

(SELECT classid , COUNT(*)as renshu FROM Table1 WHERE sex='男' group by classid ,age HAVING age>20) b

GROUP BY b.classid

执行结果：