多表内连接查询报错：统计年度Top10处方药物失败

Hey there! Let's walk through what's causing the error in your query and get it working correctly for your goal.

First, the Core Error: Mismatched SELECT and GROUP BY #

The error you're seeing is because your SELECT clause includes ENCOUNTER.OBSDATE, but this column isn't part of your GROUP BY clause and isn't wrapped in an aggregate function (like MIN() or MAX()).

Most SQL databases (especially when running in strict mode, like MySQL's ONLY_FULL_GROUP_BY) enforce a rule: any column in your SELECT that isn't aggregated must be listed in GROUP BY. Since you're grouping by MEDICATION_NAME, each group could have multiple OBSDATE values (one for each prescription of that drug in 2011), and the database doesn't know which date to return.

Luckily, for your goal—counting prescription frequency—you don't need OBSDATE in your final results at all. It only needs to be in the WHERE clause to filter records to 2011.

Your Join Logic is Actually Correct! #

Good news: the way you're joining the three tables makes perfect sense.

• ENC_MEDICATIONS acts as the junction table between MEDICATIONS (drug details) and ENCOUNTER (visit/prescription dates), which is exactly how you should link these tables to track which drugs were prescribed during which encounters. No issues here.

Missing "Top 10" Filter #

Your query sorts results by count descending, but it doesn't limit the output to the top 10 drugs. You'll need to add a database-specific clause for this.

Corrected Query (Example for MySQL) #

Here's a revised version that fixes all the issues:

SELECT 
  COUNT(EM.MED_ID) AS prescription_count,
  M.MEDICATION_NAME
FROM MEDICATIONS M
INNER JOIN ENC_MEDICATIONS EM 
  ON EM.MED_ID = M.MED_ID
INNER JOIN ENCOUNTER E 
  ON EM.ENC_ID = E.ENC_ID
WHERE E.OBSDATE BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY M.MEDICATION_NAME
ORDER BY prescription_count DESC
LIMIT 10;

Key Adjustments Made: #

• Removed ENCOUNTER.OBSDATE from the SELECT clause (it's only needed for filtering)
• Added table aliases (M, EM, E) to make the query cleaner and easier to read
• Renamed the count column to prescription_count for clarity
• Used the SQL-standard date format (YYYY-MM-DD) to avoid date parsing issues across different databases
• Added LIMIT 10 to get only the top 10 most prescribed drugs (adjust this clause if you're using a different database: use TOP 10 in SQL Server, FETCH FIRST 10 ROWS ONLY in Oracle)

If You Need Date Details in Results #

If you later want to include date-related info (like the first or last time a drug was prescribed in 2011), you can add aggregated date columns to the SELECT:

SELECT 
  COUNT(EM.MED_ID) AS prescription_count,
  M.MEDICATION_NAME,
  MIN(E.OBSDATE) AS first_prescription_date,
  MAX(E.OBSDATE) AS last_prescription_date
FROM MEDICATIONS M
INNER JOIN ENC_MEDICATIONS EM 
  ON EM.MED_ID = M.MED_ID
INNER JOIN ENCOUNTER E 
  ON EM.ENC_ID = E.ENC_ID
WHERE E.OBSDATE BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY M.MEDICATION_NAME
ORDER BY prescription_count DESC
LIMIT 10;

This works because MIN() and MAX() are aggregate functions that return a single value per group, so they're allowed in the SELECT without being in GROUP BY.

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