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

如果你想将每个学生的考勤次数（即每个学生的 status=100 的记录数）添加到查询结果中，可以使用 COUNT(*) 函数，如下所示：

SELECT student_id, COUNT(status), COUNT(*) as attendance_count
FROM dwd_ms_student_attendance_info
WHERE status = 100
GROUP BY student_id

这将为每个学生返回两个列：student_id 和 status=100 记录的计数。同时，使用 COUNT(*) 函数将为每个学生返回考勤次数的计数，该计数不仅限于 status=100 记录。您可以将其命名为 attendance_count，以使其更易于理解。

相关问题

优化这段SQL：SELECT CONCAT( HOUR ( mail_send_time ) DIV 1 * 1, ':00-', HOUR ( mail_send_time ) DIV 1 * 1, ':59' ) AS timeInterval, IFNULL( COUNT( * ), 0 ) AS total, IFNULL( SUM( contact_account_type in (SELECT code_description FROM dwd_t_code_value WHERE code_value = '01') ), 0 ) AS wxTotal, IFNULL( SUM( contact_account_type IN (SELECT code_description FROM dwd_t_code_value WHERE code_value = '02') ), 0 ) AS qqTotal, IFNULL( SUM( local_action = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '05') AND contact_account_type = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '01') ), 0 ) AS wxSend, IFNULL( SUM( local_action = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '06') AND contact_account_type = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '01')), 0 ) AS wxReceive , IFNULL( SUM( local_action = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '05') AND contact_account_type = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '02')), 0 ) AS qqSend, IFNULL( SUM( local_action = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '06') AND contact_account_type = (SELECT code_description FROM dwd_t_code_value WHERE code_value = '02')), 0 ) AS qqReceive FROM dwd_t_friend_talk, dwd_t_code_value WHERE person_no = '653129200105072624' AND contact_account_type IN ( '微信', 'QQ' ) AND friend_account <> '' AND contact_account_type <> '' AND mail_send_time IS NOT NULL AND mail_send_time <> '' GROUP BY HOUR ( mail_send_time ) DIV 1

可以尝试以下优化措施：
1. 将子查询转化为 JOIN 操作：将子查询(SELECT code_description FROM dwd_t_code_value WHERE code_value = '01') 转化为 JOIN dwd_t_code_value on dwd_t_code_value.code_value = '01'，避免子查询的重复执行。
2. 将 IFNULL 函数替换为 COALESCE 函数，COALESCE 函数可以同时处理多个参数，效率更高。
3. 使用具有更好性能的索引：可以考虑在 dwd_t_friend_talk 表的 person_no 和 contact_account_type 字段上建立联合索引，提高查询效率。
4. 避免在 WHERE 子句中使用函数：可以将 HOUR(mail_send_time) 的计算移到 SELECT 子句中，避免在 WHERE 子句中使用函数，提高查询效率。

优化后的 SQL 语句如下：

SELECT
CONCAT(HOUR(mail_send_time) DIV 1 * 1, ':00-', HOUR(mail_send_time) DIV 1 * 1, ':59') AS timeInterval,
COALESCE(COUNT(*), 0) AS total,
COALESCE(SUM(contact_account_type = '微信'), 0) AS wxTotal,
COALESCE(SUM(contact_account_type = 'QQ'), 0) AS qqTotal,
COALESCE(SUM(local_action = '05' AND contact_account_type = '微信'), 0) AS wxSend,
COALESCE(SUM(local_action = '06' AND contact_account_type = '微信'), 0) AS wxReceive,
COALESCE(SUM(local_action = '05' AND contact_account_type = 'QQ'), 0) AS qqSend,
COALESCE(SUM(local_action = '06' AND contact_account_type = 'QQ'), 0) AS qqReceive
FROM
dwd_t_friend_talk
JOIN dwd_t_code_value ON dwd_t_code_value.code_value = '01' OR dwd_t_code_value.code_value = '02'
WHERE
person_no = '653129200105072624'
AND contact_account_type IN ('微信', 'QQ')
AND friend_account <> ''
AND contact_account_type <> ''
AND mail_send_time IS NOT NULL
AND mail_send_time <> ''
GROUP BY
HOUR(mail_send_time) DIV 1

优化这段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