您似乎正在以非规范化的方式存储多值属性。
如果需要对这些值进行面向集的比较,它们应该存储在
排
,而不是列。
您可以“取消激活”它们,因此每个值都在自己的行上,如下所示:
SELECT 1 AS ValNo, A1 AS Val FROM MyTable WHERE rowID=1
UNION ALL
SELECT 2, A2 FROM MyTable WHERE rowID=1
UNION ALL
SELECT 3, A3 FROM MyTable WHERE rowID=1
UNION ALL
SELECT 4, A4 FROM MyTable WHERE rowID=1
UNION ALL
...
UNION ALL
SELECT 100, A100 FROM MyTable WHERE rowID=1
然后将其放入子查询中,得到最低的6个值。
SELECT ValNo, Val
FROM ( ... subquery above ... ) AS t
ORDER BY Val
LIMIT 6
最好存储一个表,其中一列是值,最多100
排
为每个人
rowId
以下内容:
CREATE TABLE MyNewTable (
RowId INT,
OrdinalId TINYINT, -- 1 to 100
Aval INT,
PRIMARY KEY (RowId, OrdinalId)
);
然后您可以更简单地查询它:
SELECT OrdinalId, Aval
FROM MyNewTable
WHERE RowId = 1
ORDER BY Aval
LIMIT 6;
