我有两张这样的桌子:
表:项目
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;