mysql使用来自另一个表的关系按列从表中排序数据

Question

我的问题与 previous 非常相似.但是添加一个条件，我无法实现。
我有两张 table
商品:关于商品的信息。项目有“普通”或“数字”类型。 "sortnum" 列表示结果中的主要排序。

id | name | itemtype

CREATE TABLE IF NOT EXISTS `wares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sortnum` int(11) NOT NULL,
  `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`, `sortnum`, `name`, `itemtype`) VALUES
(1, 8, 'ware1', 'usual'),
(2, 7, 'ware2', 'usual'),
(3, 4, 'ware3', 'usual'),
(4, 11, 'ware4', 'usual'),
(5, 21, 'ware5', 'usual'),
(6, 22, 'ware6', 'digital'),
(7, 2, 'ware7', 'usual'),
(8, 33, 'ware8', 'digital'),
(9, 15, 'ware9', 'usual'),
(10, 19, 'ware10', 'digital');

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

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/e069f0/1

我需要使用表 关系 按 'wares'.'sortnum' 主要顺序从表 wares 中选择数据。如果类型为"usual" 的项与项"digital" 有关系，则此数字项在结果中遵循此"usual"。

id | sortnum | name  | itemtype
 7 |    2    | ware7 | 'usual'
 10|    19   | ware10| 'digital'  
 3 |    4    | ware3 | 'usual'
 2 |    7    | ware2 | 'usual'
 1 |    8    | ware1 | 'usual'
 6 |    22   | ware6 | 'digital'
 4 |    11   | ware4 | 'usual'
 8 |    33   | ware8 | 'digital'
 9 |    15   | ware9 | 'usual'
 5 |    21   | ware5 | 'usual'

我的查询版本效果不佳:

SELECT w2.*, r.* 
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 ) 
WHERE w2.sortnum >0
ORDER BY 
  CASE WHEN (r.id_usualware IS NULL) THEN w2.sortnum END ASC , 
  CASE WHEN (r.id_usualware IS NOT NULL) THEN r.id_usualware END DESC 

请帮我查询 MySQL。

Answer 1

我认为您可以通过添加另一个左连接并将排序更改为使用 sortnum 来解决此问题，如下所示:

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)
left join wares w3 on r.id_usualware = w3.id
order by coalesce(w3.sortnum, w.sortnum), (w.itemtype = 'usual') desc;

Sample SQL Fiddle

使用您在 fiddle 中提供的示例数据，这将输出:

| ID | SORTNUM |   NAME | ITEMTYPE |
|----|---------|--------|----------|
|  7 |       2 |  ware7 |    usual |
| 10 |       9 | ware10 |  digital |
|  3 |       4 |  ware3 |    usual |
|  2 |       7 |  ware2 |    usual |
|  1 |       8 |  ware1 |    usual |
|  6 |      10 |  ware6 |  digital |
|  4 |      11 |  ware4 |    usual |
|  8 |       6 |  ware8 |  digital |
|  9 |      15 |  ware9 |    usual |
|  5 |      21 |  ware5 |    usual |

实际上，进一步看，我认为您可以将查询更改为:

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

Sample SQL Fiddle for second version.