从连接表中try 按唯一ID计算行数 MySQL

SELECT r.reference, r.status, pcla.la_name, r.project_reference, 
(SELECT COUNT(DISTINCT(id))
FROM measure
WHERE status_calculation <> "Cancelled") AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing" 
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;

SELECT r.reference, r.status, pcla.la_name, r.project_reference, 
(SELECT COUNT(DISTINCT(m1.id))
FROM measure m1
WHERE m1.status_calculation <> "Cancelled"
and m1.reference = r.reference) AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing" 
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;

Mysql相关问答推荐

—符号在哪里条件""

如何在WooCommerce中更新pm_Virtual.meta_Value=#39；否

如何为Oracle DB查询获得所需的GROUP BY结果？

运行简单查询时Prisma预准备语句错误

无法确定查询逻辑

为什么用PyMySQL构建的json返回结果会出现重复？

完全匹配 IN 子句中的所有值

如何获取每日登录用户数？

try 在 .map 中使用 Favorite 选项卡进行react .我无法更改 mysql 表上的布尔类型

MySQL - 如何查询涉及前面计算值的表结果

MySQL 8 - MBRContains 不使用空间索引

MySQL过滤食谱上的多对多 - 成分表

Golang Gorm没有从关联表中检索数据

MySQL：低基数/ Select 性列=如何索引？

如何在mysql中连接整数(整数和整数)和varchar(nvarchar和varchar)等数据类型？

MySQL SELECT 增量计数器

MySQL 导出到 outfile：CSV 转义字符

使用 mysqldriver 连接数据库时出错

如何将 MySQL 查询结果存储在另一个表中？

获取最后一组不同的记录