/** * 转换交叉表 * @param db * @param sql 需要转换的sql * @param colsql 可以为空,如果需要强制设定行标题字段内容或排序,可以这样指定如:"select id from table order by id desc" * @param r_HCols 逗号分隔的行标题字段列表 * @param r_VCols 逗号分隔的列标题字段列表 * @param r_VCol 需要分组的字段 * @param orderby 列标题字段的排序SQL * //效果如下: // 仓库1 仓库2 ... //商品1 //商品2 * @return 转换后的sql * @throws Exception */ public static String GetSql2CrossTableSQL(EADatabase db, String sql, String colsql,String r_HCols,String r_VCols,String r_VCol, String orderby) throws Exception { boolean ismssql = true; String strplus = "||"; if (!ismssql) strplus = "+"; String colheader = r_VCols.replaceAll(",", strplus + "'_'" + strplus); String _sql = ""; if (colsql.length() > 0) _sql = colsql; else _sql = EAFunc.format("select distinct %s c from (%s) t ORDER BY %s", colheader, sql, colheader); EADS colds = db.QuerySQL(_sql); _sql = "select " + r_HCols; int rows = colds.getRowCount(); for (int i = 0;i < rows;i++) { String headtitle = EAFunc.Replace(colds.getStringAt(i, 0),"'","''"); _sql += EAFunc.format(",\nsum(case %s when '%s' then %s else 0 end) \"%s\"", new Object[] {colheader,headtitle, r_VCol, headtitle}); } if(EAFunc.isEmptyStr(orderby)) orderby = r_HCols; _sql += "\nfrom (" + sql + ")t \ngroup by " + r_HCols + " \norder by " + orderby; return _sql; }
select count(*) from test where age between 0 and 10 union all select count(*) from test where age between 10 and 20 union all select count(*) from test where age between 20 and 30 union all select count(*) from test where age between 30 and 40 union all select count(*) from test where age between 50 and 60
现在有一个表sys-info,字段为id,hd id hd 1 52 2 32 3 68 4 75 5 28 6 26 7 45 8 15 9 86 10 58 按10-30,30-50,50-70,70-90区段查询统计出各个区段有多少条记录 查询语句如何写SELECT SUM(CASE WHEN hd>=10 and hd<30 THEN 1 ELSE 0 END) as "[10-30]", SUM(CASE WHEN hd>=30 and hd<50 THEN 1 ELSE 0 END) as "[30-50]", SUM(CASE WHEN hd>=50 and hd<70 THEN 1 ELSE 0 END) as "[50-70]", SUM(CASE WHEN hd>=70 and hd<90 THEN 1 ELSE 0 END) as "[70-90]" FROM sys-info WHERE hd>=10 AND hd<90;
这个简单,给你个最简单和理解的办法,我常用的,效率还高, 不准告诉别人哦,开玩笑了 原理:例如56,57,53,52除以10,得到5.*,取整,去掉末尾的小数就是50系列的了 按照整数分组即可 SELECT ROUND(HD/10),COUNT(*) FROM TAB GROUP BY ROUND(HD/10)
SELECT COUNT(0) FROM tab GROUP BY CEIL((hd - 10)/20);
* 转换交叉表
* @param db
* @param sql 需要转换的sql
* @param colsql 可以为空,如果需要强制设定行标题字段内容或排序,可以这样指定如:"select id from table order by id desc"
* @param r_HCols 逗号分隔的行标题字段列表
* @param r_VCols 逗号分隔的列标题字段列表
* @param r_VCol 需要分组的字段
* @param orderby 列标题字段的排序SQL
* //效果如下:
// 仓库1 仓库2 ...
//商品1
//商品2
* @return 转换后的sql
* @throws Exception
*/
public static String GetSql2CrossTableSQL(EADatabase db, String sql,
String colsql,String r_HCols,String r_VCols,String r_VCol, String orderby) throws Exception
{
boolean ismssql = true;
String strplus = "||";
if (!ismssql)
strplus = "+";
String colheader = r_VCols.replaceAll(",", strplus + "'_'" + strplus);
String _sql = "";
if (colsql.length() > 0)
_sql = colsql;
else
_sql = EAFunc.format("select distinct %s c from (%s) t ORDER BY %s", colheader, sql, colheader);
EADS colds = db.QuerySQL(_sql);
_sql = "select " + r_HCols;
int rows = colds.getRowCount();
for (int i = 0;i < rows;i++)
{
String headtitle = EAFunc.Replace(colds.getStringAt(i, 0),"'","''");
_sql += EAFunc.format(",\nsum(case %s when '%s' then %s else 0 end) \"%s\"",
new Object[] {colheader,headtitle, r_VCol, headtitle});
}
if(EAFunc.isEmptyStr(orderby))
orderby = r_HCols;
_sql += "\nfrom (" + sql + ")t \ngroup by " + r_HCols + " \norder by " + orderby;
return _sql;
}
select count(*) from test where age between 10 and 20 union all
select count(*) from test where age between 20 and 30 union all
select count(*) from test where age between 30 and 40 union all
select count(*) from test where age between 50 and 60
id hd
1 52
2 32
3 68
4 75
5 28
6 26
7 45
8 15
9 86
10 58
按10-30,30-50,50-70,70-90区段查询统计出各个区段有多少条记录
查询语句如何写SELECT SUM(CASE WHEN hd>=10 and hd<30 THEN 1 ELSE 0 END) as "[10-30]",
SUM(CASE WHEN hd>=30 and hd<50 THEN 1 ELSE 0 END) as "[30-50]",
SUM(CASE WHEN hd>=50 and hd<70 THEN 1 ELSE 0 END) as "[50-70]",
SUM(CASE WHEN hd>=70 and hd<90 THEN 1 ELSE 0 END) as "[70-90]"
FROM sys-info
WHERE hd>=10 AND hd<90;
不准告诉别人哦,开玩笑了
原理:例如56,57,53,52除以10,得到5.*,取整,去掉末尾的小数就是50系列的了
按照整数分组即可
SELECT ROUND(HD/10),COUNT(*)
FROM TAB
GROUP BY ROUND(HD/10)