多表INNER JOIN查询需求协助：含'/'编码的字段提取与统计

Let's tweak your existing query to include the PM_ANO_SP.L_ANO_SP field and filter for PM_ANO_L.L_ANO values containing a /. Here's the updated code:

SELECT 
    COUNT(RF_INTRN),
    TRIM(pm_st.c_st),
    TRIM(pm_ano_l.L_ANO),
    pm_ano_sp.L_ANO_SP
FROM PM_FLX_ENTE 
INNER JOIN PM_ST ON PM_FLX_ENTE.C_ST = PM_ST.C_ST 
INNER JOIN PM_ANO_L ON PM_FLX_ENTE.C_ANO = pm_ano_l.c_ano 
INNER JOIN PM_ANO_SP ON PM_FLX_ENTE.C_ANO = pm_ano_sp.c_ano
WHERE 
    pm_flx_ente.C_ANO <> '0000' 
    AND pm_ano_l.c_lang = 'FR'
    AND pm_ano_l.L_ANO LIKE '%/%'
GROUP BY 
    TRIM(pm_st.c_st),
    TRIM(pm_ano_l.L_ANO),
    pm_ano_sp.L_ANO_SP
ORDER BY COUNT(RF_INTRN) DESC;

Key Changes Explained: #

• Added JOIN for PM_ANO_SP: We linked this table using the shared C_ANO field (adjust the join condition if your schema uses a different key for connecting these tables).
• Included L_ANO_SP in SELECT: Added the required field to your output columns.
• Added / Filter: The LIKE '%/%' condition ensures we only keep rows where PM_ANO_L.L_ANO contains a forward slash.
• Updated GROUP BY: Since we added a non-aggregated field (L_ANO_SP) to the SELECT clause, we had to include it in GROUP BY to comply with SQL standards.

Quick Notes: #

• If you need to retain records that don't have a matching entry in PM_ANO_SP, replace INNER JOIN with LEFT JOIN for that table—this will return NULL for L_ANO_SP where no match exists.
• Double-check the join key between PM_FLX_ENTE and PM_ANO_SP to make sure it aligns with your actual database schema.

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