[GitHub] [shardingsphere] Fujinzhong commented on issue #6497: Can subqueries be supported in select？

[GitBox <gi...@apache.org>]

Fujinzhong commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694610519


   Hi,all, This sharding-jdbc-4.1.1`s log 
    ShardingSphere-SQL - Logic SQL: SELECT  `date`, customer_name `name`, customer_account `account`, customer_phone `phone`, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS `reject`  FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS `date`,max( CODE ) AS CODE  FROM mms_order WHERE customer_account IS NOT NULL  and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,`date` ) a  GROUP BY `date`,customer_name,customer_account,customer_phone
   ShardingSphere-SQL - Actual SQL: master ::: SELECT  `date`, customer_name `name`, customer_account `account`, customer_phone `phone`, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS `reject`  FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS `date`,max( CODE ) AS CODE  FROM mms_order WHERE customer_account IS NOT NULL  and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,`date` ) a  GROUP BY `date`,customer_name,customer_account,customer_phone ::: [2020-09-18 00:00:00, 2020-09-18 23:59:59].
    I use create_time as sharding column,mms_order is sharding table,the database is MySql. Can you explain why this query can not use sharding table?(BTW,this is my first time to ask question in github and use English to raise question) @lwtdev @jingshanglu @chana71 @tristaZero 
   
   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org