mysql - 在SQL中的其他表中查找同一列

我有一个小问题，我有四个表，我正在使用mysql数据库

调查表将在此处创建所有调查。

╔══════════╤════════╗
║ SurveyId │ Name   ║
╠══════════╪════════╣
║ 1        │ First  ║
╟──────────┼────────╢
║ 2        │ Second ║
╚══════════╧════════╝

量规类别表

╔════════════╤═════════════╗
║ CategoryId │ Title       ║
╠════════════╪═════════════╣
║ 1          │ Stress      ║
╟────────────┼─────────────╢
║ 2          │ Environment ║
╟────────────┼─────────────╢
║ 3          │ Health      ║
╚════════════╧═════════════╝

要同时连接“量表类别”表和“测量表”，

我使用另一个称为标题表的表

标题表

这里title1，title2和title3是仪表类别表的外键，SurveyId是Survey表的外键

╔══════════════╤════════╤════════╤════════╤══════════╗
║ GaugeTitleId │ title1 │ title2 │ title3 │ surveyId ║
╠══════════════╪════════╪════════╪════════╪══════════╣
║ 1            │ 2      │ 3      │ 1      │ 1        ║
╟──────────────┼────────┼────────┼────────┼──────────╢
║ 2            │ 1      │ 3      │ 2      │ 1        ║
╟──────────────┼────────┼────────┼────────┼──────────╢
║ 3            │ 3      │ 1      │ 2      │ 2        ║
╚══════════════╧════════╧════════╧════════╧══════════╝

另一个表叫做Average_values，具有相应的Title表值

平均值表

╔═════════╤════════╤════════╤════════╤══════════╤══════════════╗
║ GaugeID │ Gauge1 │ Guage2 │ Gauge3 │ SurveyId │ GaugeTitleId ║
╠═════════╪════════╪════════╪════════╪══════════╪══════════════╣
║ 1       │ 34     │ 76     │ 23     │ 1        │ 1            ║
╟─────────┼────────┼────────┼────────┼──────────┼──────────────╢
║ 2       │ 56     │ 23     │ 67     │ 1        │ 1            ║
╟─────────┼────────┼────────┼────────┼──────────┼──────────────╢
║ 3       │ 14     │ 28     │ 56     │ 1        │ 2            ║
╚═════════╧════════╧════════╧════════╧══════════╧══════════════╝

我的问题是，如果我想从“平均值”表中获取“应力”值，该如何获取？因为外键顺序不对。输出应为23,56,28。有什么办法吗？我还有另一种想法，那就是将Average_values表修改为

╔═════════╤════════╤════════════╤══════════╗
║ GuageId │ values │ CategoryId │ SurveyId ║
╚═════════╧════════╧════════════╧══════════╝

并将这些值一个接一个地放置，此处CategoryId和SurveyID分别位于仪表类别表和Survey Table的外键关系中。但是我觉得这将不是一个有效的表。因为很多数据要动态处理。

最佳答案

我觉得您的表格设计很怪异，在我看来，其中的2个表格需要“无枢轴旋转”才能删除某些字段上的1,2,3后缀。关于您剩下的问题是什么，仍然是一个谜。没有明确的方法可以联接到名为Guage的表，但是下面有一个CategoryID的键是执行“取消枢轴”并尝试联接所有表的查询

SQL Fiddle

MySQL 5.6模式设置：

CREATE TABLE Survey
    (`SurveyId` int, `Name` varchar(6))
;

INSERT INTO Survey
    (`SurveyId`, `Name`)
VALUES
    (1, 'First'),
    (2, 'Second')
;


CREATE TABLE Gauge
    (`CategoryId` int, `Title` varchar(11))
;

INSERT INTO Gauge
    (`CategoryId`, `Title`)
VALUES
    (1, 'Stress'),
    (2, 'Environment'),
    (3, 'Health')
;


CREATE TABLE Title
    (`GaugeTitleId` int, `title1` int, `title2` int, `title3` int, `surveyId` int)
;

INSERT INTO Title
    (`GaugeTitleId`, `title1`, `title2`, `title3`, `surveyId`)
VALUES
    (1, 2, 3, 1, 1),
    (2, 1, 3, 2, 1),
    (3, 3, 1, 2, 2)
;


CREATE TABLE Average_values
    (`GaugeID` int, `Gauge1` int, `Guage2` int, `Gauge3` int, `SurveyId` int, `GaugeTitleId` int)
;

INSERT INTO Average_values
    (`GaugeID`, `Gauge1`, `Guage2`, `Gauge3`, `SurveyId`, `GaugeTitleId`)
VALUES
    (1, 34, 76, 23, 1, 1),
    (2, 56, 23, 67, 1, 1),
    (3, 14, 28, 56, 1, 2)
;

查询1：

select
        t.surveyId
      , t.GaugeTitleId
      , g.title Gauge_Title
      , case when cj.n = 1 then t.title1
             when cj.n = 2 then t.title2
             when cj.n = 3 then t.title3
        end Title
      , case when cj.n = 1 then av.Gauge1
             when cj.n = 2 then av.Guage2
             when cj.n = 3 then av.Gauge3
        end Gauge
from Title t
cross join (
  select 1 n union all
  select 2 n union all
  select 3 n) cj
inner join Average_values av on t.surveyId = av.surveyId
                            and t.GaugeTitleId = av.GaugeTitleId
inner join Gauge g on t.GaugeTitleId = g.CategoryId
where g.title = 'Stress'
order by Title, Gauge

Results：

| surveyId | GaugeTitleId | Gauge_Title | Title | Gauge |
|----------|--------------|-------------|-------|-------|
|        1 |            1 |      Stress |     1 |    23 |
|        1 |            1 |      Stress |     1 |    67 |
|        1 |            1 |      Stress |     2 |    34 |
|        1 |            1 |      Stress |     2 |    56 |
|        1 |            1 |      Stress |     3 |    23 |
|        1 |            1 |      Stress |     3 |    76 |