报错是说语法错了难道是因为我的数据库的问题

    <insert id="insertProductSellDaily" parameterType="ProductSellDaily">
        insert into tb_product_sell_daily (product_id,shop_id,create_time,total) values
        (
        select product_id,shop_id,create_time,count(product_id) as total from
        tb_user_product_map
        where date_format(create_time,'%Y-%m-%d')=
        date_sub(curdate(),interval 1 day) GROUP by product_id
        )
        <!--调用group by count才能生效-->
    </insert>
</mapper>

SQL: insert into tb_product_sell_daily (product_id,shop_id,create_time,total) values ( select product_id,shop_id,create_time,count(product_id) as total from tb_user_product_map where date_format(create_time,'%Y-%m-%d')= date_sub(curdate(),interval 1 day) GROUP by product_id )

### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select product_id,shop_id,create_time,count(product_id) as total from

tb' at line 3

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select product_id,shop_id,create_time,count(product_id) as total from

tb' at line 3

肖小小 2019-09-28

源自：项目：校园商铺——框架大换血 4-3 ProductSellDailyDao的开发与测试

收起

3回答

好帮手慕柯南 2019-09-28 19:06:01

同学你好！

这次的报错信息是同学的mysql版本较高，默认是ONLY_FULL_GROUP_BY SQL模式，MySQL将拒绝选择列表所以报错了，同学使用以下命令重设一下模式：

set @@global.sql_mode=`STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, 
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`;

然后重启msql测试一下。

如果我的回答解决了你的疑惑，请采纳，祝学习愉快~

收起回答

提问者肖小小 #1

好像不行耶。 ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of ' ERROR_FOR_DIVISION_BY_ZERO'

2019-09-28 19:12:57
慕设计6382757 回复提问者肖小小 #2

好几种方法，你可以试试https://www.cnblogs.com/chancy/p/10026097.html

2019-09-29 10:56:11

好帮手慕柯南 2019-09-28 18:43:54

同学你好！

第一个报错是因为多写了values
同学所说的老师的代码的问题，是同学忘记写要插入的对应的列了呢
老师的代码是下面样子：

INSERT
INTO
tb_product_sell_daily(product_id,shop_id,create_time,total)
(
SELECT
product_id,shop_id,date_format(create_time,'%Y-%m-%d'),count(product_id)
AS total FROM
tb_user_product_map
WHERE
date_format(create_time,'%Y-%m-%d') =
date_sub(curdate(),interval 1
day) GROUP BY product_id)

如果我的回答解决了你的疑惑，请采纳，祝学习愉快~

收起回答

提问者肖小小 #1

### SQL: insert into tb_product_sell_daily(product_id,shop_id,create_time,total) ( select product_id,shop_id,create_time,count(product_id) as total from tb_user_product_map where date_format(create_time,'%Y-%m-%d')= date_sub(curdate(),interval 1 day) GROUP by product_id ) ### Cause: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'o2o.tb_user_product_map.shop_id' 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 'o2o.tb_user_product_map.shop_id' 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)

2019-09-28 18:46:32

提问者肖小小 2019-09-28 16:54:18

<insert id="insertProductSellDaily" parameterType="ProductSellDaily">
    insert into tb_product_sell_daily 
    (
    select product_id,shop_id,create_time,count(product_id) as total from
    tb_user_product_map
    where date_format(create_time,'%Y-%m-%d')=
    date_sub(curdate(),interval 1 day) GROUP by product_id
    )
    <!--调用group by count才能生效-->
</insert>

改成和老师一样也报错

### The error occurred while setting parameters

### SQL: insert into tb_product_sell_daily ( select product_id,shop_id,create_time,count(product_id) as total from tb_user_product_map where date_format(create_time,'%Y-%m-%d')= date_sub(curdate(),interval 1 day) GROUP by product_id )

### Cause: java.sql.SQLException: Column count doesn't match value count at row 1

; bad SQL grammar []; nested exception is java.sql.SQLException: Column count doesn't match value count at row 1

收起回答