mysql - 使用 UNION 从结果集中的各个表中获取列值

Question

我有一个问题，我试图获取每个折扣的百分比值，但复杂的部分是有两种类型的折扣。灵活折扣和固定折扣。我正在尝试获取所有折扣的百分比，并取决于折扣类型，例如已修复 我进入固定折扣表并检索百分比，否则从 FlexiBand 折扣表中检索百分比。

我的表及其属性:

Discount:
DiscountID,
Name,
Type

FixedDiscount:
DiscountID,
Percentage

FlexibleDiscount:
DiscountID

FlexiBand:
UpperBound,
PercentageRate,
DiscountID

我收到错误java.sql.SQLException:未找到列“PercentageRate”。

当我这样做时:

 public ArrayList<Discount> getDiscounts() throws SQLException {

    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    ArrayList<Discount> discount = new ArrayList<Discount>();

    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/abpp034?user=abpp034&password=120001772");

    stmt = con.prepareStatement("SELECT Discount.DiscountID, Discount.Type, FixedDiscount.Percentage\n" +
"FROM Discount\n" +
"INNER JOIN FixedDiscount\n" +
"ON Discount.DiscountID = FixedDiscount.DiscountID\n" +
"\n" +
"UNION\n" +
"\n" +
"SELECT Discount.DiscountID, Discount.Type, FlexiBand.PercentageRate\n" +
"FROM Discount\n" +
"INNER JOIN FlexiBand\n" +
"ON Discount.DiscountID = FlexiBand.DiscountID");


    try {

        while (rs.next()) {

            Discount d = new Discount();
            Fixed_Discount fd = new Fixed_Discount();
            FlexiBand fb = new FlexiBand();                
            d.setDiscountId(rs.getInt("DiscountID"));
            d.setType(rs.getString("Type"));      
            fd.setPercentage(rs.getInt("Percentage"));
            fb.setPercentageRate(rs.getInt("PercentageRate"));



            discount.add(d);
            discount.add(fd);
            discount.add(fb);

        }

        } finally {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException se) {
                System.out.println(se.getErrorCode());
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException se) {
                System.out.println(se.getErrorCode());
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException se) {
                System.out.println(se.getErrorCode());
            }
        }
    }

    return discount;

    }

奇怪的是 PercentageRate 确实存在于表 FlexiBand 中，但我认为问题出在 UNION 之后，下一个语句没有被正确识别。

Answer 1

您可能想要从连接字符串中删除用户名和密码信息。独立运行时，两个查询是否都能干净地触发？你的 RDBMS 是 mysql 还是 sql-server？