gpt4 book ai didi

mysql - 使用函数 FOUND_ROWS() 时出现连接给出错误结果的问题

转载 作者:行者123 更新时间:2023-11-29 11:35:58 26 4
gpt4 key购买 nike

这是我的查询。

SELECT SQL_CALC_FOUND_ROWS (FOUND_ROWS() ) as total, 
(SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization
WHERE dsp.profileid = pm.id and
(dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec ,
pm.id as profileid, pm.loginid as loginid, dam.clinicname, dam.area, dam.address, dam.pincode, dam.id as
addressid, dam.feecharge as feecharge, pm.fname, pm.lname, pm.email, pm.mobile, pm.phone, pm.gender, pm.dob,
pm.totexp, pm.imagepath, pm.languages, pm.statement, pm.createdby, um.profile_url, um.clinic_url,
pm.hsbit, (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm LEFT JOIN DAddress_Master dam on dam.profileid = pm.id left join Unique_Url_Master um on
um.clinicid =dam.id WHERE dam.city='Surat' and pm.id IN (SELECT profileid FROM DSpecialization_Master
WHERE specialization = (select id from Specialization_master where specialization='Dentist')) ORDER BY pm.id limit 0 , 10

接受的结果是total:344,表明我得到total:1

我已经执行了这个查询,并且得到了正确的结果。

select SQL_CALC_FOUND_ROWS id,(FOUND_ROWS() ) as total from unique_url_master1 limit 10;

总计:1313

我做错了什么?仅供引用,我无法中断查询,即首先执行

select SQL_CALC_FOUND_ROWS

并执行

select FOUND_ROWS()

非常需要帮助..

编辑

我更改了查询并使用 count(*) 函数。现在我得到了正确的 total:344 但它只返回一行。现在有人可以指导我吗?

SELECT  count(*) as total_count ,
(SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec,
pm.id as profileid,
pm.loginid as loginid,
dam.clinicname,
dam.area,
dam.address,
dam.pincode,
dam.id as addressid,
dam.feecharge as feecharge,
pm.fname,
pm.lname,
pm.email,
pm.mobile,
pm.phone,
pm.gender,
pm.dob,
pm.totexp,
pm.imagepath,
pm.languages,
pm.statement,
pm.createdby,
um.profile_url,
um.clinic_url,
pm.hsbit,
(SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10;

我已经使用 COUNT(*) OVER() 在 MSSQL 中进行查询,由于需求更改,我必须将 MSSQL 查询转换为 MYSQl。结果我接受这样的this

最佳答案

您使用的聚合函数COUNT没有n GROUP BY,这将始终生成一行。 Definition of GROUP BY

最好只使用 2 个单独的查询来获得您想要的结果,其中一个查询选择所有其他变量,而在一个查询中仅选择计数

所有行+变量

SELECT  (SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
pm.id as profileid,
pm.loginid as loginid,
dam.clinicname,
dam.area,
dam.address,
dam.pincode,
dam.id as addressid,
dam.feecharge as feecharge,
pm.fname,
pm.lname,
pm.email,
pm.mobile,
pm.phone,
pm.gender,
pm.dob,
pm.totexp,
pm.imagepath,
pm.languages,
pm.statement,
pm.createdby,
um.profile_url,
um.clinic_url,
pm.hsbit,
(SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10;

计数:

SELECT  count(*) as total_count,
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10;

如果您确实希望它像一次查询中的图像一样,则必须执行以下操作:

SELECT T1.overall_count, T2.*
FROM (SELECT COUNT(*) AS overall_count
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10) AS T1
JOIN ((SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec,
pm.id as profileid,
pm.loginid as loginid,
dam.clinicname,
dam.area,
dam.address,
dam.pincode,
dam.id as addressid,
dam.feecharge as feecharge,
pm.fname,
pm.lname,
pm.email,
pm.mobile,
pm.phone,
pm.gender,
pm.dob,
pm.totexp,
pm.imagepath,
pm.languages,
pm.statement,
pm.createdby,
um.profile_url,
um.clinic_url,
pm.hsbit,
(SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu
FROM Profile_Master pm
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id
WHERE dam.city='Surat'
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist'))
ORDER BY pm.id
LIMIT 0, 10) AS T2

关于mysql - 使用函数 FOUND_ROWS() 时出现连接给出错误结果的问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36590285/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com