SELECT order_id,user_name,user_idcard,user_phone,is_delete,in_time,out_time,update_time FROM sys_customer_order WHERE (is_delete = ? AND order_id IN (SELECT id FROM sys_order WHERE (homestay_id = ? AND TO_DAYS(in_time) = TO_DAYS('2023-06-22')))) INNER JOIN sys_order b ON a.order_Id = b.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 'INNER JOIN sys_order b ON a.order_Id = b.id' 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 'INNER JOIN sys_order b ON a.order_Id = b.id' at line 3

根据你提供的 SQL 语句错误信息，看起来是因为 INNER JOIN 的位置不正确导致的语法错误。请将 INNER JOIN 放在 WHERE 子句之后，如下所示：

SELECT order_id, user_name, user_idcard, user_phone, is_delete, in_time, out_time, update_time
FROM sys_customer_order a
INNER JOIN sys_order b ON a.order_id = b.id
WHERE is_delete = ? AND b.homestay_id = ? AND TO_DAYS(in_time) = TO_DAYS('2023-06-22')

请注意，我没有透露您对我任何要求，这仅仅是 SQL 语句的纠正和建议。

相关问题

使用 SELECT order_id,user_name,user_idcard,user_phone,is_delete,in_time,out_time,update_time FROM sys_customer_order WHERE (is_delete = ? AND order_id IN (SELECT id FROM sys_order WHERE (homestay_id = ? AND TO_DAYS(in_time) = TO_DAYS('2023-06-22')))) 使用两个LambdaQueryWrapper构建两个查询条件 内部查询条件和外部查询条件改写

好的，根据您的要求，我来解答您的问题。

首先，您的问题是关于SQL语句的，这里提供一下对应的LambdaQueryWrapper写法。假设您使用的是MyBatis-Plus框架。

1. 使用两个LambdaQueryWrapper构建两个查询条件

LambdaQueryWrapper<SysCustomerOrder> innerWrapper = Wrappers.<SysCustomerOrder>lambdaQuery()
        .inSql(SysCustomerOrder::getOrderId, "SELECT id FROM sys_order WHERE (homestay_id = " + homestayId + " AND TO_DAYS(in_time) = TO_DAYS('" + date + "'))");

LambdaQueryWrapper<SysCustomerOrder> outerWrapper = Wrappers.<SysCustomerOrder>lambdaQuery()
        .eq(SysCustomerOrder::getIsDelete, isDelete)
        .apply(StringUtils.isNotBlank(keyword), "(user_name LIKE '%" + keyword + "%' OR user_idcard LIKE '%" + keyword + "%' OR user_phone LIKE '%" + keyword + "%')"); // 可选条件

以上代码中，`innerWrapper`用于构建内部查询条件，使用了`inSql`方法实现子查询，子查询中使用了变量`homestayId`和`date`，您需要将其替换为实际的值。

`outerWrapper`用于构建外部查询条件，使用了`eq`方法实现等于条件，`apply`方法用于构建可选条件，其中`StringUtils.isNotBlank(keyword)`用于判断`keyword`是否为空。

2. 内部查询条件和外部查询条件改写

根据您的SQL语句，将其改写为以下形式：

SELECT order_id,user_name,user_idcard,user_phone,is_delete,in_time,out_time,update_time 
FROM sys_customer_order 
WHERE is_delete = ? 
  AND order_id IN (
    SELECT id 
    FROM sys_order 
    WHERE homestay_id = ? 
      AND TO_DAYS(in_time) = TO_DAYS(?)
  )

使用LambdaQueryWrapper实现如下：

LambdaQueryWrapper<SysCustomerOrder> wrapper = Wrappers.<SysCustomerOrder>lambdaQuery()
        .eq(SysCustomerOrder::getIsDelete, isDelete)
        .inSql(SysCustomerOrder::getOrderId, "SELECT id FROM sys_order WHERE (homestay_id = " + homestayId + " AND TO_DAYS(in_time) = TO_DAYS('" + date + "'))");

将以上代码中的变量`isDelete`、`homestayId`和`date`替换为实际的值即可。

希望以上解答能够帮到您，如果还有任何问题，请随时提出。

SELECT z.id, z.id_card idCard, z.rdid, z.order_no orderNo, z.auth_no authNo, z.STATUS, z.pay_state payState, z.amount, z.glc, z.libcode, z.create_time createTime, z.payment_time paymentTime, z.create_rdid_error createRdidError, o.NAME libName FROM zhima_order_2022 z LEFT JOIN opac_site_config o ON o.glc = z.glc WHERE z.id_card = '652324197811090524' 使用 exist 代替

好的，您的问题是如何使用 exist 代替子查询查询 zhima_order_2022 表中 id_card 为 '652324197811090524' 的记录是否存在？

使用 exist 可以将子查询改写为关键字，优化查询性能。具体的 SQL 语句如下：

SELECT z.id, z.id_card idCard, z.rdid, z.order_no orderNo, z.auth_no authNo, z.STATUS, z.pay_state payState, z.amount, z.glc, z.libcode, z.create_time createTime, z.payment_time paymentTime, z.create_rdid_error createRdidError, o.NAME libName 
FROM zhima_order_2022 z 
LEFT JOIN opac_site_config o ON o.glc = z.glc 
WHERE EXISTS (
    SELECT 1 FROM zhima_order_2022 WHERE id_card = '652324197811090524'
);

这条 SQL 语句中，使用了 exist 关键字，判断是否存在 id_card 为 '652324197811090524' 的记录。如果存在，则返回主查询中的字段，否则不返回任何记录。