mysql使用另一个表的关系从表订购数据 [英] mysql order data from table using relations from another table

问题描述

我有2张桌子
商品:有关商品的信息.项目具有常规"或数字"类型.

I have 2 tables
wares: Information about wares. Items have "usual" or "digital" type.

id | name | itemtype

CREATE TABLE IF NOT EXISTS `wares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `itemtype` enum('usual','digital') NOT NULL DEFAULT 'usual',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

   INSERT INTO `wares` (`id`, `name`, `itemtype`) VALUES
    (1, 'ware1', 'usual'),
    (2, 'ware2', 'usual'),
    (3, 'ware3', 'usual'),
    (4, 'ware4', 'usual'),
    (5, 'ware5', 'usual'),
    (6, 'ware6', 'digital'),
    (7, 'ware7', 'usual'),
    (8, 'ware8', 'digital'),
    (9, 'ware9', 'usual'),
    (10, 'ware10', 'digital');

关系:具有表商品中项目之间关系的表.类型为"usual"的某些项目与类型为"digital"的项目相关.并非所有项目都链接.

relations: table with relations between items from table wares. Some items with type "usual" related with item with type "digital". Not all items linked.

id_usualware | id_digitalware

CREATE TABLE IF NOT EXISTS `relations` (
  `id_usualware` int(11) NOT NULL,
  `id_digitalware` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `relations` (`id_usualware`, `id_digitalware`) VALUES
(1, 6),
(4, 8),
(7, 10);

http://sqlfiddle.com/#!2/2831a/13

我需要使用下面的表关系从表商品中选择数据.如果类型为"usual"的项目与项目"digital"有关联，则此数字项目的结果为跟随该"usual"的项目.我该如何使用MySQL?

I need to select data from table wares using table relations ordering below. If item with type "usual" has relation with item "digital", then this digital item follows this "usual" in result. How can i do it using MySQL?

id | name  | itemtype
 1 | ware1 | 'usual'  
 6 | ware6 | 'digital'
 2 | ware2 | 'usual'
 3 | ware3 | 'usual'
 4 | ware4 | 'usual'
 8 | ware8 | 'digital'
 5 | ware5 | 'usual'
 7 | ware7 | 'usual'
 10| ware10| 'digital'
 9 | ware9 | 'usual'

推荐答案

以下应按正确的顺序获取ids:

The following should get the ids in the right order:

select coalesce(r.id_digitalware, w.id)
from wares w left join
     relations r
     on r.id_digitalware = w.id
order by coalesce(r.id_usualware, w.id),
         (w.itemtype = 'usual') desc;

要获取完整的行，您需要另一个join:

To get the full rows, you need another join:

select w2.*
from wares w left join
     relations r
     on r.id_digitalware = w.id left join
     wares w2
     on w2.id = coalesce(r.id_digitalware, w.id)
order by coalesce(r.id_usualware, w.id),
         (w.itemtype = 'usual') desc;

此处是SQL提琴.

这篇关于mysql使用另一个表的关系从表订购数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！