用set和select查看某年龄段的人数_select输出年龄段，人数两个字段-优快云博客

本文链接：https://blog.youkuaiyun.com/weiyanghuadi/article/details/8718789

本文展示如何使用JSP和Java实现MySQL中的复杂SQL查询，包括设置变量来获取特定年龄段的人数总数，并进一步查询该年龄段内按音乐偏好分组的人数及占比。

在MySQL中，运行sql

set @total=(select count(*) from people where Age>20 and Age<=50);
select count(*),count(*)*100/@total as '%',Music from people where age>20 and age<50 group by Music;

在JSP中实现

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8"%>

<%@ page language="java" import="java.sql.*"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>

	<%
		String driver = "com.mysql.jdbc.Driver";
		Class.forName(driver).newInstance();

		Connection con = null;
		ResultSet rst = null;

		Statement stmt = null;

		try {
			String url = "jdbc:mysql://localhost/tutorial?user=root&password=HuaDi5";

			con = DriverManager.getConnection(url);
			stmt = con.createStatement();

			stmt.executeUpdate("set @total=(select count(*) from people where Age>20 and Age<=50)");
			rst=stmt.executeQuery("select @total from people");
			rst.next();
			out.println(rst.getInt(1));

		} catch (Exception e) {
			out.print("<h3>数据库系统出错</h3>");
			System.out.println(e.getMessage());
		} finally {
			if (rst != null)
				try {
					rst.close();
				} catch (Exception e) {
				}

			if (stmt != null)
				try {
					stmt.close();
				} catch (Exception e) {
				}

			if (con != null)
				try {
					con.close();
				} catch (Exception e) {
				}
		}
	%>

</body>
</html>