java - 嵌套where子句我给mysql sql语法异常(exception)

我是MySQL的新手，需要帮助。

基本上，对于选定的日期范围，我需要记录计数，在这些记录中，称为UniwareReport的表格中存在跟踪号，但有空运单号。 MisReport中不存在

以下是我尝试过的内容，但我知道这是不正确的。

select count (*) from UniwareReport
  where invoiceCreated >=:sDate
    and invoiceCreated <=:eDate
    and TrackingNumber NOT IN(select airwayBill from MisReport)


@Override
public Long getUniwareReportsCountInDateRange(String param) throws ParseException {
    String sDate = param;
    String eDate = param;
    //String oId = saleOrderNumber.trim();
    Query query;
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    if (param.contains("-")) {
        String date[] = param.split("-");
        sDate = date[0];
        eDate = date[1];
    }
        query = sessionFactory.getCurrentSession().createQuery(
                "select count (*) from UniwareReport where invoiceCreated>=:sDate and invoiceCreated <=:eDate and (SELECT trackingNumber from UniwareReport where trackingNumber NOT IN(select airwayBill from MisReport))");
        Date startDate = DateUtils.addToDate(dateFormat.parse(sDate), Calendar.DATE, 0);
        Date endDate = DateUtils.addToDate(dateFormat.parse(eDate), Calendar.DATE, 1);
        query.setParameter("sDate", startDate);
        query.setParameter("eDate", endDate);
        //query.setParameter("oId", oId);
        return (Long) query.uniqueResult();

}

最佳答案

您不需要在子查询中的UniwareReport中进行另一个SELECT。这应该工作：

select count (*) from UniwareReport
  where invoiceCreated >= :sDate
    and invoiceCreated <= :eDate
    and TrackingNumber NOT IN (select airwayBill from MisReport)