mysql:带as变量的查询不起作用

ljo96ir5 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(523)

我做了一个查询，我用它创建了一个变量accountid，我不能在我的子查询中重用accountid为什么？
这是我的密码：

SELECT userId AS accountId,
        (SELECT recommended FROM ads_connections WHERE byUserId = accountId AND throughUserId = accountId AND adId = :recommendedAdId) AS recommended,
        --
        (SELECT requestStatus FROM ads_recommends_requests WHERE 
                                                                userId = :currentUserId AND 
                                                                requestFromUserId = accountId AND 
                                                                advertisementId = :statusAdId) AS requestStatus,

        (SELECT COUNT(id) FROM ads_recommends_requests 
                WHERE 
                    requestFromUserId = accountId AND 
                    advertisementId = :countAdId AND 
                    requestStatus = '1'
        ) AS recommendedTimes
        --
        FROM user_contacts

        WHERE userId IN " . $myCircleString . "
        AND phoneNumber = :phoneNumber
        AND (SELECT fictive FROM users WHERE id = userId) = 0
        ";

输出示例：

"data": [
    {
        "userId": 0,
        "accountId": 2,
        "fictive": false,
        "name": "Mickael",
        "phoneNumber": "0584688888",
        "review": "0",
        "liked": false,
        "recommended": 0,
        "requestStatus": 0,
        "recommendedTimes": 0
    }

谢谢

sql mysql sql-server php subquery

来源：https://stackoverflow.com/questions/53812295/mysql-query-with-as-variable-not-working

2条答案

按热度按时间

5ktev3wc1#

看看这个-

SELECT userId AS accountId,
        (SELECT recommended FROM ads_connections WHERE byUserId = userId AND throughUserId = userId AND adId = :recommendedAdId) AS recommended,
        --
        (SELECT requestStatus FROM ads_recommends_requests WHERE 
                                                                userId = :currentUserId AND 
                                                                requestFromUserId = userId AND 
                                                                advertisementId = :statusAdId) AS requestStatus,

        (SELECT COUNT(id) FROM ads_recommends_requests 
                WHERE 
                    requestFromUserId = userId AND 
                    advertisementId = :countAdId AND 
                    requestStatus = '1'
        ) AS recommendedTimes
        --
        FROM user_contacts

        WHERE userId IN " . $myCircleString . "
        AND phoneNumber = :phoneNumber
        AND (SELECT fictive FROM users WHERE id = userId) = 0
        ";

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

n53p2ov02#

应答
按以下方式创建变量：

SELECT @acctId := userId AS accountId

并使用它：

(SELECT requestStatus FROM ads_recommends_requests WHERE
                   userId = :currentUserId AND
                   requestFromUserId = @acctId AND
                   advertisementId = :statusAdId) AS requestStatus

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

我来回答

mysql:带as变量的查询不起作用

2条答案

相关问题

热门标签

最新问答