php - MySQL:使用 AS 变量查询不起作用
问题描述
我做了一个查询,使用它创建了一个变量 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
}
谢谢
解决方案
检查这个-
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
";
推荐阅读
- google-colaboratory - 使用 Gsuite 的 Colab pro
- ios - ScrollView 滚动出约束
- c++ - gmock,预期:被调用一次实际:从未调用
- excel - Excel中的动态乘法
- sprite-kit - Watch OS Sprite Kit 节点上可以添加什么样的组件以及如何添加?
- r - 基于另一列在 DF 上创建迭代器
- javascript - Vue.js 使用给定的 html 数据
- python-3.x - 如何在 Flask-Admin 中禁用导航栏?
- mysql - 在 Node.js 中同时生成的发票具有相同的编号。如何实现独特性?
- jquery - 如果数据库中存在记录,则执行条件