我有这个表:
item_id item_name item_status visibility "item_number"
6 foo6 3 1 4
5 foo5 2 1 3
4 foo4 1 0 x
3 foo3 3 1 2
2 foo2 2 0 x
1 foo1 1 1 1
项目由visibility
字段"支配"。如果我想将可见性设置为0,例如items_id
的2和4,我希望"item_number
"(不是实列)进行相应调整。
如果我选择item_status
为2的项,它将返回状态为2的项目,但"已调整"的项目的item_number
为3。
这在PHP+MySQL中可能吗?
通常情况下,您会将item_id
作为项目的id。然而,问题是,即使删除了一个项目(在本例中,它是visibility
列),MySQL的自动增量仍然会继续。因此,如果我最近添加了项目6,然后删除了项目2和4,则下一个item_id
将具有7,但数据库中只有4个项目。对于这些"可见"项目,我希望项目编号为1、2、3和4,而不是id为1、3、5和6。
此外,如果我得到item_status
为2的项目,通常情况下,你会得到item_id
为5的项目。但该项目在数据库中仅排名第三(由于之前删除了项目),所以我想返回3。
SELECT item_id, item_name, item_status, visibility,
CASE WHEN (visibility !=0) THEN @itemCount := @itemCount +1 END AS item_number
FROM items
JOIN (SELECT @itemCount :=0) AS vars
ORDER BY item_id
这将导致:
+---------+------------+-------------+------------+-------------+
| item_id | item_name | item_status | visibility | item_number |
+---------+------------+-------------+------------+-------------+
| 1 | Banana | 1 | 1 | 1 |
| 2 | Apple | 4 | 1 | 2 |
| 3 | Orange | 3 | 1 | 3 |
| 4 | Strawberry | 2 | 0 | NULL |
| 5 | Pear | 5 | 1 | 4 |
| 6 | Plum | 4 | 0 | NULL |
| 7 | Grape | 2 | 0 | NULL |
| 8 | Peach | 3 | 1 | 5 |
| 9 | Papaya | 6 | 1 | 6 |
| 10 | Melon | 7 | 0 | NULL |
+---------+------------+-------------+------------+-------------+
弄清楚了,(不幸的是)需要一个嵌套查询:
SELECT * //select all columns
FROM ( //from a derived table
SELECT
b.*, //select all columns
@itemCount:=@itemCount +1 AS item_number //and a counter column named item_number
FROM items b, (SELECT @itemCount :=0) r //from items table and with the counter column initialized as 0
WHERE visibility != 0 //select only those visible
) AS b
LIMIT 0, 30 //limit to 30 rows
它做了什么:
- 选择了具有可见项目的所有行(这将影响性能)
- 由于只显示可见项目,因此它们是唯一"计数"的项目
- 上面的查询是一个完整的结果集,现在是父选择的派生表
- 父选择然后根据需要的行数限制行并保留计数
因此,如果我想显示从1000开始的30行,它将显示从1000起的30个可见项目,计数从1000 开始
参考:
- 添加计数器列
- 使用派生表