2021年1+X大数据Java答案

public  Member() {	
	}
	public  Member(String name,String pwd,float score,int rank) {
		this.name = name;
		this.pwd = pwd;
		this.score = score;
		this.rank = rank;
	}

//请修改该方法，并且在赋值时，商品数量不能超过100
	public void setNum(int num) {
		if(num <= 100){
			this.num = num;
		}
	}

	public static Date convertFromStringToDate(String stringDate) {
		// 补全代码：
		Date date = null;
		try {
			date = sdf.parse(stringDate);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return date;
	}

	public static String convertFromDateToString(Date date) {
		// 补全或替换代码
		String str1 = sdf.format(date);
		return str1;
	}

	public String queryExpensiveGoods() {
                // 请补全sql语句
                String sql = "select `name` from `goods` where `price` = (select max(`price`) from `goods`)";
                Goods g = goodsUtil.getOne(sql, Goods.class);
                return g.getName();
        }

 
	public double querySum() {
                String sql = "select * from goods";
                double sum = 0;
                List list = goodsUtil.getList(sql, Goods.class);
                for (Goods goods : list) {
                        // 请补全代码
                       sum+=goods.getNum()*goods.getPrice();
                }
                return sum;
        }

	public void updateRank() {
                // 请补全sql语句
                String sql = "update `member` set `rank` = (case when score between 0 and 99 then 0 "+
                                                                "when score between 100 and 199 then 1 "+
                                                                "when score between 200 and 299 then 2 "+
                                                                "when score between 300 and 399 then 3 "+
                                                                "when score between 400 and 499 then 4 "+
                                                                "when score>=500 then 5 end"+
                ")";
                memberUtil.update(sql); 
        }

	public int queryNum() {
                String sql = "select * from `order`;";
                List o = orderUtil.getList(sql, Order.class); 
                Set s = new HashSet();
                for (Order order : o) {
                        // 请补全语句
                        s.add(order.getId());
                }
                return s.size();
        }

	public String queryMax() {
                // 请补全sql语句
                String sql = "SELECt `mname` from (SELECt `mname`,sum(`num`*`price`) as aa  from `order` GROUP BY `mname` ) tab1 where aa = (SELECt max(aa) from (SELECt sum(`num`*`price`) as aa  from `order` GROUP BY `mname`) tab1)";
                Order o = orderUtil.getOne(sql, Order.class); 
                return o.getMname();
        }