mysql为什么变量在if中没有改变

Question

我有一个 mysql 查询和 IF 子句。如果为 false，@cid 变为 category_id，@a 必须变为 1。突出显示行下方的行必须再次具有 @a 1。

为什么在@cid 发生变化时@a 保持不变？

代码

SET @a:=0;
SET @cid:=0;
SELECT @a, @cid, q.* FROM (
    SELECT *
    FROM photos
    ORDER BY category_id
) AS q
WHERE IF(@cid=q.category_id, @a:=@a+1, (@a:=1) AND (@cid:=q.category_id))

期望的目标

对于每个新的 category_id - @a 必须一次又一次地从 1 开始

这一切的意义

我有一个带照片的画廊。几乎每张照片都有一个类别

所以我有一个图库类别页面，您可以在其中查看所有类别和每个类别的 4 张照片

所以基本上我需要每个类别限制 4

可能的解决方案

我可以简单地选择所有类别 ID，然后 foreach

select * from photos where category_id = 1 LIMIT 4
select * from photos where category_id = 2 LIMIT 4
...

和 union，但我有点不喜欢 20+ select

所以我尝试了当我计算照片时使用嵌套选择只拍摄其中 4 张的方式

完整查询

SET @a:=0;
SET @cid:=0;
SELECT @a, @cid, q.* FROM (
    SELECT
    c.id AS cid,
    c.title AS category_title,
    c.slug AS category_slug,
    i.id AS image_id,
    i.title AS image_title,
    i.slug AS image_slug,
    i.place AS image_place,
    i.created_at AS image_date
    FROM categories AS c
    JOIN photos AS i 
    ON c.id = i.category_id
    WHERE c.visible AND i.visible
    GROUP BY i.id
    HAVING COUNT(i.id) > 0
    ORDER BY c.priority, c.title, i.priority, i.title
) AS q
WHERE IF(@cid=q.cid, @a:=@a+1, (@a:=1) AND (@cid:=q.cid))
AND @a <= 4

但是 - 它没有像上面的简化版本那样将@a 设置为 1

---

示例的愚蠢但可行的解决方案(第 3 条腿)

当我添加到 select 1 AS one并更改 @a:=q.one - 它工作但这很奇怪

愚蠢的工作代码

SET @a:=0;
SET @cid:=0;
SELECT @a, @cid, q.* FROM (
    SELECT *, 1 AS ONE
    FROM photos
    ORDER BY category_id
) AS q
WHERE IF(@cid=q.category_id, @a:=@a+1, (@a:=q.one) AND (@cid:=q.category_id))

---

所以主要问题 - 为什么 @a:=1 不起作用而 @a:=q.one 起作用？

Answer 1

你在找这个吗？

SELECT rn, id, category_id, title
  FROM
(
  SELECT *, @a := IF(@c = category_id, @a + 1, 1) rn, @c := category_id
    FROM photos CROSS JOIN (SELECT @c := NULL, @a := 0) i
   ORDER BY category_id
) q
 WHERE rn <= 4;

输出:

| RN | ID | CATEGORY_ID |  TITLE ||----|----|-------------|--------||  1 | 43 |           1 | Title1 ||  1 | 28 |           2 | Title2 ||  2 | 42 |           2 | Title3 ||  1 | 11 |           3 | Title4 ||  1 |  3 |           4 | Title5 ||  2 | 29 |           4 | Title6 ||  3 | 33 |           4 | Title7 |

Here is SQLFiddle demo

---

Here is a fix for your code to produce the correct row numbers

SET @a:=0;
SET @cid:=0;   
SELECT @a, @cid, q.*, @cid:=q.category_id FROM (
    SELECT *
    FROM photos
    ORDER BY category_id
) AS q
WHERE @a := IF(@cid=q.category_id, @a+1, 1)

这是 SQLFiddle 演示

SQL 语句的执行顺序很重要。