SELECT
t.order_id,
t.goods_name,
t.price,
t.months,
t.pay_time,
s.real_name,
cgs.BJMC,
cgs.GRADEINFO_NAME,
cgs.SCHOOL_NAME
FROM
g_order t
JOIN
t_student s
ON t.buyer_id=s.studentbaseinfo_id
JOIN
(
SELECT
c.CLASSINFO_ID,
c.BJMC,
g.GRADEINFO_NAME,
sl.SCHOOL_NAME
FROM
t_classinfo c
JOIN
t_gradeinfo g
ON c.GRADEINFO_ID=g.GRADEINFO_ID
JOIN
t_school sl
ON c.SCHOOLBASEINFO_ID=sl.SCHOOLBASEINFO_ID
WHERE
1=1
) cgs
ON s.classinfo_id=cgs.CLASSINFO_ID
WHERE
1=1
ORDER BY
t.pay_time DESC limit ?设计的表太多,请大神看下下面的java查询语句:
sql.append(" SELECT");
sql.append(" t.order_id,");
sql.append(" t.goods_name,");
sql.append(" t.price,");
sql.append(" t.months,");
sql.append(" t.pay_time,");
sql.append(" s.real_name,");
sql.append(" cgs.BJMC,");
sql.append(" cgs.GRADEINFO_NAME,");
sql.append(" cgs.SCHOOL_NAME ");
sql.append(" FROM ");
sql.append(" g_order t ");
sql.append(" JOIN ");
sql.append(" t_student s ");
sql.append(" ON ");
sql.append(" t.buyer_id=s.studentbaseinfo_id ");
sql.append(" JOIN ");
sql.append(" (");
sql.append(" SELECT ");
sql.append(" c.CLASSINFO_ID, ");
sql.append(" c.BJMC, ");
sql.append(" g.GRADEINFO_NAME, ");
sql.append(" sl.SCHOOL_NAME ");
sql.append(" FROM ");
sql.append(" t_classinfo c ");
sql.append(" JOIN t_gradeinfo g ");
sql.append(" ON c.GRADEINFO_ID=g.GRADEINFO_ID ");
sql.append(" JOIN t_school sl ");
sql.append(" ON c.SCHOOLBASEINFO_ID=sl.SCHOOLBASEINFO_ID ");
sql.append(" WHERE 1=1 ");
if(StringUtils.isNotBlank(clazzId)){
sql.append(" AND c.CLASSINFO_ID= :clzzzID ");
map.put("clzzzID", clazzId);
}
if(StringUtils.isNotBlank(gradeId)){
sql.append(" AND g.GRADEINFO_ID= :gradeID ");
map.put("gradeID", gradeId);
}
if(StringUtils.isNotBlank(schoolId)){
sql.append(" AND sl.SCHOOLBASEINFO_ID= :schoolID ");
map.put("schoolID", schoolId);
}
sql.append(" ) cgs");
sql.append(" ON ");
sql.append(" s.classinfo_id=cgs.CLASSINFO_ID ");
sql.append(" WHERE 1=1 ");
if(beginDate != null){
sql.append(" AND t.pay_time > :beginDate ");
map.put("beginDate", beginDate);
}
if(endDate != null){
sql.append(" AND t.pay_time < :endDate ");
map.put("endDate", endDate);
}
if(StringUtils.isNotBlank(stuName)){
sql.append(" AND s.stuName like :stuName ");
map.put("stuName", "%" + stuName + "%");
}
sql.append(" ORDER BY t.pay_time DESC ");求教大神优化,不胜感激优化sql
t.order_id,
t.goods_name,
t.price,
t.months,
t.pay_time,
s.real_name,
cgs.BJMC,
cgs.GRADEINFO_NAME,
cgs.SCHOOL_NAME
FROM
g_order t
JOIN
t_student s
ON t.buyer_id=s.studentbaseinfo_id
JOIN
(
SELECT
c.CLASSINFO_ID,
c.BJMC,
g.GRADEINFO_NAME,
sl.SCHOOL_NAME
FROM
t_classinfo c
JOIN
t_gradeinfo g
ON c.GRADEINFO_ID=g.GRADEINFO_ID
JOIN
t_school sl
ON c.SCHOOLBASEINFO_ID=sl.SCHOOLBASEINFO_ID
WHERE
1=1
) cgs
ON s.classinfo_id=cgs.CLASSINFO_ID
WHERE
1=1
ORDER BY
t.pay_time DESC limit ?设计的表太多,请大神看下下面的java查询语句:
sql.append(" SELECT");
sql.append(" t.order_id,");
sql.append(" t.goods_name,");
sql.append(" t.price,");
sql.append(" t.months,");
sql.append(" t.pay_time,");
sql.append(" s.real_name,");
sql.append(" cgs.BJMC,");
sql.append(" cgs.GRADEINFO_NAME,");
sql.append(" cgs.SCHOOL_NAME ");
sql.append(" FROM ");
sql.append(" g_order t ");
sql.append(" JOIN ");
sql.append(" t_student s ");
sql.append(" ON ");
sql.append(" t.buyer_id=s.studentbaseinfo_id ");
sql.append(" JOIN ");
sql.append(" (");
sql.append(" SELECT ");
sql.append(" c.CLASSINFO_ID, ");
sql.append(" c.BJMC, ");
sql.append(" g.GRADEINFO_NAME, ");
sql.append(" sl.SCHOOL_NAME ");
sql.append(" FROM ");
sql.append(" t_classinfo c ");
sql.append(" JOIN t_gradeinfo g ");
sql.append(" ON c.GRADEINFO_ID=g.GRADEINFO_ID ");
sql.append(" JOIN t_school sl ");
sql.append(" ON c.SCHOOLBASEINFO_ID=sl.SCHOOLBASEINFO_ID ");
sql.append(" WHERE 1=1 ");
if(StringUtils.isNotBlank(clazzId)){
sql.append(" AND c.CLASSINFO_ID= :clzzzID ");
map.put("clzzzID", clazzId);
}
if(StringUtils.isNotBlank(gradeId)){
sql.append(" AND g.GRADEINFO_ID= :gradeID ");
map.put("gradeID", gradeId);
}
if(StringUtils.isNotBlank(schoolId)){
sql.append(" AND sl.SCHOOLBASEINFO_ID= :schoolID ");
map.put("schoolID", schoolId);
}
sql.append(" ) cgs");
sql.append(" ON ");
sql.append(" s.classinfo_id=cgs.CLASSINFO_ID ");
sql.append(" WHERE 1=1 ");
if(beginDate != null){
sql.append(" AND t.pay_time > :beginDate ");
map.put("beginDate", beginDate);
}
if(endDate != null){
sql.append(" AND t.pay_time < :endDate ");
map.put("endDate", endDate);
}
if(StringUtils.isNotBlank(stuName)){
sql.append(" AND s.stuName like :stuName ");
map.put("stuName", "%" + stuName + "%");
}
sql.append(" ORDER BY t.pay_time DESC ");求教大神优化,不胜感激优化sql
SELECT
t.order_id,
t.goods_name,
t.price,
t.months,
t.pay_time,
s.real_name,
c.CLASSINFO_ID,
c.BJMC,
g.GRADEINFO_NAME s1.SCHOOL_NAME
FROM g_order t
JOIN t_student s
ON t.buyer_id = s.studentbaseinfo_id
JOIN t_classinfo c
ON c.classinfo_id = s.classinfo_id
JOIN t_gradeinfo g
ON c.GRADEINFO_ID = g.GRADEINFO_ID
JOIN t_school sl
ON c.SCHOOLBASEINFO_ID = sl.SCHOOLBASEINFO_ID
WHERE 1 = 1
ORDER BY t.pay_time DESC
LIMIT ?
您看下下面java实现里面,我想着在做sql查询的时候,子查询里面的会先执行,也就是说在子查询中加上条件筛选出来一部分数据与外面的表再join,这样可行吗
是的 按索引排序,会快点,谢谢。您看下我在java代码中的子查询筛选有必要吗
这样的话,我的查询条件都写在外面的 where语句中,是吧
SELECT
t.order_id,
t.goods_name,
t.price,
t.months,
t.pay_time,
s.real_name,
c.BJMC,
g.GRADEINFO_NAME,
sl.SCHOOL_NAME
FROM
g_order t
INNER JOIN
t_student s
ON t.buyer_id=s.studentbaseinfo_id
INNER JOIN
(t_classinfo c
INNER JOIN t_gradeinfo g ON c.GRADEINFO_ID=g.GRADEINFO_ID
INNER JOIN t_school sl ON c.SCHOOLBASEINFO_ID=sl.SCHOOLBASEINFO_ID
WHERE 1=1)
ON s.classinfo_id=c.CLASSINFO_ID
WHERE 1=1
ORDER BY t.pay_time DESC LIMIT 1
您看下下面java实现里面,我想着在做sql查询的时候,子查询里面的会先执行,也就是说在子查询中加上条件筛选出来一部分数据与外面的表再join,这样可行吗
这样可行,但没必要,实际mysql自动优化查询分析器,会选取小表作为驱动的。
2.应该不慢,因为都是用的主键关联,最好给个explain计划看下。
3.java中不用sql.append(),直接用“+”会更快。
2:的确没必要在里面使用子查询
3:用“+”经过编译为class文件之后还是append()
您看下下面java实现里面,我想着在做sql查询的时候,子查询里面的会先执行,也就是说在子查询中加上条件筛选出来一部分数据与外面的表再join,这样可行吗
这样可行,但没必要,实际mysql自动优化查询分析器,会选取小表作为驱动的。嗯 ,是的,试了一下,好像没有太大的差别,谢谢您
show index from ..贴出来以供分析。