我有两张这样的桌子:
表:项目

id | itemId
---|------
0  | 1
1  | 2
2  | 3

表:项目规格
id | itemId | key          | values
---|--------|---------------
0  | 1      | itemreceived | 2012-06-01
1  | 1      | modelyear    | 1992
2  | 1      | model        | 2
3  | 2      | itemreceived | 2012-06-05
4  | 2      | modelyear    | 2003
5  | 2      | model        | 1
6  | 3      | itemreceived | 2012-07-05
7  | 3      | modelyear    | 2000
8  | 3      | model        | 3

我当前的查询如下:
SELECT items.*, item_specs.* FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
WHERE itemId IN(1,2,3)

如何按键值排序结果,例如:model?
我要找的结果是这样的:(如果我按型号订购)
id | itemId | key          | values
---|--------|---------------
3  | 2      | itemreceived | 2012-06-05
4  | 2      | modelyear    | 2003
5  | 2      | model        | 1
0  | 1      | itemreceived | 2012-06-01
1  | 1      | modelyear    | 1992
2  | 1      | model        | 2
6  | 3      | itemreceived | 2012-07-05
7  | 3      | modelyear    | 2000
8  | 3      | model        | 3

返回的内容按具有键模型的值排序

最佳答案

你需要每行的型号。你可以通过一个join来实现:

SELECT items.*, item_specs.*
FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
INNER JOIN item_specs aux ON (aux.key = 'model' and aux.itemID = item_specs.itemId)
WHERE item_specs.itemId IN(1,2,3)
ORDER BY aux.values/*this is the model*/, item_specs.id;

或使用子选择:
SELECT items.*,
       item_specs.*,
       (select aux.values
        from item_specs aux
        where aux.key = 'model' and aux.itemID = item_specs.itemId
        ) as model
FROM item_specs
INNER JOIN item_specs ON items.itemId = item_specs.itemId
WHERE item_specs.itemId IN(1,2,3)
ORDER BY model, item_specs.id;

10-04 16:10