sql多条件组合查询

Question

sql如何对列中的部分数据做统计，结果显示在其它列。
以下是想要的结果

我的sql代码

select year,cecp_id,my_type,totalCount,completeCount,unCompleteCount,completePercent,concat(TRUNCATE(completeCount/totalCount * 100,1) ,'%') from
        (
        SELECT year,cecp_id,my_type,COUNT(1) totalCount,SUM(status=1) completeCount,SUM(status=0) unCompleteCount,CONCAT(TRUNCATE(SUM(status=1)/COUNT(1) * 100,1) ,'%') completePercent  FROM
        (SELECT Year(t2.created) year,my_type,cecp_id,status
        FROM
        (SELECT created,cecp_id,ceci_id,ns_box ,my_type ,cecc.id cecc_id
        FROM
        (select cecp.created,cecp.id cecp_id,ceci.id ceci_id,ns_box ,ceci.`type` my_type
        FROM corp_equipment_check_plan cecp
        inner join corp_equipment_check_item ceci on cecp.id = ceci.corp_equipment_check_plan_id) t1
        inner join corp_equipment_check_circle cecc on cecc.corp_equipment_check_plan_id = t1.cecp_id) t2
        inner join corp_equipment_check_circle_item cecci on t2.cecc_id = cecci.corp_equipment_check_circle_id AND
        t2.cecp_id = cecci.corp_equipment_check_plan_id AND t2.ceci_id = cecci.corp_equipment_check_item_id
        where my_type = 0
        )t3 group by cecp_id
        union all
        SELECT year,cecp_id,my_type,COUNT(1) totalCount,SUM(status=1) completeCount,SUM(status=0) unCompleteCount,CONCAT(TRUNCATE(SUM(status=1)/COUNT(1) * 100,1) ,'%') completePercent FROM
        (SELECT Year(t2.created) year,my_type,cecp_id,status
        FROM
        (SELECT created,cecp_id,ceci_id,ns_box ,my_type ,cecc.id cecc_id
        FROM
        (select cecp.created,cecp.id cecp_id,ceci.id ceci_id,ns_box ,ceci.`type` my_type
        FROM corp_equipment_check_plan cecp
        inner join corp_equipment_check_item ceci on cecp.id = ceci.corp_equipment_check_plan_id) t1
        inner join corp_equipment_check_circle cecc on cecc.corp_equipment_check_plan_id = t1.cecp_id) t2
        inner join corp_equipment_check_circle_item cecci on t2.cecc_id = cecci.corp_equipment_check_circle_id AND
        t2.cecp_id = cecci.corp_equipment_check_plan_id AND t2.ceci_id = cecci.corp_equipment_check_item_id
        where my_type = 1
        )t3 group by cecp_id
        union all
        SELECT year,cecp_id,my_type,COUNT(1) totalCount,SUM(status=1) completeCount,SUM(status=0) unCompleteCount,CONCAT(TRUNCATE(SUM(status=1)/COUNT(1) * 100,1) ,'%') completePercent FROM
        (SELECT Year(t2.created) year,my_type,cecp_id,status
        FROM
        (SELECT created,cecp_id,ceci_id,ns_box ,my_type ,cecc.id cecc_id
        FROM
        (select cecp.created,cecp.id cecp_id,ceci.id ceci_id,ns_box ,ceci.`type` my_type
        FROM corp_equipment_check_plan cecp
        inner join corp_equipment_check_item ceci on cecp.id = ceci.corp_equipment_check_plan_id) t1
        inner join corp_equipment_check_circle cecc on cecc.corp_equipment_check_plan_id = t1.cecp_id) t2
        inner join corp_equipment_check_circle_item cecci on t2.cecc_id = cecci.corp_equipment_check_circle_id AND
        t2.cecp_id = cecci.corp_equipment_check_plan_id AND t2.ceci_id = cecci.corp_equipment_check_item_id
        where my_type = 2
        )t3 group by cecp_id  order by cecp_id , my_type )t4

数据库运行sql后的结果

totalCompletePercent应该是相同cecp_id的数据，它们的completeCount 和 totalCount的比值。
例如cecp_id=252，totalCompletePercent = 58+0+0/340+20+10 =15.6%，15.6%显示在这三条数据的totalCompletePercent列。

Answer 1

你可以参考一下我之前写的存储过程:
https://ask.csdn.net/questions/7649716?spm=1001.2014.3001.5505
可以看看chatGPT写的,源于chatGPT仅供参考

根据您提供的 SQL 查询，看起来您想对列中的部分数据进行统计，并将结果显示在其他列中。在给出具体的数据库结构和样例数据之前，下面是对您的查询进行解释的简化版本：

```sql
SELECT
  year,
  cecp_id,
  my_type,
  COUNT(1) AS totalCount,
  SUM(status = 1) AS completeCount,
  SUM(status = 0) AS unCompleteCount,
  CONCAT(TRUNCATE(SUM(status = 1) / COUNT(1) * 100, 1), '%') AS completePercent
FROM
  (
    SELECT
      Year(t2.created) AS year,
      my_type,
      cecp_id,
      status
    FROM
      (
        SELECT
          created,
          cecp_id,
          ceci_id,
          ns_box,
          my_type,
          cecc.id AS cecc_id
        FROM
          (
            SELECT
              cecp.created,
              cecp.id AS cecp_id,
              ceci.id AS ceci_id,
              ns_box,
              ceci.`type` AS my_type
            FROM
              corp_equipment_check_plan cecp
              INNER JOIN corp_equipment_check_item ceci ON cecp.id = ceci.corp_equipment_check_plan_id
          ) t1
          INNER JOIN corp_equipment_check_circle cecc ON cecc.corp_equipment_check_plan_id = t1.cecp_id
      ) t2
      INNER JOIN corp_equipment_check_circle_item cecci ON t2.cecc_id = cecci.corp_equipment_check_circle_id
                                                          AND t2.cecp_id = cecci.corp_equipment_check_plan_id
                                                          AND t2.ceci_id = cecci.corp_equipment_check_item_id
    WHERE
      my_type IN (0, 1, 2)
  ) t3
GROUP BY
  cecp_id
ORDER BY
  cecp_id,
  my_type

根据查询中的代码，您正在从较大的查询结果集中获取数据，并使用聚合函数和条件表达式进行统计。在内部查询中，通过连接多个表并应用过滤条件来检索相应的数据。

请注意，由于我无法获得您的具体数据库结构和样例数据，因此无法提供完整和准确的解决方案。要对列中的部分数据进行统计，您可以尝试以下步骤：

1. 确保查询的内部子查询能够正确地返回所需的数据。
2. 检查聚合函数 COUNT、SUM 和 CONCAT 的用法是否正确。
3. 根据需要添加或修改 WHERE 子句以过滤特定的 my_type 值。
4. 调整 SELECT 子句以选择所需的列和进行适当的重命名。

请根据您的实际情况修改查询，包括表名、列名和条件，以便与您的数据库结构匹配。如果有更多关于数据库结构和样例数据的详细信息，请提供给我，以便我能够为您提供更准确的帮助。

```