mysql - 如何自加入以及何时加入

Question

我从一个正在进行逆向工程的 java 应用程序中得到了这个巨大的查询。

`

SELECT
    T.transferNumber,
    T.TransferNumber,
    T.BarrelsRequested - T.TotalBarrels br,
    TrTy.TransferTypeName,
    TrTa.SourceDest,
    Ta.TankName,
    SBP.SBPName,
    T.Notes
FROM
    RC.Transfers T,
    RC.TransferTypes TrTy,
    RC.TransferTank TrTa,
    RC.Tanks Ta,
    RC.ShipBargePipe SBP
WHERE
    T.TransferTypeNumber = TrTy.TransferTypeNumber
    AND T.TransferNumber = TrTa.TransferNumber
    AND TrTa.TankNumber = Ta.TankNumber
    AND T.SBPNumber = SBP.SBPNumber
    AND T.StartStamp IS NOT NULL
    AND T.EndStamp IS NULL
    AND T.Void IS NULL
    AND TrTy.TransferTypeName <> 'Truck'
UNION SELECT
    T.transferNumber,
    T.TransferNumber,
    T.BarrelsRequested - T.TotalBarrels,
    TrTy.TransferTypeName,
    TrTa.SourceDest,
    Ta.TankName,
    '',
    T.Notes
FROM
    RC.Transfers T,
    RC.TransferTypes TrTy,
    RC.TransferTank TrTa,
    RC.Tanks Ta
WHERE
    T.TransferTypeNumber = TrTy.TransferTypeNumber
    AND T.TransferNumber = TrTa.TransferNumber
    AND TrTa.TankNumber = Ta.TankNumber
    AND T.SBPNumber IS NULL
    AND T.StartStamp IS NOT NULL
    AND T.EndStamp IS NULL
    AND T.Void IS NULL
    AND TrTy.TransferTypeName <> 'Truck'

`

这将返回结果集:

但我真的想要:

任何帮助将不胜感激。如图所示，我希望具有相同传输编号的行显示在一行上。我想我可以使用自连接来实现这一点，并且当 SourceDest = 's' AS 'FROM' 时的情况当 SourceDest = 'd' AS 'TO' 时的情况

另请注意，当类型为 Ship 或 Pipe 时，from 或 To 列将成为 SBPName，具体取决于它是源还是目的地。

谢谢!您也可以根据需要随意修改我的问题。

编辑*

