Oracle聚合函数应用：基于employee_1与mobile_1表的查询需求

Got it, let's tackle this problem step by step. First, let's recap your table structures and sample data to make sure we're on the same page:

表结构与样本数据 #

employee_1表 #

mobile_1表 #

Below are several practical aggregation queries tailored to common use cases:

1. 汇总每个手机号的SIM信息与状态 #

This query uses LISTAGG to concatenate all SIM numbers for each mobile, COUNT to tally SIM count, and joins with mobile_1 to get connection status. It also flags if the account has an active SIM (no end date):

SELECT
    e.Mobile_No,
    COALESCE(m.Status, 'Not found in mobile_1') AS connection_status,
    COUNT(e.Sim_No) AS total_sim_count,
    LISTAGG(e.Sim_No, ', ') WITHIN GROUP (ORDER BY e.Start_Date) AS all_sim_numbers,
    MAX(e.Start_Date) AS latest_activation_date,
    CASE 
        WHEN EXISTS (SELECT 1 FROM employee_1 e2 WHERE e2.Mobile_No = e.Mobile_No AND e2.End_Date IS NULL) 
        THEN 'Has active SIM' 
        ELSE 'No active SIM' 
    END AS account_active_status
FROM employee_1 e
LEFT JOIN mobile_1 m ON e.Mobile_No = m.Mobile_No
GROUP BY e.Mobile_No, m.Status
ORDER BY e.Mobile_No;

2. 统计每个手机号的有效SIM（未过期或当前在用） #

If you only want to aggregate SIMs that are currently valid (either no end date or end date is in the future), use this query with a filtered count and list:

SELECT
    e.Mobile_No,
    COALESCE(m.Status, 'Not found in mobile_1') AS connection_status,
    COUNT(e.Sim_No) FILTER (WHERE e.End_Date IS NULL OR SYSDATE BETWEEN e.Start_Date AND e.End_Date) AS active_sim_count,
    LISTAGG(
        CASE WHEN e.End_Date IS NULL OR SYSDATE BETWEEN e.Start_Date AND e.End_Date THEN e.Sim_No END, 
        ', '
    ) WITHIN GROUP (ORDER BY e.Start_Date) AS active_sim_numbers
FROM employee_1 e
LEFT JOIN mobile_1 m ON e.Mobile_No = m.Mobile_No
GROUP BY e.Mobile_No, m.Status
ORDER BY e.Mobile_No;

3. 获取每个手机号的最新SIM记录 #

To get the most recently activated SIM for each mobile number (using ranking alongside aggregation logic):

WITH ranked_sim_records AS (
    SELECT
        e.Mobile_No,
        e.Sim_No,
        e.Start_Date,
        e.End_Date,
        m.Status,
        ROW_NUMBER() OVER (PARTITION BY e.Mobile_No ORDER BY e.Start_Date DESC) AS record_rank
    FROM employee_1 e
    LEFT JOIN mobile_1 m ON e.Mobile_No = m.Mobile_No
)
SELECT
    Mobile_No,
    Status,
    Sim_No AS latest_sim_number,
    Start_Date AS latest_activation_date,
    End_Date
FROM ranked_sim_records
WHERE record_rank = 1;

Each query can be adjusted based on your specific needs—just let me know if you need to tweak the aggregation logic further!

内容的提问来源于stack exchange，提问作者Mayur