我使用php, mysql和html一起显示一个表。但我想按时间戳对项目进行排序,并将具有相同id的项目分组在一起。例子:
Unsorted table:
timestamp id name description
14:02 2 Hans Is very nice!
12:01 3 Jürgen Is very bad!
10:03 2 Hans Again good.
11:08 6 Anna BAD!
06:09 11 Peter Good.
Right sorted table:
timestamp id name description
06:09 11 Peter Good.
10:03 2 Hans Again good.
14:02 2 Hans Is very nice!
11:08 6 Anna BAD!
12:01 3 Jürgen Is very bad!
Bad sorted table:
timestamp id name description
06:09 11 Peter Good.
10:03 2 Hans Again good.
11:08 6 Anna BAD!
12:01 3 Jürgen Is very bad!
14:02 2 Hans Is very nice!
我有以下的,但不工作:
SELECT * FROM fehlerfilm ORDER BY timestamp, imdb_id
我得到Bad排序表。
我要在最后的所有项目!过程:
- SELECT all并查找TIMESTAMP时间最长的项具有相同ID的物品?->如果是,将其分组在最老的项(等于TIMESTAMP)旁边。->如果不,查找第二个最老的项目,然后再执行
派生表有一个相关的子查询,它返回每个imdb_id的最低时间戳。按列排序:
SELECT timestamp, id, name, description
from
(
select f1.*, (select min(timestamp) from fehlerfilm f2
where f2.imdb_id = f1.imdb_id) as mintime
from fehlerfilm f1
)
ORDER BY mintime, imdb_id, timestamp
您可以使用以下查询,它将按id分组,并按时间戳降序排序,如果您首先想要最近的数据。