SQLite GROUP BY子句

广告位

SQLite GROUP BY子句与SELECT语句一起使用,将相同的相同元素合并成一个组。 GROUP BY…

SQLite GROUP BY子句与SELECT语句一起使用,将相同的相同元素合并成一个组。

GROUP BY子句与SELECT语句中的WHERE子句一起使用,并且WHERE子句在ORDER BY子句之前。

语法:

SELECT column-list    FROM table_name    WHERE [ conditions ]    GROUP BY column1, column2....columnN    ORDER BY column1, column2....columnN  

下面举个例子来说明如何使用GROUP BY子句。 假设有一个名为student的表,具有以下数据:

sqlite> select * from student;  1|Maxsu|27|Shengzhen|20000.0  2|Minsu|25|Beijing|15000.0  3|Avgsu|23|Shanghai|2000.0  4|Linsu|25|Guangzhou|65000.0  5|Sqlsu|26|Haikou|25000.0  6|Javasu|21|Shengzhen|18000.0  sqlite>  

使用GROUP BY查询每位学生的费用总额:

SELECT NAME, SUM(FEES) FROM STUDENT GROUP BY NAME;  

执行上面代码,得到以下结果 –

SQLite GROUP BY子句

现在,使用以下INSERT语句向student表中创建一些记录,为了更好演示,插入的部分列的数据值是相同的:

INSERT INTO STUDENT VALUES (7, 'Linsu', 27, 'Haikou', 10000.00 );    INSERT INTO STUDENT VALUES (8, 'Minsu', 23, 'Guangzhou', 5000.00 );    INSERT INTO STUDENT VALUES (9, 'Maxsu', 23, 'Shenzhen', 9000.00 );  

执行上面语句插入数据后,现在表中存在的数据如下 –

sqlite> select * from student;  1|Maxsu|27|Shengzhen|20000.0  2|Minsu|25|Beijing|15000.0  3|Avgsu|23|Shanghai|2000.0  4|Linsu|25|Guangzhou|65000.0  5|Sqlsu|26|Haikou|25000.0  6|Javasu|21|Shengzhen|18000.0  7|Linsu|27|Haikou|10000.0  8|Minsu|23|Guangzhou|5000.0  9|Maxsu|23|Shenzhen|9000.0  sqlite>  

如上所示,现在有几个字段:name,agecity中的值是相同的。

现在,使用GROUP BY语句按NAME列来分组并对同分组内的所有的记录的fees列求和:

 select name, sum(fees) from student group by name;  

执行上面代码,得到以下结果 –

SQLite GROUP BY子句

可以使用ORDER BY子句和GROUP BY按升序或降序排列数据。

SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY NAME DESC;  -- 或者  SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY total_fees DESC;  

执行上面代码,得到以下结果 –

sqlite> SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY NAME DESC;  Sqlsu|25000.0  Minsu|20000.0  Maxsu|29000.0  Linsu|75000.0  Javasu|18000.0  Avgsu|2000.0  sqlite>  sqlite> SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY total_fees DESC;  Linsu|75000.0  Maxsu|29000.0  Sqlsu|25000.0  Minsu|20000.0  Javasu|18000.0  Avgsu|2000.0  sqlite>  

贺, 贺朝

关于作者: 贺朝

为您推荐