这里是 java 源代码，显示了它们如何将行分组在一起。`

void updateRunning(Connection conn)
    throws SQLException
  {
    Query q = new Query(conn);
    this.jLabelRunningUpdate.setText("Reading");
    this.jLabelRunningUpdate.setVisible(true);

    q
      .setQuery("SELECT T.transferNumber, T.TransferNumber, T.BarrelsRequested - T.TotalBarrels br,       TrTy.TransferTypeName, TrTa.SourceDest, Ta.TankName, SBP.SBPName, T.Notes  FROM RC.Transfers T, RC.TransferTypes TrTy, RC.TransferTank TrTa, RC.Tanks Ta,       RC.ShipBargePipe SBP WHERE T.TransferTypeNumber = TrTy.TransferTypeNumber   AND T.TransferNumber = TrTa.TransferNumber   AND TrTa.TankNumber = Ta.TankNumber   AND T.SBPNumber = SBP.SBPNumber   AND T.StartStamp is not NULL   AND T.EndStamp is NULL   AND T.Void is NULL   AND TrTy.TransferTypeName <> 'Truck' UNION SELECT T.transferNumber, T.TransferNumber, T.BarrelsRequested - T.TotalBarrels,       TrTy.TransferTypeName, TrTa.SourceDest, Ta.TankName, '', T.Notes  FROM RC.Transfers T, RC.TransferTypes TrTy, RC.TransferTank TrTa, RC.Tanks Ta WHERE T.TransferTypeNumber = TrTy.TransferTypeNumber   AND T.TransferNumber = TrTa.TransferNumber   AND TrTa.TankNumber = Ta.TankNumber   AND T.SBPNumber is NULL   AND T.StartStamp is not NULL   AND T.EndStamp is NULL   AND T.Void is NULL   AND TrTy.TransferTypeName <> 'Truck'");

    ResultSetCA transfers = q.execute(false);

    this.jLabelRunningUpdate.setText("Updating");

    String sourceTankList = "";
    String destinationTankList = "";
    ResultSetCA runningList = new ResultSetCA();
    for (int count = 0; count < transfers.size(); count++)
    {
      sourceTankList = "";
      destinationTankList = "";

      String currentTNumber = transfers.getString(count, "transferNumber");
      while ((count < transfers.size()) && (
        currentTNumber.equals(transfers.getString(count, "transferNumber"))))
      {
        String sourceDest = transfers.getString(count, "sourceDest");
        String tankName = transfers.getString(count, "tankName");
        if (sourceDest.equals("s")) {
          sourceTankList = sourceTankList + tankName + ", ";
        } else {
          destinationTankList = destinationTankList + tankName + ", ";
        }
        count++;
      }
      if (!sourceTankList.equals("")) {
        sourceTankList = sourceTankList.substring(0, sourceTankList.length() - 2);
      }
      if (!destinationTankList.equals("")) {
        destinationTankList = 
          destinationTankList.substring(0, destinationTankList.length() - 2);
      }
      count--;int lastCount = count;

      String transferTypeName = transfers.getString(lastCount, "transferTypeName");
      if (!transferTypeName.equals("Tank"))
      {
        String sbpName = transfers.getString(lastCount, "sbpName");
        if (sourceTankList.equals("")) {
          sourceTankList = sbpName;
        } else {
          destinationTankList = sbpName;
        }
      }
      Number barrelsRemaining = transfers.getNumber(lastCount, "br");
      Object BBLS;
      Object BBLS;
      if (barrelsRemaining == null) {
        BBLS = "????";
      } else {
        BBLS = new Integer(barrelsRemaining.intValue());
      }
      Vector register = new Vector();

      Integer transferNumber = transfers.getInteger(lastCount, "transferNumber");
      String transferTName = transfers.getString(lastCount, "transferTypeName");
      String notes = transfers.getString(lastCount, "notes");

      register.add(transferNumber);
      register.add(transferNumber);
      register.add(BBLS);
      register.add(transferTName);
      register.add(sourceTankList);
      register.add(destinationTankList);
      register.add(notes);

      runningList.add(register);
    }
    Object key = null;
    if (this.jTableTransfersRunning.getSelectedRow() != -1) {
      key = this.jTableTransfersRunning.getSelectedKey();
    }
    this.jTableTransfersRunning.fill(runningList);
    if (runningList.size() > 0) {
      if (key != null) {
        this.jTableTransfersRunning.setSelectedKey(key);
      }
    }
    this.jLabelRunningUpdate.setVisible(false);
  }

`

Answer 1

已编辑

尝试一下:

SELECT 
    T.transferNumber,
    T.BarrelsRequested - T.TotalBarrels AS `BBLs left`,
    TrTy.TransferTypeName AS `Type`,
    IF(TrTy.TransferTypeName = 'Tank',Ta.TankName,IFNULL(Ta.TankName,SBP.SBPName)) AS `From`,
    IF(TrTy.TransferTypeName = 'Tank',Ta1.TankName,IFNULL(Ta1.TankName,SBP.SBPName)) AS `To`,
    T.notes
FROM transfers T
JOIN transfertypes TrTy
    ON T.TransferTypeNumber = TrTy.TransferTypeNumber
LEFT JOIN transfertank TrTa
    ON T.TransferNumber = TrTa.TransferNumber
    AND TrTa.SourceDest = 's'
LEFT JOIN tanks Ta
    ON TrTa.TankNumber = Ta.TankNumber
LEFT JOIN transfertank TrTa1
    ON T.TransferNumber = TrTa1.TransferNumber
    AND TrTa1.SourceDest = 'd'
LEFT JOIN tanks Ta1
    ON TrTa1.TankNumber = Ta1.TankNumber
LEFT JOIN shipbargepipe SBP
    ON T.SBPNumber = SBP.SBPNumber
WHERE T.StartStamp IS NOT NULL
    AND T.EndStamp IS NULL
    AND T.Void IS NULL
    AND TrTy.TransferTypeName <> 'Truck'
ORDER BY TRANSFERNUMBER