操作数应在 MySQL 中包含 1 列

Question

我正在使用Codeigniter并使用分组依据显示当前月份数据。我正在使用下面的查询。

$month="MONTH(date_of_created) = MONTH(CURRENT_DATE())";
$group="f_id IN (SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid)";
if(($this->session->userdata['login_session']['access_role']==1) || ($this->session->userdata['login_session']['access_role']==2)){
$or_where="leadstatus='1' AND is_leadConfirm='1' AND ".$month;
}
else{
$or_where="leadstatus='1'  AND createby='".$this->session->userdata['login_session']['id']."' AND ".$month;
}
$query="select 
(SELECT COUNT(c_id) FROM tbl_lead WHERE ".$or_where." ) as confirmCount, 
(SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=1 ) as docCount, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=1 and ".$month." and ".$group.") as a, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=2 and ".$month." and ".$group.") as b, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=3 and ".$month." and ".$group.") as c, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=4 and ".$month." and  ".$group.") as d";

我得到的输出是正确的。

Array
(
[0] => stdClass Object
(
[confirmCount] => 1
[docCount] => 1
[a] => 0
[b] => 0
[c] => 2
[d] => 0
)
)

现在，我有两个疑问

1(上述查询是最好的吗？还有其他简单的方法吗？

2(我必须更改此子查询

(SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=1 ) as docCount 

自

(SELECT (SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=1) as doc_comp, (SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=2) as doc_online) as docCount

这样我就会得到这样的输出(这是我的预期输出(

Array
(
[0] => stdClass Object
(
[confirmCount] => 1
[docCount] =>(
[doc_comp] => 1
[doc_online] => 1
),
[a] => 0
[b] => 0
[c] => 2
[d] => 0
)
)

我尝试了以下查询，但遇到了这个问题。我知道我收到不止一列，但你能帮我正确查询吗？

操作数应包含 1 列

$query="select 
(SELECT COUNT(c_id) FROM tbl_lead WHERE ".$or_where." ) as confirmCount, 
(SELECT (SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=1) as doc_comp, (SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=2) as doc_online) as docCount, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=1 and ".$month." and ".$group.") as a, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=2 and ".$month." and ".$group.") as b, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=3 and ".$month." and ".$group.") as c, 
(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=4 and ".$month." and  ".$group.") as d";

Answer 1

Json 只是文本：

所以你可以这样做

select CONCAT('{ doc_comp: '
,(SELECT COUNT(c_id) FROM tbl_lead WHERE ".$or_where." )
,', docCount: { confirmCount : '
,(SELECT COUNT(c_id) FROM tbl_lead WHERE ".$or_where." )
,', doc_comp : '
,(SELECT COUNT(doc_id) FROM tbl_documentsPickup WHERE d_pickupStatus=1)
,' } }, a : '
, (SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=1 and ".$month." and ".$group.")
,', b : '
,(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=1 and ".$month." and ".$group.")
,' c : '
, (SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=3 and ".$month." and ".$group.")
,' d :'
,(SELECT COUNT(f_id) FROM tbl_fileStatus WHERE f_filestatus=4 and ".$month." and  ".$group.") 
,'}');

由于您没有提供数据，我没有测试json，因此您应该在jsonlint中对其进行测试