如何在表格中查询表格? [英] How can i query table within table?

问题描述

我该如何进行查询，从一个表中获取所有内容，然后联接另一个表，并将第二个表中的值放在结果的某个列中

How can i make a query that takes everything from one table then joins another table and put the values from the second table in a certain column in the result

我要问的问题可以得到更好的解释:

What i am asking can be better explained:

clients:
id | name      | age | ...
---------------------
15 | something | 30  |
17 | somethiaa | 30  |
13 | ggggthing | 30  |

clients_meta:
id | client_id | meta_key  | meta_value |
-----------------------------------------
1  | 15        | location  | NY         |
2  | 15        | height    | 195        |
3  | 15        | job       | student    |
4  | 13        | location  | TN         |

这是我当前的查询:

SELECT
`clients`.*,
`clients_meta`.*

FROM `clients`

JOIN clients_meta ON ( clients_meta.client_id = clients.id )

WHERE
`clients_age` = '30'

如何代替像这样的丑陋表?

how can instead of a ugly table like that:

15 | something | 30  | 1  | 15        | location  | NY         |
15 | something | 30  | 2  | 15        | height    | 195        |
15 | something | 30  | 3  | 15        | job       | student    |

将其更改为以下内容:

15 | something | 30  | 1  | 15        | location  | NY         |
                     | 2  | 15        | height    | 195        |
                     | 3  | 15        | job       | student    |

谢谢

推荐答案

您可以使用变量来检查最后一个ID是否等于当前ID，在这种情况下，输出null或''.

You can use a variable to check whether the last id is equal to the current id, and in that case output null or '' instead.

select
  case when c.ClientId <> @clientid then c.Name else '' end as ClientName,
  case when c.ClientId <> @clientid then @ClientId := c.ClientId else '' end as ClientId,
  p.ContactId,
  p.Name as ContactName
from
  Clients c
  inner join Contacts p on p.ClientId = c.Clientid
  , (select @clientid := -1) x
order by
  c.ClientId, p.ContactId

示例: http://sqlfiddle.com/#!2/658e4c/6

注意，这有点不客气.我故意将ClientId设置为第二个字段，因此可以在同一字段中更改并返回clientId变量.在其他更复杂的情况下，您可能必须在单独的字段中执行此操作.但是要从结果中删除该占位符字段，您必须将整个查询嵌入子选择中，并在顶层查询中以正确的顺序定义所需的字段.

Note, this is a bit hacky. I deliberately made ClientId the second field, so I could change and return the clientId variable in the same field. In other, more elaborate cases, you may have to do that in a separate field. But to eliminate that placeholder field from the result, you'll have to embed the whole query in a subselect, and define the wanted fields in the right order on the top level query.

这篇关于如何在表格中查询表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！