SELECT student_id,count(status) FROM dwd_ms_student_attendance_info where status = 100 group by student_id分组相加

这是一条 SQL 查询语句，它的作用是从表 dwd_ms_student_attendance_info 中选择学生ID和状态值为100的出勤记录数量，并按照学生ID分组相加。

具体的 SQL 语句如下所示：

SELECT student_id, COUNT(status) 
FROM dwd_ms_student_attendance_info 
WHERE status = 100 
GROUP BY student_id;

这条语句会返回一个表格，其中包含两列：学生ID和状态值为100的出勤记录数量。每个学生ID对应一个计数值，表示该学生出勤状态为100的记录数量。通过 GROUP BY 子句将结果按学生ID进行分组，并使用 COUNT 聚合函数计算每个分组中状态值为100的记录数量。

相关问题

select count(distinct offer_name),month_id from (select count(distinct offer_name), month_id from dwd_sum_zhuliuxxsc where month_id =to_char(month_id+1,yyyymm) group by month_id)语法修正一下

你的 SQL 查询语句已经很接近正确的语法了，只需要将子查询的结果作为一个临时表，再在外层查询中进行聚合即可。可以使用以下语句：

SELECT COUNT(DISTINCT offer_name), month_id 
FROM (
  SELECT COUNT(DISTINCT offer_name), month_id 
  FROM dwd_sum_zhuliuxxsc 
  WHERE month_id = TO_CHAR(month_id+1, 'yyyymm') 
  GROUP BY month_id
) tmp
GROUP BY month_id;

这样就可以得到按月份统计的不同 offer_name 数量了。希望对你有所帮助。

优化这段sql:SELECT `msisdn`, `relationship_account` AS relationshipAccount, '' AS relationshipName, IFNULL(SUM( `dual_time` ), 0) AS totalTime, IFNULL(COUNT(1), 0) AS totalCall, IFNULL(SUM(`local_action` = (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '07')), 0) AS active, IFNULL(SUM(`local_action` = (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '08')), 0) AS passive, IFNULL(SUM(`call_status` = (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '09')), 0) AS success, IFNULL(SUM(`call_status` = (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '10')), 0) AS fail, FROM_UNIXTIME(MIN(UNIX_TIMESTAMP(`start_time`))) AS onceCallTime, FROM_UNIXTIME(MAX(UNIX_TIMESTAMP(`start_time`))) AS endCallTime FROM `dwd_t_call_record` WHERE `person_no` = '653129200105072624' GROUP BY `relationship_account` ORDER BY `start_time` ASC

可以优化的地方有：

1. 子查询可以改为使用 JOIN，例如：

   SELECT status_description
   FROM dwd_t_code_value
   WHERE storage_status = '07'

改为：

   SELECT t2.status_description
   FROM (
       SELECT status_description, storage_status
       FROM dwd_t_code_value
   ) t2
   WHERE t2.storage_status = '07'

2. 使用 EXISTS 或者 IN 替换掉子查询，例如：

   IFNULL(SUM(`local_action` = (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '07')), 0) AS active

改为：

   IFNULL(SUM(`local_action` IN (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '07')), 0) AS active

或者：

   IFNULL(SUM(EXISTS (SELECT 1 FROM dwd_t_code_value WHERE storage_status = '07' AND status_description = `local_action`)), 0) AS active

3. 可以考虑将 FROM_UNIXTIME 和 UNIX_TIMESTAMP 改为 DATE_FORMAT 和 STR_TO_DATE，以避免频繁的数据类型转换。

综上所述，优化后的 SQL 可能类似于以下内容：

SELECT
    `msisdn`,
    `relationship_account` AS relationshipAccount,
    '' AS relationshipName,
    IFNULL(SUM(`dual_time`), 0) AS totalTime,
    IFNULL(COUNT(1), 0) AS totalCall,
    IFNULL(SUM(`local_action` IN (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '07')), 0) AS active,
    IFNULL(SUM(`local_action` IN (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '08')), 0) AS passive,
    IFNULL(SUM(`call_status` IN (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '09')), 0) AS success,
    IFNULL(SUM(`call_status` IN (SELECT status_description FROM dwd_t_code_value WHERE storage_status = '10')), 0) AS fail,
    DATE_FORMAT(MIN(STR_TO_DATE(`start_time`, '%Y-%m-%d %H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS onceCallTime,
    DATE_FORMAT(MAX(STR_TO_DATE(`start_time`, '%Y-%m-%d %H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS endCallTime
FROM
    `dwd_t_call_record`
WHERE
    `person_no` = '653129200105072624'
GROUP BY
    `relationship_account`
ORDER BY
    `start_time` ASC