SELECT t3.id,t3.name FROM ( SELECT t1.id,t1.name, IF ( FIND_IN_SET( t1.parent_Id, @pids ) > 0, @pids := CONCAT( @pids, ',', t1.id ), 0 ) AS ischild FROM ( SELECT t.id,t.name,t.parent_id FROM idaas_department t ORDER BY parent_Id, id ) t1,( SELECT @pids := '8a8a80867baa3f2b017baa465d8a0001' ) t2 ) t3 where ischild != 0 给我转化成SQL语句
时间: 2023-06-25 12:04:25 浏览: 61
这已经是 SQL 语句了,只不过使用了子查询和变量。以下是同样功能的 SQL 语句,不使用子查询和变量:
SELECT t3.id, t3.name
FROM idaas_department t3
JOIN idaas_department t2 ON FIND_IN_SET(t3.parent_Id, t2.id)
WHERE t2.id = '8a8a80867baa3f2b017baa465d8a0001';
相关问题
SELECT t1.organization_id, t1.organization_name, t1.road_name, SUM( t2.sj_amount ) xfSjAmount, SUM( t2.jl_amount ) xfJlAmount, SUM( t3.xfGeneralSectionAmount ) xfGeneralSectionAmount, SUM( t3.xfGeneralSectionLength ) xfGeneralSectionLength FROM `main_project` t1 LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id LEFT JOIN ( SELECT project_id, data_type, SUM( money ) xfGeneralSectionAmount, SUM( mileage ) xfGeneralSectionLength FROM mat_project_pavement_approval GROUP BY project_id ) t3 ON t3.project_id = t2.id WHERE t1.project_general_name = '修复性养护' AND t1.project_category_name = '路面工程' AND t1.process_status = '100' AND t3.data_type = '1' AND t1.road_name = '常祁高速' AND t1.project_year = '2023' 这条sql中t1.road_name多条数据只会展示一个,导致根据t1.road_name查询不到数据
这个问题的原因可能是使用了`LEFT JOIN`,导致t1表中的多个记录与t2和t3表中的记录进行了匹配。如果t1表中有多个记录的`t1.road_name`值相同,那么在`SELECT`语句中使用`SUM`函数对t2和t3表中的记录进行了合并,使得最终查询结果中只有一个记录。这可能导致你根据`t1.road_name`无法查到完整的数据。
为了解决这个问题,你可以考虑在`SELECT`语句中增加`t1.road_name`字段,并在`GROUP BY`子句中包含该字段,以确保t1表中的每个记录都能够被正确匹配和展示。例如:
```
SELECT
t1.organization_id,
t1.organization_name,
t1.road_name,
SUM(t2.sj_amount) AS xfSjAmount,
SUM(t2.jl_amount) AS xfJlAmount,
SUM(t3.xfGeneralSectionAmount) AS xfGeneralSectionAmount,
SUM(t3.xfGeneralSectionLength) AS xfGeneralSectionLength
FROM
main_project t1
LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id
LEFT JOIN (
SELECT
project_id,
data_type,
SUM(money) AS xfGeneralSectionAmount,
SUM(mileage) AS xfGeneralSectionLength
FROM
mat_project_pavement_approval
GROUP BY
project_id
) t3 ON t3.project_id = t2.id
WHERE
t1.project_general_name = '修复性养护'
AND t1.project_category_name = '路面工程'
AND t1.process_status = '100'
AND t3.data_type = '1'
AND t1.road_name = '常祁高速'
AND t1.project_year = '2023'
GROUP BY
t1.organization_id,
t1.organization_name,
t1.road_name;
```
这样,就可以确保每个`t1.road_name`值都能够正确地展示在查询结果中。
SELECT t1.organization_id, t1.organization_name, SUM( t2.jl_amount ) yfSjAmount, SUM( t2.sj_amount ) yfJlAmount, SUM( t3.money ) yfSectionAmount, SUM( t3.mileage ) yfSectionLength FROM main_project t1 LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id LEFT JOIN mat_project_pavement_approval t3 ON t3.project_id = t2.id WHERE t1.project_general_name = '预防性养护' AND t1.project_category_name = '路面工程' AND t1.process_status = '100' AND t1.organization_id = 'b3f029fa-62ab-476e-9876-b87ce69c41c5' AND t1.project_year = '2023' SQL统计错误,请修改
这条SQL语句缺少结束符号,可以在语句末尾添加分号(;)来解决。
完整的SQL语句应该是:
```
SELECT t1.organization_id, t1.organization_name, SUM(t2.jl_amount) yfSjAmount, SUM(t2.sj_amount) yfJlAmount, SUM(t3.money) yfSectionAmount, SUM(t3.mileage) yfSectionLength
FROM main_project t1
LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id
LEFT JOIN mat_project_pavement_approval t3 ON t3.project_id = t2.id
WHERE t1.project_general_name = '预防性养护'
AND t1.project_category_name = '路面工程'
AND t1.process_status = '100'
AND t1.organization_id = 'b3f029fa-62ab-476e-9876-b87ce69c41c5'
AND t1.project_year = '2023';
```
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)