在其他select中使用列值

7kqas0il 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(220)

我想选择每一个类别的计数，每个产品在这个类别和每个子类别它是。
此sql查询返回一个id为的类别中包含的产品的计数 X 考虑到其子类别产品。

SELECT
    count(*)
FROM productos
INNER JOIN categorias
ON categorias.id = productos.categoria_id
WHERE productos.categoria_id IN (select  id
        FROM    (SELECT * FROM categorias
                 ORDER BY parent, id) products_sorted,
                (SELECT @pv := X) initialisation
        WHERE   find_in_set(parent, @pv)
        AND     length(@pv := concat(@pv, ',', id))
        OR      id = @pv)
        AND productos.active IS TRUE AND categorias.active IS TRUE
ORDER BY categorias.pos) as productos

现在我试图用他们的产品计数来获取我的每一个类别，这是我的sql，但它说的是未知表 cats 在字段列表中。我也试过不用化名。我想我不能在第二个选择中使用它。那么，我该怎么做呢？

SELECT
    cats.*,
    (SELECT
        count(*)
    FROM productos
    INNER JOIN categorias
    ON categorias.id = productos.categoria_id
    WHERE productos.categoria_id IN (select  id
            FROM    (SELECT * FROM categorias
                     ORDER BY parent, id) products_sorted,
                    (SELECT @pv := cats.id) initialisation
            WHERE   find_in_set(parent, @pv)
            AND     length(@pv := concat(@pv, ',', id))
            OR      id = @pv)
            AND productos.active IS TRUE AND categorias.active IS TRUE
    ORDER BY categorias.pos) as productos
FROM categorias AS cats ORDER BY cats.parent, cats.pos

table

+----------------------+
| Catrgorias           |
+----------------------+
| id (int 11)          |
+----------------------+
| nombre (varchar 255) |
+----------------------+
| parent (int 11)      |
+----------------------+
| active (tinyint 11)  |
+----------------------+
| pos (int 11)         |
+----------------------+
+----+------------+--------+
| id | nombre     | parent |
+----+------------+--------+
| 1  | Cat1       | NULL   |
+----+------------+--------+
| 2  | Cat2.      | NULL   |
+----+------------+--------+
| 3  | SubCat1    | 1      |
+----+------------+--------+
| 4  | SubCat2    | 2      |
+----+------------+--------+
| 5  | SubSubCat1 | 3      |
+----+------------+--------+

+-----------------------+
| Productos             |
+-----------------------+
| id (int 11)           |
+-----------------------+
| name (varchar 255)    |
+-----------------------+
| description (text)    |
+-----------------------+
| image (varchar 255)   |
+-----------------------+
| price (decimal 11,2)  |
+-----------------------+
| categoria_id (int 11) |
+-----------------------+
| pos (int 11)          |
+-----------------------+
| active (tinyint 11)   |
+-----------------------+

sql mysql

来源：https://stackoverflow.com/questions/53516572/use-column-value-in-other-select

1条答案

按热度按时间

cgh8pdjw1#

在错误消息中是否有行被感染？我认为问题是

(SELECT @pv := cats.id)

我会改变的

(SELECT @pv := id FROM CATEGORIAS)

我不知道这是否是你的解决方案，但在你的查询中我看不到其他结构性问题。
为了得到同样的结果，我写了这样的东西，检查它是否有效（你确定你在评论中写的数字吗？）

SELECT ID, SUM(N) FROM

# COLL DIRETTI

((SELECT A.ID AS ID, COUNT(*) AS N
FROM CATEGORIAS A
LEFT JOIN PRODUCTOS B
ON A.ID = B.CATEGORIA_ID
WHERE A.ACTIVE IS TRUE AND B.ACTIVE IS TRUE
GROUP BY A.ID)
UNION ALL

# PRIMA GENERAZIONE

(SELECT C.PARENT AS ID, D.N AS N
FROM CATEGORIAS C
LEFT JOIN (SELECT A.ID, COUNT(*) AS N
FROM CATEGORIAS A
LEFT JOIN PRODUCTOS B
ON A.ID = B.CATEGORIA_ID
WHERE A.ACTIVE IS TRUE AND B.ACTIVE IS TRUE
GROUP BY A.ID) D
ON C.ID = D.ID
WHERE C.PARENT IS NOT NULL
AND D.N IS NOT NULL)
UNION ALL

# SECONDA GENERAZIONE

(SELECT CC.PARENT AS ID, DD.N AS N
FROM
(SELECT AA.ID AS ID, BB.PARENT AS PARENT
FROM CATEGORIAS AA
LEFT JOIN CATEGORIAS BB
ON AA.PARENT=BB.ID
WHERE BB.PARENT IS NOT NULL) CC
LEFT JOIN (SELECT A.ID, COUNT(*) AS N
FROM CATEGORIAS A
LEFT JOIN PRODUCTOS B
ON A.ID = B.CATEGORIA_ID
WHERE A.ACTIVE IS TRUE AND B.ACTIVE IS TRUE
GROUP BY A.ID) DD
ON CC.ID = DD.ID
WHERE DD.N IS NOT NULL)
UNION ALL

# TERZA GENERAZIONE

(SELECT CCC.PARENT AS ID, DDD.N AS N
FROM
(SELECT AA.ID AS ID, CC.PARENT AS PARENT
FROM CATEGORIAS AA
LEFT JOIN CATEGORIAS BB
ON AA.PARENT=BB.ID
LEFT JOIN CATEGORIAS CC
ON BB.PARENT=CC.ID
WHERE CC.PARENT IS NOT NULL) CCC
LEFT JOIN (SELECT A.ID, COUNT(*) AS N
FROM CATEGORIAS A
LEFT JOIN PRODUCTOS B
ON A.ID = B.CATEGORIA_ID
WHERE A.ACTIVE IS TRUE AND B.ACTIVE IS TRUE
GROUP BY A.ID) DDD
ON CCC.ID = DDD.ID
AND DDD.N IS NOT NULL)) TOT
GROUP BY ID

赞(0）回复(0）举报 2021-06-18

我来回答

在其他select中使用列值

1条答案

相关问题

热门标签

最新问答