我每天将大量import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.util.Random;
import javax.swing.BorderFactory;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.SwingConstants;
public class Soduko {
private Cell[][] cells;
private static final int SIZE = 9,GAP = 2;
private final JFrame jFrame;
public Soduko() {
jFrame = new JFrame("Soduko");
jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
jFrame.setLocationRelativeTo(null);
buildUi();
jFrame.pack();
jFrame.setVisible(true);
}
void buildUi() {
JPanel gridPanel = new JPanel();
gridPanel.setLayout(new GridLayout(SIZE,SIZE,GAP,GAP));
gridPanel.setBorder(BorderFactory.createEmptyBorder(GAP,GAP));
jFrame.add(gridPanel,BorderLayout.CENTER);
cells = new Cell[SIZE][SIZE];
Random rand = new Random();
for(int row=0; row <cells.length; row++) {
for(int col=0; col<cells[row].length; col++) {
Cell cell = new Cell(rand.nextInt(SIZE+1)); //initialize with random number for demo
cells[row][col] = cell;
gridPanel.add(cell);
}
}
jFrame.add(new JLabel("Help: "),BorderLayout.SOUTH);
}
public static void main(String[] args) {
SwingUtilities.invokeLater(()-> new Soduko());
}
class Cell extends JLabel {
private static int CELL_H =35,CELL_W = 35;
Cell(int value) {
super(String.valueOf(value));
setHorizontalAlignment(SwingConstants.CENTER);
setBorder(BorderFactory.createLineBorder(Color.BLUE));
setPreferredSize(new Dimension(CELL_H,CELL_W));
setOpaque(true);
}
}
提取到mainframe中。每个摘录是所有可用数据的完整导出。我一直在将数据加载到BigQuery中，然后使用BigQuery语句生成2类历史记录，在该语句中，我加入每个表的主键并比较所有列以查找差异，并关闭过时的行并将新/更新的行插入到历史表中。效果很好。
一位同事提出了这样的情况：我们不需要知道主键即可执行此操作，我们可以将所有数据列都视为唯一约束。考虑到这一点，我创建了一个新的SQL MERGE语句，该语句似乎与旧语句一样工作，但是不必知道主键，这使我们更轻松了很多。这是新查询的示例：
MERGE有人可以告诉我这种方法是否有缺点吗？考虑到MERGE ods.kfir_history AS main USING (
SELECT FF_NR,FIRMA_PRODENH_TYPE,FIRMA_NAVN_1,FIRMA_NAVN_2,SE_NR,KONCERN_NR,FIRMA_STATUS_DATO,FIRMA_STATUS_DATO_NUL,FIRMA_STATUS,FIRMA_TYPE,ANTAL_ANSAT_DATO,ANTAL_ANSAT_DATO_NUL,ANTAL_ANSAT,ANTAL_ANSAT_NUL,ANTAL_ANSAT_KILDE,FIRMA_STIFTET_DATO,FIRMA_STIFTET_DATO_NUL,AS_REGISTRERET,MOMS_REGISTRERET,FAGLIG_FORENING,HEKTAR,RET_SBH,RET_TIMESTAMP,SUPL_FIRMA_NAVN,SUPL_FIRMA_NAVN_NUL,CVR_NR,P_NR from staging.kfir_new
) AS delta ON
IFNULL(main.FF_NR,'null') = IFNULL(delta.FF_NR,'null') AND
IFNULL(main.FIRMA_PRODENH_TYPE,'null') = FNULL(delta.FIRMA_PRODENH_TYPE,'null') AND
IFNULL(main.FIRMA_NAVN_1,'null') = IFNULL(delta.FIRMA_NAVN_1,'null') AND
IFNULL(main.FIRMA_NAVN_2,'null') = IFNULL(delta.FIRMA_NAVN_2,'null') AND
IFNULL(main.SE_NR,0) = IFNULL(delta.SE_NR,0) AND IFNULL(main.KONCERN_NR,'null') = IFNULL(delta.KONCERN_NR,'null') AND
IFNULL(main.FIRMA_STATUS_DATO,'null') = IFNULL(delta.FIRMA_STATUS_DATO,'null') AND
IFNULL(main.FIRMA_STATUS_DATO_NUL,'null')= IFNULL(delta.FIRMA_STATUS_DATO_NUL,'null') AND
IFNULL(main.FIRMA_STATUS,'null') = IFNULL(delta.FIRMA_STATUS,'null') AND IFNULL(main.FIRMA_TYPE,'null') = IFNULL(delta.FIRMA_TYPE,'null') AND IFNULL(main.ANTAL_ANSAT_DATO,'null') = IFNULL(delta.ANTAL_ANSAT_DATO,'null') AND IFNULL(main.ANTAL_ANSAT_DATO_NUL,'null') = IFNULL(delta.ANTAL_ANSAT_DATO_NUL,'null') AND IFNULL(main.ANTAL_ANSAT,0) = IFNULL(delta.ANTAL_ANSAT,0) AND IFNULL(main.ANTAL_ANSAT_NUL,'null') = IFNULL(delta.ANTAL_ANSAT_NUL,'null') AND IFNULL(main.ANTAL_ANSAT_KILDE,'null') = IFNULL(delta.ANTAL_ANSAT_KILDE,'null') AND IFNULL(main.FIRMA_STIFTET_DATO,'null') = IFNULL(delta.FIRMA_STIFTET_DATO,'null') AND IFNULL(main.FIRMA_STIFTET_DATO_NUL,'null') = IFNULL(delta.FIRMA_STIFTET_DATO_NUL,'null') AND IFNULL(main.AS_REGISTRERET,0) = IFNULL(delta.AS_REGISTRERET,0) AND IFNULL(main.MOMS_REGISTRERET,'null') = IFNULL(delta.MOMS_REGISTRERET,'null') AND IFNULL(main.FAGLIG_FORENING,'null') = IFNULL(delta.FAGLIG_FORENING,'null') AND IFNULL(main.HEKTAR,0) = IFNULL(delta.HEKTAR,0) AND IFNULL(main.RET_SBH,'null') = IFNULL(delta.RET_SBH,'null') AND IFNULL(main.RET_TIMESTAMP,'null') = IFNULL(delta.RET_TIMESTAMP,'null') AND IFNULL(main.SUPL_FIRMA_NAVN,'null') = IFNULL(delta.SUPL_FIRMA_NAVN,'null') AND IFNULL(main.SUPL_FIRMA_NAVN_NUL,'null') = IFNULL(delta.SUPL_FIRMA_NAVN_NUL,'null') AND IFNULL(main.CVR_NR,0) = IFNULL(delta.CVR_NR,0) AND IFNULL(main.P_NR,0) = IFNULL(delta.P_NR,0)
WHEN NOT MATCHED BY SOURCE AND main.SystemTimeEnd = "5999-12-31 23:59:59" --Close all updated records
THEN UPDATE SET SystemTimeEnd=delta.SystemTime,current_timestamp,Lastaction = 'U'
WHEN NOT MATCHED BY TARGET --insert all new and updated records
THEN INSERT (FF_NR,P_NR,SystemTime,SystemTimeEnd,Lastupdated,Lastaction)
VALUES (delta.FF_NR,delta.FIRMA_PRODENH_TYPE,delta.FIRMA_NAVN_1,delta.FIRMA_NAVN_2,delta.SE_NR,delta.KONCERN_NR,delta.FIRMA_STATUS_DATO,delta.FIRMA_STATUS_DATO_NUL,delta.FIRMA_STATUS,delta.FIRMA_TYPE,delta.ANTAL_ANSAT_DATO,delta.ANTAL_ANSAT_DATO_NUL,delta.ANTAL_ANSAT,delta.ANTAL_ANSAT_NUL,delta.ANTAL_ANSAT_KILDE,delta.FIRMA_STIFTET_DATO,delta.FIRMA_STIFTET_DATO_NUL,delta.AS_REGISTRERET,delta.MOMS_REGISTRERET,delta.FAGLIG_FORENING,delta.HEKTAR,delta.RET_SBH,delta.RET_TIMESTAMP,delta.SUPL_FIRMA_NAVN,delta.SUPL_FIRMA_NAVN_NUL,delta.CVR_NR,delta.P_NR,delta.SystemTime,'5999-12-31 23:59:59','I')
根本不处理主键，最好在解决方案中完全不使用这个概念。