将 mySQL 表合并为一行? [英] Combining mySQL tables into one row?

问题描述

我有四个链接的表.

图像 表格

cid     link
=======================
1       something.jpg
2       else.jpg

terms 表格

cid     term         is_attr
================================
1      Location     0
2      Caption      1
3      Camera Lens  0

tags 表格

cid      Name          term_id
==============================
1       somewhere      1
2       BFE            1
3       A word         2

linked_tags 表格

cid    photo_id     tag_id
==========================
1      1            1
2      1            2
3      1            3

如果词条 is_attr == 1 图像在 linked_tags 表中应该只有该词条的一个条目.

if a Term is_attr == 1 the image should only have ONE entry in the linked_tags table for that term.

如果我要同时查询图像表和获取标签，我该怎么做?

If I were to query the image table and get the tags at the same time, how would I do that?

我想要返回(某些东西):

I'd like to have (something) this returned:

_____________________________________________________________________________
cid      |link              |attributes        |tags                |
=========|==================|==================|====================|
1        |something.jpg     |__________________|____________________| 
         |                  ||term    |value  |||term    |value    ||
         |                  ||========|=======|||========|=========||
         |                  ||caption |A word |||Location|somewhere||
         |                  ||        |       |||Location|BFE      ||  

这就是我要找的东西(PHP 方面):

This is what I am looking for (PHP side):

//Row 1
array(
  'link' => "something.jpg",
  'attributes' => array('caption'=>"A word"),
  'tags' => array('Location'=>array('somewhere','BFE'))
);
//Notice 'caption' points to a string and 'location' points to an array
//Row 2
array(
  'link' => "else.jpg",
  'attributes' => array(),
  'tags' => array()
);
// OR
array(
  'link' => "else.jpg"
);
// OR
array(
  'link' => "else.jpg",
  'attributes' => array('caption'=>""),
  'tags' => array();
);

推荐答案

SELECT  i.*, GROUP_CONCAT(tag.name SEPARATOR ', ')
FROM    image i
JOIN    tag
ON      tag.image_id = i.id
GROUP BY
        i.id

更新:

表格格式假设每条记录中的列数相同，所有记录的类型都相同.

Tabular format assumes same number of columns in each record, all having the same type across the records.

您的任务最好通过三个查询来完成:

Your task is best done in three queries:

SELECT  cid, link
FROM    image
WHERE   cid = 1

(cid 和链接)

SELECT  t.name, tag.name
FROM    linked_tags lt
JOIN    tags
ON      tags.cid = lt.tag_id
JOIN    terms t
ON      t.cid = tags.term_id
WHERE   lt.photo_id = 1
        AND  t.is_attr = 1

(属性)

SELECT  t.name, tag.name
FROM    linked_tags lt
JOIN    tags
ON      tags.cid = lt.tag_id
JOIN    terms t
ON      t.cid = tags.term_id
WHERE   lt.photo_id = 1
        AND  t.is_attr = 0

(标签)

这篇关于将 mySQL 表合并为一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！