GROUP BY子句与SELECT语句一起使用,如果数据相同,则用于形成子集,通常,此子句后跟ORDER BY子句,并放在WHERE子句之后。
GROUP BY - 语法
以下是GROUP BY子句的语法-
ij>SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .;
GROUP BY - 命令行示例
假设无涯教程在数据库中有一个名为Employees的表,其中包含以下记录-
ID |NAME |SALARY |LOCATION ------------------------------------------------------------------ 1 |Amit |30000 |Hyderabad 2 |Rahul |39000 |Lucknow 3 |Renuka |50000 |Hyderabad 4 |Learnfk |15000 |Vishakhapatnam 5 |Kalyan |40000 |Hyderabad 6 |Trupthi |45000 |Vishakhapatnam 7 |Raghav |12000 |Lucknow 8 |Suchatra |33000 |Vishakhapatnam 9 |Rizwan |20000 |Lucknow
以下带有GROUP BY子句的SELECT语句根据位置对表进行分组,它显示给某个位置的员工的工资总额。
ij> SELECT Location, SUM(Salary) from Employees GROUP BY Location;
这将生成以下输出-
LOCATION |2 ------------------------------------------------------- Hyderabad |120000 Lucknow |71000 Vishakhapatnam |93000 3 rows selected
以相同的方式,以下查询查找在某个位置作为员工薪水所花在员工身上的平均金额。
ij> SELECT Location, AVG(Salary) from Employees GROUP BY Location;
这将生成以下输出-
LOCATION |2 ----------------------------------------------------- Hyderabad |40000 Lucknow |23666 Vishakhapatnam |31000 3 rows selected
GROUP BY - JDBC示例
以下JDBC示例演示了如何使用 Group By 子句以及如何使用JDBC程序在Apache Derby中的表上执行CURD操作,在这里,无涯教程使用嵌入式驱动程序连接到名为sampleDB的数据库(如果不存在则创建)。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; public class GroupByClauseExample { public static void main(String args[]) throws Exception { //注册驱动 Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); //创建连接 String URL="jdbc:derby:sampleDB;create=true"; Connection conn=DriverManager.getConnection(URL); //获取Statement对象 Statement stmt=conn.createStatement(); //创建表结构,插入一些测试数据 stmt.execute("CREATE TABLE EmployeesData( " + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, " + "Name VARCHAR(255), " + "Salary INT NOT NULL, " + "Location VARCHAR(255), " + "PRIMARY KEY (Id))"); stmt.execute("INSERT INTO EmployeesData(Name, Salary, Location) " + "VALUES ('Amit', 30000, 'Hyderabad'), " + "('Rahul', 39000, 'Lucknow'), " + "('Renuka', 50000, 'Hyderabad'), " + "('Learnfk', 15000, 'Vishakhapatnam'), " + "('Kalyan', 40000, 'Hyderabad'), " + "('Trupthi', 45000, 'Vishakhapatnam'), " + "('Raghav', 12000, 'Lucknow'), " + "('Suchatra', 33000, 'Vishakhapatnam'), " + "('Rizwan', 20000, 'Lucknow')"); //执行SQL语句 String query="SELECT Location, SUM(Salary) from EmployeesData GROUP BY Location"; ResultSet rs=stmt.executeQuery(query); while(rs.next()) { System.out.println("Location: "+rs.getString(1)); System.out.println("Sum of salary: "+rs.getString(2)); System.out.println(" "); } } }
在执行上述程序时,您将获得以下输出-
Location: Hyderabad Sum of salary: 120000 Location: Lucknow Sum of salary: 71000 Location: Vishakhapatnam Sum of salary: 93000
参考链接
https://www.learnfk.com/derby/apache-derby-group-by-clause.html
标签:Vishakhapatnam,Hyderabad,GROUP,Lucknow,子句,无涯,Location,Derby,Group From: https://blog.51cto.com/u_14033984/7798779