mysql为什么变量没有在if中改变 [英] mysql Why variable is not changing in if

问题描述

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

为什么@a 保持不变而@cid 发生变化?

代码

SET @a:=0;设置@cid:=0;SELECT @a, @cid, q.* FROM (选择 *从照片按 category_id 排序) 作为 qWHERE 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 4select * from photos where category_id = 2 LIMIT 4...

和工会，但我有点不喜欢 20 多个选择

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

完整查询

SET @a:=0;设置@cid:=0;SELECT @a, @cid, q.* FROM (选择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_dateFROM 类别 AS c加入照片作为我ON c.id = i.category_id哪里 c.visible 和 i.visible按 i.id 分组有计数(i.id)>0按 c.priority, c.title, i.priority, i.title 排序) 作为 qWHERE IF(@cid=q.cid, @a:=@a+1, (@a:=1) AND (@cid:=q.cid))AND @a <= 4

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

<小时>

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

当我添加选择 1 作为一个并更改@a:=q.one - IT WORKS但很奇怪

愚蠢的工作代码

SET @a:=0;设置@cid:=0;SELECT @a, @cid, q.* FROM (SELECT *, 1 AS ONE从照片按 category_id 排序) 作为 qWHERE IF(@cid=q.category_id, @a:=@a+1, (@a:=q.one) AND (@cid:=q.category_id))

<小时>

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

解决方案

你在找这个吗?

SELECT rn, id, category_id, title从(SELECT *, @a := IF(@c = category_id, @a + 1, 1) rn, @c := category_id从照片交叉连接(选择@c := NULL，@a := 0)我按 category_id 排序) q其中 rn <= 4;

输出:

<前>|注册护士 |身份证 |CATEGORY_ID |标题 ||----|----|-------------|--------||1 |43 |1 |标题1 ||1 |28 |2 |标题2 ||2 |42 |2 |标题3 ||1 |11 |3 |标题4 ||1 |3 |4 |标题5 ||2 |29 |4 |标题6 ||3 |33 |4 |标题7 |

这是SQLFiddle 演示

<小时>

这是您的代码生成正确行号的修复

SET @a:=0;设置@cid:=0;SELECT @a, @cid, q.*, @cid:=q.category_id FROM (选择 *从照片按 category_id 排序) 作为 q哪里@a := IF(@cid=q.category_id, @a+1, 1)

这是SQLFiddle演示

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

I have a mysql query and IF clause. If it is false, @cid changes to category_id and @a must change to 1. The row below the highlighted one has to have @a again 1.

Why does @a stay unchanged while @cid is changing ?

code

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))

desired goal

with each new category_id - @a has to start from 1 again and again

the point of all of this

i have a gallery with photos. almost every photo has a category

so i have a gallery categories page, where you can see all categories and 4 photos from each of it

so basically i need limit 4 from each category

possible solution

i can simply make a select of all category ids, then foreach

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

and union, but i kinda dont like 20+ selects

so i tried the way when i count photo and take only 4 of them with nested select

full query

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

BUT - it does not set @a to 1 as in simplified version above

---

STUPID BUT WORKING SOLUTION TO EXAMPLES (3-rd leg)

when i add to select 1 AS one and change @a:=q.one - IT WORKS but it is odd

stupid working code

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))

---

SO the main question - WHY DOES NOT @a:=1 WORK and @a:=q.one DOES WORK ?

解决方案

Are you looking for this?

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;

Output:

| 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)

Here is SQLFiddle demo

The order of SQL statement execution does matter.

这篇关于mysql为什么变量没有在if中改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！