精华内容
下载资源
问答
  • select b.data_type,b.field_id,count(1) num from BSINERR b GROUP BY b.field_id,b.data_type; 结果为: 2、mybatis的mappe: <select id="countByFieldId" resultMap="byFiledId"> ...

    1、使用Navicat查询:

    select b.data_type,b.field_id,count(1) num from BSINERR b GROUP BY b.field_id,b.data_type;

    结果为:

     

    2、mybatis的mappe:

    <select id="countByFieldId" resultMap="byFiledId">
      select b.data_type,b.field_id,count(1) num from BSINERR b GROUP BY b.field_id,b.data_type
    </select>
    <resultMap id="byFiledId" type="java.util.Map">
      <result column="DATA_TYPE" property="dataType" jdbcType="VARCHAR" />
      <result column="FIELD_ID" property="fieldId" jdbcType="VARCHAR" />
      <result column="count(1)" property="num" jdbcType="VARCHAR"/>
    </resultMap>

    3、Dao用List<Map>接收

    List<Map> countByFieldId();
    

    运行结果为:

    {NUM=1, dataType=D2, fieldId=pid}
    {NUM=1, dataType=C1, fieldId=name} 
    {NUM=2, dataType=C1, fieldId=pid}
    

     

    展开全文
  • mybatis使用group by 分组查询返回为null

    千次阅读 2020-01-09 09:44:33
    博主在使用mybatis进行分组查询时数据库有数据,但是mybatis返回为null,使用mybatis版本为3.4.1 解决方法时在resultMap的result标签中添加 property属性 如下: ```java <resultMap id="deptMap" type=...

    博主在使用mybatis进行分组查询时数据库有数据,但是mybatis返回为null,使用mybatis版本为3.4.1
    解决方法时在resultMap的result标签中添加 property属性
    如下:

    
    ```java
    <resultMap id="deptMap" type="java.util.Map">
            <result column="id" property="id"/>
            <result column="dept_name" property="deptname"/>
            <result column="count(1)" property="count"/>
        </resultMap>
      
     <select id="getDeptByIdStep" resultMap="deptMap">
      select  id,dept_name,count(1)  from tbl_dept where dept_id=#{id} group by id;
     </select>
    

    博主在第一次使用时没有添加property导致mybatis返回null,添加后就可以正常返回。

    dao层代码

    public List<Map> getDeptByIdStep(Integer id);
    展开全文
  • mybatis使用group by返回sum,count数据

    万次阅读 2016-03-08 10:12:04
    对于这样的带有group by的语句,可以自定义返回类型  select sum(transAmt),count(*), respCode from transdetail where batchNo = #{batchNo,jdbcType=VARCHAR} GROUP BY respCode

        对于这样的带有group by的语句,可以自定义返回类型

    <select id="getAllInfoByRespCode" resultMap="allInfo">

            select sum(transAmt),count(*), respCode from transdetail where batchNo = #{batchNo,jdbcType=VARCHAR} GROUP BY respCode

        </select>

        <resultMap id="allInfo" type="java.util.Map">
            <result column="sum(transAmt)" property="sum" jdbcType="VARCHAR"/>
            <result column="count(*)" property="count" jdbcType="VARCHAR"/>
            <result column="respCode" property="respCode" jdbcType="VARCHAR"/>
        </resultMap>

    展开全文
  • mybatisgroup by有关的bug 连表查询中,attr_id和attr_name是一一对应的关系 #Condition1:GROUP BY pssav.`attr_id` SELECT pssav.`attr_id`,pssav.`attr_name`,GROUP_CONCAT(DISTINCT pssav.`attr_value`) FROM ...

    mybatis的group by有关的问题

    连表查询中,attr_id和attr_name是一一对应的关系

    #Condition1:GROUP BY pssav.`attr_id`
    SELECT pssav.`attr_id`,pssav.`attr_name`,GROUP_CONCAT(DISTINCT pssav.`attr_value`) 
    FROM `pms_sku_info` info 
    LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`
    WHERE info.`spu_id`=4
    GROUP BY pssav.`attr_id`,pssav.`attr_name`
    
    #Condition2:GROUP BY pssav.`attr_id`,pssav.`attr_name`;
    SELECT pssav.`attr_id`,pssav.`attr_name`,GROUP_CONCAT(DISTINCT pssav.`attr_value`) 
    FROM `pms_sku_info` info 
    LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`
    WHERE info.`spu_id`=4
    GROUP BY pssav.`attr_id`,pssav.`attr_name`;
    

    在Mysql中查询为一样的结果,但是在在mybatis中写sql语句时,condition2可以正常运行,但是condition1会报错

    org.springframework.jdbc.BadSqlGrammarException: 
    ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    ### The error may exist in file [D:\workspace\java\taomall\taomall-product\target\classes\mapper\product\SkuSaleAttrValueDao.xml]
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: SELECT             pssav.`attr_id` attr_id,             pssav.`attr_name` attr_name,             GROUP_CONCAT(DISTINCT pssav.`attr_value`) attr_values             FROM `pms_sku_info` info         LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`         WHERE info.`spu_id`=?         GROUP BY pssav.`attr_id`;
    ### Cause: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    
    	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    	at com.sun.proxy.$Proxy106.selectList(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    	at com.sun.proxy.$Proxy128.getSaleAttrsBySpuId(Unknown Source)
    	at com.tao.taomall.product.service.impl.SkuSaleAttrValueServiceImpl.getSaleAttrsBySpuId(SkuSaleAttrValueServiceImpl.java:35)
    	at com.tao.taomall.product.service.impl.SkuSaleAttrValueServiceImpl$$FastClassBySpringCGLIB$$5dd8c914.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
    	at com.tao.taomall.product.service.impl.SkuSaleAttrValueServiceImpl$$EnhancerBySpringCGLIB$$27ebc456.getSaleAttrsBySpuId(<generated>)
    	at com.tao.taomall.product.TaomallProductApplicationTests.testSpuInfo(TaomallProductApplicationTests.java:61)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
    	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
    	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
    	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
    	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
    	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
    	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
    Caused by: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    	at com.sun.proxy.$Proxy160.query(Unknown Source)
    	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
    	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
    	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:92)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
    	... 43 more
    
    
    
    展开全文
  • select **C.ITEM_NAME**, **Y.PLAN_YEAR**, SUM(X.GAS_CONS_SCALE)GAS_CONS_SCALE, SUM(X.CY_DRILL_NUM)CY_DRILL_NUM...SUM(X.BY_DRILL_NUM)BY_DRILL_NUM, SUM(X.CY_END_DRILL_NUM)CY_END_DRILL_NUM, SUM(X.BY_END_D...
  • 写了几百遍的group by,居然查询出来只有一条数据, 但是把执行语句放入mysql中执行又没问题 找了返回值的问题也没事 group by 参数,这样的组合遇到的坑 错误的语句如下 SELECT * FROM tb_equip_archives...
  • mybatis使用函数 再group by 传参报错

    千次阅读 2018-09-07 16:31:19
    ### Cause: java.sql.SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式 错误配置: ...
  • 报错信息:......not in GROUP BY clause and contains nonaggregated column ...... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_...
  • mybatis group by substr函数传参报错

    千次阅读 2018-12-01 15:52:31
    ### Cause: java.sql.SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式 SELECT SUBSTR( region_code, 1,#{ queryMap.groupCodeLength, jdbcType = INTEGER } ) AS "region_code", count( ...
  • 关于Mybatis plus 使用QueryWrapper,group by + having ,自定义查询字段使用方法 例: QueryWrapper<CustomerBussUser> qw = new QueryWrapper<>(); qw.select("CUST_ID,...qw.groupBy("cust_Id"); qw.
  • group by查询后汇总行数 select count(*) from (select distinct e.id from e left join a on e.id=a.e_id where 1=1 and org_cd=#{orgCd}) child distinct e.id 改为 count(distinct e.id)也可以画蛇添足,后来...
  • 分组统计: SELECT user_id,company_name,count(*) FROM t_message GROUP BY ...mybatis使用group by返回多个值,包含count数据,该怎么接返回值: https://blog.csdn.net/weixin_43276786/article/details/94547839
  • mybatis group by查询返回map类型

    千次阅读 2017-06-12 21:11:00
    故事的发生是这样的....额e,可是为嘛不使用简单便捷的 group by 语句呢 我们知道MyBatis提供了selectMap的方法,查询结果为hashmap。查询的时候,可以配置相应的传入参数和查询返回结果。 对应dao ...
  • 使用group by分组查询,将查询结果封装成类3.group by分组查询,将结果封装为map。直接封装为map?List1. 最简单但性能最差的做法 在逻辑层分多次对数据库进行查询。伪代码如下。 List<String> nameList ; ...
  • MyBatis使用总结

    千次阅读 2017-08-04 11:27:17
    MyBatis使用总结
  • 原先的sql语句 mysql没问题。。达梦数据报错。。 SELECT t1.id, t1.cityState, t1.countyName, sum(t1.lossRatio) as lossRatio, sum(t1.lossArea) as lossArea, ... sum(t1.medium) as medium,...
  • Mybatis 使用教程

    2019-12-24 23:29:40
    Mybatis 使用教程 参考资料 官网(http://mybatis.org/mybatis-3/zh/index.html) 示例源码地址 https://gitee.com/cq-laozhou/mybaits-source-code-analyzer 入门介绍 参考:...
  • <if test="orderByClause != null and !(orderByClause.indexOf(';')>=0) and !(orderByClause.indexOf('\'')>=0)" > ORDER BY ${orderByClause} </if>
  • MyBatis使用与分析

    2019-10-03 22:47:29
    MyBatis使用与分析 MyBatis使用 1:MyBatis初始化对象 configuration configuration全局性配置 具体属性说明见官方文档:http://mybatis.github.com/mybatis-3/zh/configuration.html <!-- 独立使用...
  • Mybatis使用常见技巧

    2016-09-29 15:23:30
    Mybatis使用常见技巧
  • 记录一次mybatis使用

    2021-02-03 13:58:28
    mybatis的xml中使用时不要使用<WHERE></WHERE>否则会提示没有where关键字,填写where关键字,数据源无法初始化,导致工程无法启动 ...中不要包含order bygroup by limit等关键字,否则会报错 ...
  • Mybatis 使用count

    千次阅读 2017-10-03 18:33:12
    java方法,这里得注意方法的返回值 List,Object>> findGroup(Integer user_id); 第一步编写resultMap <result column="assrotment" property="assortment"></result>

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,778
精华内容 4,711
关键字:

mybatis使用groupby