mysql - 没有多行的 SQL 连接

Question

我有三个表，例如 DATA、SATURATION、VALUE，其中:

CREATE TABLE DATA(
  data_id INT AUTO_INCREMENT,
  PRIMARY KEY(data_id)
);

CREATE TABLE VALUE(
  value_id INT AUTO_INCREMENT,
  data_id INT,
  sample INT,
  channel_1 DOUBLE,
  channel_2 DOUBLE,
  channel_3 DOUBLE,
  PRIMARY KEY (value_id),
  FOREIGN KEY(data_id) REFERENCES DATA(data_id)
);

CREATE TABLE SATURATION(
  saturation_id INT AUTO_INCREMENT,
  data_id INT,
  sample INT,
  channel_1 INT CHECK (channel_1 >= 0 AND channel_1 <= 1),
  channel_2 INT CHECK (channel_2 >= 0 AND channel_2 <= 1),
  channel_3 INT CHECK (channel_3 >= 0 AND channel_2 <= 3),
  PRIMARY KEY (saturation_id),
  FOREIGN KEY(data_id) REFERENCES DATA(data_id)
);

我的查询如下:

SELECT s.sample AS 'S Sample', s.channel_1 AS '#Ch_1 S', s.channel_2 AS '#Ch_2 ES', s.channel_3 AS '#Ch_3 ES',
  v.sample AS 'V Sample', v.channel_1 AS '#Ch_1 V', v.channel_2 AS '#Ch_2 V', v.channel_3 AS '#Ch_3 V'
FROM VALUE v
  LEFT OUTER JOIN SATURATION s
  ON v.data_id = s.data_id
  WHERE s.sample = v.sample AND s.data_id = 1;

如果一个系列中的样本数量相等，结果就可以。但我想要这样的东西:

-----------| ES Sample | #Ch_1 ES | #Ch_2 ES | #Ch_3 ES | EV Sample | #Ch_1 EV | #Ch_2 EV | #Ch_3 EV |-----------| 1 | 1 | 0 | 1 | 1 | 114.5 | 10.2 | 114.5 |-----------| 2 | 0 | 0 | 1 | 2 | 114.5 | 10.2 | 114.5 |-----------| 3 | 0 | 0 | 1 | 3 | 114.5 | 10.2 | 114.5 |-----------| 4 | 1 | 0 | 1 | 4 | 114.5 | 10.2 | 114.5 |-----------| null | null | null | null | 5 | 114.5 | 10.2 | 114.5 |-----------

How to do it? Normally without clause 's.sample = v.sample' i get this. I don't want store saturation and value in one table but this very simple and foolish solution I guess.

UPDATE:

Here's a query which produce result as I wanted (if I have more values in one serie than saturations):

SELECT v.data_id AS 'V ID', s.data_id AS 'S ID', s.sample AS 'S Sample', s.channel_1 AS '#Ch_1 S', s.channel_2 AS '#Ch_2 S', s.channel_3 AS '#Ch_3 S',
  v.sample AS 'V Sample', v.channel_1 AS '#Ch_1 V', v.channel_2 AS '#Ch_2 V', v.channel_3 AS '#Ch_3 V'
FROM SATURATION s RIGHT JOIN VALUE v ON v.sample = s.sample
WHERE (v.data_id = 1 OR s.data_id IS NULL) AND (s.data_id = 1 OR s.data_id IS NULL);

Answer 1

我将在数据、值(value)和饱和度之间创建一个示例表。因此，一个数据将有许多样本，每个样本将有零个或一个值以及零个或一个饱和度记录

然后从此示例表驱动查询，左连接到值和饱和度。

如果这没有意义，请告诉我，我会尽力澄清，但希望您明白我的意思。