php - 声明变量 MySQL
问题描述
我正在尝试声明变量并像这样运行查询:
$sql = "SELECT @dateconsult := (YEARWEEK('2018-10-01',3)),
@countunits := ( SELECT COUNT(s.id_production)
FROM sw_sowing
WHERE status != 0
AND YEARWEEK(date,3) <= @dateconsult
GROUP BY id_production_unit_detail
),
@quadrants := ( SELECT DISTINCT value
FROM cf_config
WHERE parameter = 'PLANTHEALTH'
);
SELECT FORMAT(((count_quadrant * 100)/(total_units * Cuadrantes)),3) AS incidence
FROM (
SELECT @countunits AS total_units, @quadrants AS Cuadrantes,
FROM ph_planthealth
INNER JOIN ph_planthealth_detail ON ph_planthealth_detail.id_p = ph_planthealth.id
WHERE YEARWEEK(ph_planthealth.date,3) = @dateconsult
AND ph_planthealth.status = 200
AND ph_planthealth.id_tenant = 1
AND ph_planthealth_detail.id_plague != 0
GROUP BY ph_planthealth_detail.id_plague
) AS s
ORDER BY incidence DESC; ";
$plague = $this->db->fetchAll($sql, Phalcon\Db::FETCH_ASSOC, $options) ";
问题是它显示了第一个 SELECT 的结果,这是我声明的变量,而不是作为主查询的第二个 SELECT 的结果。
这是我第一次声明变量,我不知道我是否做得对。
感谢您对此主题的评论和帮助。
解决方案
您不需要在单独的SELECT
. 您可以通过加入主查询来完成它们。
SELECT FORMAT(((count_quadrant * 100)/(total_units * Cuadrantes)),3) AS incidence
FROM (
SELECT @countunits AS total_units, @quadrants AS Cuadrantes,
FROM ph_planthealth
INNER JOIN ph_planthealth_detail ON ph_planthealth_detail.id_p = ph_planthealth.id
WHERE YEARWEEK(ph_planthealth.date,3) = @dateconsult
AND ph_planthealth.status = 200
AND ph_planthealth.id_tenant = 1
AND ph_planthealth_detail.id_plague != 0
GROUP BY ph_planthealth_detail.id_plague
) AS s
CROSS JOIN (
SELECT @dateconsult := (YEARWEEK('2018-10-01',3)),
@countunits := ( SELECT COUNT(s.id_production)
FROM sw_sowing
WHERE status != 0
AND YEARWEEK(date,3) <= @dateconsult
GROUP BY id_production_unit_detail
),
@quadrants := ( SELECT DISTINCT value
FROM cf_config
WHERE parameter = 'PLANTHEALTH'
)
) AS vars
ORDER BY incidence DESC
推荐阅读
- java - 修复 java 中交错的正则表达式替换输出
- python - 在 Django 上进行迁移
- ubuntu - /bin/sh: -c: 第 0 行:在寻找匹配的 `"' 时出现意外的 EOF
- javascript - Redux - Hooks:将 React Hooks 与 Redux Hooks 一起使用,创建无限循环或什么都不做
- ios - 无法将类型“(QueryDocumentSnapshot).Type”的值转换为预期的参数类型“QueryDocumentSnapshot”
- python - PyTorch LSTM 为 MSELoss 提供了 nan
- r - 使用条件-R studio 计算单元格中的出现次数
- java - Intellij Idea 错误:缺少 kotlin-compiler.jar
- c++ - 在二维数组中使用 long long int
- c# - 如何通过IDAutomation_Code39FreeFont将条形码存储到c#asp.net MVC上的sql server中?