将多个结果合并为列

ogq8wdun 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(373)

我想使用sql查询来显示“佛罗里达”大学的“学费”和“学费”。
我使用下面的代码首先查询学费，然后结合第二个查询来查询费用。然而，我没有得到我期望的结果。
预期结果如下：

>    School      |     Tuition     |   Fees
> --------------------------------------------
>   School 1     |      2000       |    50
>   School 2     |      1000       |    80

但我得到了以下结果：

>    School      |     Tuition     |   Fees
> --------------------------------------------
>   School 1     |      2000       |    50
>   School 1     |      1000       |    50
>   School 2     |      2000       |    80
>   School 2     |      1000       |    80

我对wordpress数据库使用了以下查询。“学费”和“费用”的所有值都包含在名为“实体\字段\编号”的表中。这些值位于名为“value”的列中。学费和学费由一个名为“字段名称”的列来区分“实体id”是分配给学校的id号。
表“实体\字段\编号”

>    entity_id   |     field_name     |   value
> --------------------------------------------
>        1       |     tuition       |    2000
>        2       |     tuition       |    1000
>        1       |      fees         |    50
>        2       |      fees         |    80

下面是我使用的查询。

SELECT *
    FROM
       (SELECT field_number.`value` as tuition,
           posts.`post_title` as school
    FROM field_number
      INNER JOIN posts
         ON posts.`post_type` = 'school_dir'
         AND field_number.`entity_id` = posts.`ID`
      INNER JOIN field_location
         ON field_location.`province` LIKE '%florida%'
         AND posts.`ID` = field_location.`entity_id`
         AND field_number.`field_name` = 'tuition') tuition,

       (SELECT field_number.`value` as fees,
           posts.`post_title` as school
    FROM field_number
      INNER JOIN posts
         ON posts.`post_type` = 'school_dir'
         AND field_number.`entity_id` = posts.`ID`
      INNER JOIN field_location
         ON field_location.`province` LIKE '%florida%'
         AND posts.`ID` = field_location.`entity_id`
         AND field_number.`field_name` = 'fees') fees

sql mysql sqlite

来源：https://stackoverflow.com/questions/53431773/combine-multiple-results-as-columns

1条答案

按热度按时间

fcy6dtqo1#

你所做的可以解释为。。。

SELECT
   *
FROM
   (<sql>) tuition,
   (<sql>) fees

这个 , 然而，这是一个 CROSS JOIN . 所以你做了这个。。。

SELECT
   *
FROM
   (<sql>) tuition
CROSS JOIN
   (<sql>) fees

一 CROSS JOIN 获取两个表中的每一行组合（笛卡尔积）。
你真正想要的是 INNER JOIN ...

SELECT
   *
FROM
   (<sql>) tuition
INNER JOIN
   (<sql>) fees
        ON tuition.school = fees.school

现在，只有同一学校的学生才能加入。
不过，更好的方法是只修改查询以在 field_number 两张table。要做到这一点，每次都需要“别名”它（以避免歧义）。

SELECT
    posts.`post_title` as school,
    fees.`value`       as fees,
    tuition.`value`    as tuition
FROM
    field_location
INNER JOIN
    posts
        ON  posts.`ID` = field_location.`entity_id`
INNER JOIN
    field_number   AS fees
        ON  fees.`entity_id`  = posts.`ID`
        AND fees.`field_name` = 'fees'
INNER JOIN
    field_number   AS tuition
        ON  tuition.`entity_id`  = posts.`ID`
        AND tuition.`field_name` = 'tuition'
WHERE
        field_location.`province` LIKE '%florida%'
    AND posts.`post_type` = 'school_dir'

赞(0）回复(0）举报 2021-06-18

我来回答

将多个结果合并为列

1条答案

相关问题

热门标签

最新问答