mysql - SQL连接没有多行

我有三个表，例如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);

最佳答案

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

然后从该“样本”表驱动查询，将“ Value”和“ Saturation”连接在一起。

让我知道这是否没有道理，我会尽力澄清，但希望您明白我的意思。

关于mysql - SQL连接没有多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40747288/