从mysql中选择具有引用id的行到同一个表

I have a table such as:

我有一张桌子如:

id  name  ref_id  order  data_obj
--  ----  ------  -----  --------
1   Sam   0       15     [binary data]
2   Jack  0       20     [binary data]
3   Sue   0       25     [binary data]
4   Sam2  1       -      [no data]
5   Sue2  3       -      [no data]
6   Sam3  1       -      [no data]

The idea is that I have more columns other than data_obj which can be common, so I don't want to insert them again, just want to insert a reference id to the same data.

我的想法是,除了data_obj之外我有更多的列可以是常见的,所以我不想再次插入它们,只是想将引用ID插入到相同的数据中。

Is it possible to write a query and select this:

是否可以编写查询并选择此项:

1 - Sam - binary data from id 1
4 - Sam2 - binary data from id 1
6 - Sam3 - binary data from id 1
2 - Jack - binary data from id 2
3 - Sue - binary data from id 3
5 - Sue2 - binary data from id 3

Please note that I'm ordering according to column named order and there's no actual data for this column for referenced rows.

请注意,我按照命名顺序列进行排序,并且此列的引用行没有实际数据。

3 个解决方案

#1

SELECT t1.id, t1.name, t2.data_obj 
FROM your_table t1
LEFT JOIN your_table t2 ON t1.ref_id = t2.id
ORDER BY t1.order

Other version, which doesn't return rows without ref

其他版本,不返回没有ref的行

SELECT t1.id, t1.name, t2.data_obj 
FROM your_table t1, your_table t2
WHERE t1.ref_id = t2.id
ORDER BY t1.order

#2

Here's a modification of @vartec's answer. This modification uses COALESCE() to combine the data_obj from either the primary row or the referenced row.

这是@ vartec答案的修改。此修改使用COALESCE()来组合来自主行或引用行的data_obj。

SELECT t1.id, t1.name, COALESCE(t1.data_obj, t2.data_obj) 
FROM your_table t1
LEFT JOIN your_table t2 ON t1.ref_id = t2.id
ORDER BY COALESCE(t1.order, t2.order), ref_id;

COALESCE() is a standard SQL function that returns its first non-NULL argument.

COALESCE()是一个标准的SQL函数,它返回第一个非NULL参数。

#3

Why aren't you using more than one table?

你为什么不使用多张桌子?

CREATE TABLE user (
    user_id number not null (some form of auto increment or sequence),
    name varchar(50) not null,
    otherdata type,
    primary key (id));

CREATE TABLE common (
    common_id number not null (autoinc),
    user_id number not null,
    commondata type,
    primary key (common_id),
    unique index (user_id, common_id));

SELECT u.name, u.otherdata, c.commondata
FROM user u, common c
WHERE u.user_id = c.user_id

TABLE user
user_id  name   otherdata
1        Sam    abc
2        Jack   def
3        Sue    ghi

Table common
common_id user_id  commondata
1         1        AAA
2         1        BBB
3         1        CCC
4         2        DDD
5         3        EEE
6         3        FFF

Output
name   otherdata  commondata
Sam    abc        AAA
Sam    abc        BBB
Sam    abc        CCC
Jack   def        DDD
Sue    ghi        EEE
Sue    ghi        FFF

#1

SELECT t1.id, t1.name, t2.data_obj 
FROM your_table t1
LEFT JOIN your_table t2 ON t1.ref_id = t2.id
ORDER BY t1.order