java - 同一SQL表中的项目比较

Question

我的数据库中有一个表，如下所示:

+-------------+---------+--------+
| ProductName | Status  | Branch |
+-------------+---------+--------+
| P1          | dead    |      1 |
| P1          | dead    |      2 |
| P2          | expired |      1 |
+-------------+---------+--------+

我想在我的 java 应用程序中将比较后的结果显示为:

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |        |
+-------------+---------+--------+

我一直在思考这个问题，但没有找到解决办法。关于如何做到这一点有什么建议吗？

Answer 1

在mysql中可以通过这种方式实现。 SQLFiddle Demo

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(case when branch = ''',
      branch,
      ''' then status ELSE NULL end) AS ',
      CONCAT('Branch',branch)
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT productName, ', @sql, ' 
                   FROM Table1 
                   GROUP BY productName');


PREPARE stmt FROM @sql;
EXECUTE stmt;

但我个人建议您通过执行简单的 select mysql 查询来使用 Java Collection 实现同样的效果。

更新:请尝试下面给定的代码快照。

package com.plugin.jira.api.util;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

public class Product {
  private String productName;
  private String status;
  private Integer branch;
  private Map<Integer, String> branchStatusMap = new HashMap<>();

  /**
   * @return the productName
   */
  public String getProductName() {
    return productName;
  }

  /**
   * @param productName the productName to set
   */
  public void setProductName(String productName) {
    this.productName = productName;
  }

  /**
   * @return the status
   */
  public String getStatus() {
    return status;
  }

  /**
   * @param status the status to set
   */
  public void setStatus(String status) {
    this.status = status;
    if (this.branch != null) {
      branchStatusMap.put(this.branch, this.status);
    }
  }

  /**
   * @return the branchStatusMap
   */
  public Map<Integer, String> getBranchStatusMap() {
    return branchStatusMap;
  }

  /**
   * @return the branch
   */
  public Integer getBranch() {
    return branch;
  }

  /**
   * @param branch the branch to set
   */
  public void setBranch(Integer branch) {
    this.branch = branch;
    if (this.status != null) {
      branchStatusMap.put(this.branch, this.status);
    }
  }

  @Override
  public int hashCode() {
    final int prime = 31;
    int result = 1;
    result = prime * result + ((productName == null) ? 0 : productName.hashCode());
    return result;
  }

  @Override
  public boolean equals(Object obj) {
    if (this == obj)
      return true;
    if (obj == null)
      return false;
    if (getClass() != obj.getClass())
      return false;
    Product other = (Product) obj;
    if (productName == null) {
      if (other.productName != null)
        return false;
    } else if (!productName.equals(other.productName))
      return false;

    /** make sure that the hasCode is also same for safer side. */
    if (this.hashCode() == other.hashCode()) {
      other.branchStatusMap.put(this.branch, this.status);
    }

    return true;
  }


  public static void main(String[] args) {

    Set<Product> products = new HashSet<>();
    Product p;

    p = new Product();
    p.setProductName("P1");
    p.setStatus("dead");
    p.setBranch(1);
    products.add(p);

    p = new Product();
    p.setProductName("P1");
    p.setStatus("dead");
    p.setBranch(2);
    products.add(p);

    p = new Product();
    p.setProductName("P2");
    p.setStatus("expired");
    p.setBranch(1);
    products.add(p);


    /** now you will get two results in set with map of branches */

    for (Product product : products) {
      System.out.println("Product : " + product.getProductName());
      for (Map.Entry<Integer, String> entry : product.getBranchStatusMap().entrySet()) {
        System.out.println("Branch " + entry.getKey() + " : " + entry.getValue());
      }
      System.out.println("----------------------------------");
    }

  }
}