如何用WHERE语句获取周、月、年销售额？周查询返回月数据求助

问题根源 #

你的SQL查询存在核心逻辑错误：

• WHERE Date = WEEK(CURDATE()) 是将Date字段（日期类型）直接与WEEK(CURDATE())返回的**周数字（如当前是第30周则返回30）**做相等判断，类型与逻辑完全不匹配，导致查询结果异常。
• 代码中格式化当前日期的DateTimeFormatter dtf...String date = dtf.format(now);部分未实际使用，属于冗余代码。
• 异常捕获块为空，无法定位具体错误原因。

修正后的周销售额查询代码 #

修正SQL逻辑 #

要筛选当前周的销售数据，需判断Date字段是否落在当前周的日期范围内，以下两种方法可选：

方法1：使用YEARWEEK（推荐，避免跨年周问题）

public void weekly(){
    ResultSet rs = null;
    java.sql.Statement st = null;
    try{
        con = Connector.getConnection();
        st = con.createStatement();
        // YEARWEEK(CURDATE()) 返回YYYYWW格式的数字，比如202430，确保年份+周数完全匹配
        String query = "SELECT sum(Quantity) FROM `salesproduct_table` WHERE YEARWEEK(Date) = YEARWEEK(CURDATE())";
        rs = st.executeQuery(query);
        
        if(rs.next()){
            productsales4.setText(rs.getString(1) != null ? rs.getString(1) : "0");
        } else {
            productsales4.setText("0");
        }
        
    }catch(Exception ex){
        // 打印异常便于排查问题
        ex.printStackTrace();
    } finally {
        // 关闭资源，避免内存泄漏
        try {
            if(rs != null) rs.close();
            if(st != null) st.close();
            if(con != null) con.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
}

方法2：使用日期范围（适用于DATE类型字段）

public void weekly(){
    ResultSet rs = null;
    java.sql.Statement st = null;
    try{
        con = Connector.getConnection();
        st = con.createStatement();
        // 筛选本周一到周日的日期范围
        String query = "SELECT sum(Quantity) FROM `salesproduct_table` WHERE Date >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND Date <= DATE_ADD(CURDATE(), INTERVAL (6 - WEEKDAY(CURDATE())) DAY)";
        rs = st.executeQuery(query);
        
        if(rs.next()){
            productsales4.setText(rs.getString(1) != null ? rs.getString(1) : "0");
        } else {
            productsales4.setText("0");
        }
        
    }catch(Exception ex){
        ex.printStackTrace();
    } finally {
        // 资源关闭逻辑同上
    }
}

附赠：月、年度销售额查询示例 #

月销售额 #

public void monthly(){
    ResultSet rs = null;
    java.sql.Statement st = null;
    try{
        con = Connector.getConnection();
        st = con.createStatement();
        String query = "SELECT sum(Quantity) FROM `salesproduct_table` WHERE YEAR(Date) = YEAR(CURDATE()) AND MONTH(Date) = MONTH(CURDATE())";
        rs = st.executeQuery(query);
        
        if(rs.next()){
            // 替换为你的月销售额显示控件
            productsalesMonth.setText(rs.getString(1) != null ? rs.getString(1) : "0");
        } else {
            productsalesMonth.setText("0");
        }
        
    }catch(Exception ex){
        ex.printStackTrace();
    } finally {
        // 资源关闭逻辑同上
    }
}

年度销售额 #

public void yearly(){
    ResultSet rs = null;
    java.sql.Statement st = null;
    try{
        con = Connector.getConnection();
        st = con.createStatement();
        String query = "SELECT sum(Quantity) FROM `salesproduct_table` WHERE YEAR(Date) = YEAR(CURDATE())";
        rs = st.executeQuery(query);
        
        if(rs.next()){
            // 替换为你的年度销售额显示控件
            productsalesYear.setText(rs.getString(1) != null ? rs.getString(1) : "0");
        } else {
            productsalesYear.setText("0");
        }
        
    }catch(Exception ex){
        ex.printStackTrace();
    } finally {
        // 资源关闭逻辑同上
    }
}

内容的提问来源于stack exchange，提问作者Dev W