包含子查询的sql语句报错

[wmbxx]

2) EXPLAIN执行计划: +------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +======+===============+======================+==============+========+======================+======================+===========+=======+========+============+==================+ | 1 | PRIMARY | | None | ALL | None | None | None | None | 2 | 100 | Using filesort | +------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+ | 2 | DERIVED | user_aukd | None | range | idx_f_date_country_i | idx_f_date_country_i | 8 | None | 5 | 7.14 | Using index | | | | ay_stat | | | d,idx_user_activeuse | d | | | | | condition; Using | | | | | | | rkeepday_stat | | | | | | where | +------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+

3) 索引优化建议：

Executing SQL query where_clause f_date... ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 0% 0:00:00 Traceback (most recent call last): File "sqlai_helper.py", line 215, in Cardinality = count_column_value(table_name, where_field, mysql_settings, sample_size) File "sql_count_value.py", line 48, in count_column_value cursor.execute(sql) File "pymysql/cursors.py", line 153, in execute File "pymysql/cursors.py", line 322, in _query File "pymysql/connections.py", line 558, in query File "pymysql/connections.py", line 822, in _read_query_result File "pymysql/connections.py", line 1200, in read File "pymysql/connections.py", line 772, in _read_packet File "pymysql/protocol.py", line 221, in raise_for_error File "pymysql/err.py", line 143, in raise_mysql_exception pymysql.err.ProgrammingError: (1064, "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 '\n LIMIT 100000\n ) AS subquery\n GR' at line 4") [8060] Failed to execute script 'sqlai_helper' due to unhandled exception!

SQL： SELECT datediff(now(), fdate) AS 'daynum', fdate, newusernum, oneday, secondday, thirdday, fourthday, fifthday, sixthday, seventhday, fifteenthday, thirtiethday FROM (SELECT f_date AS fdate, SUM(f_active_num) AS newusernum, SUM(f_one_day) AS oneday, SUM(f_second_day) AS secondday, SUM(f_third_day) AS thirdday, SUM(f_fourth_day) AS fourthday, SUM(f_fifth_day) AS fifthday, SUM(f_sixth_day) AS sixthday, SUM(f_seventh_day) AS seventhday, SUM(f_fifteenth_day) AS fifteenthday, SUM(f_thirtieth_day) AS thirtiethday FROM table_stat WHERE f_date>='2024-08-17' AND f_date<='2024-08-23' AND f_country_id IN(0) AND f_os IN(0) AND f_language IN(0) AND f_bind_type IN(0) AND f_back_front =0 GROUP BY fdate) a ORDER BY fdate DESC LIMIT 0,50

[hcymysql]

已修复。

sqlai_helper工具版本号: 2.1.3，更新日期：2024-10-10 <-> 修复派生子查询derived2产生的临时表问题

下载地址：

链接：https://pan.baidu.com/s/1UJ14bjoNrlmAuwIdsSohiw

提取码：pipy

[wmbxx]

测试已修复，谢谢！