如何用GROUP BY c.*替代手动罗列200+字段实现SQL分组查询？

Great question—dealing with super wide tables and GROUP BY clauses is such a hassle when you don’t want to type out every single column name. Let’s break down what works (and what doesn’t) across common databases, plus a better approach you should use:

What’s the Deal with GROUP BY c.*? #

Whether GROUP BY c.* is allowed depends entirely on your database system:

• MySQL: If you disable the ONLY_FULL_GROUP_BY SQL mode (not recommended for production, since it can lead to unpredictable results), GROUP BY c.* will work. But if CLIENT_ID is the primary key of the client table, you don’t even need that—just GROUP BY c.CLIENT_ID will let you select all c.* columns, because MySQL recognizes that the primary key uniquely determines all other columns in the row.
• PostgreSQL: GROUP BY c.* is technically allowed, but only if every column in c is functionally dependent on the grouping key. Again, if CLIENT_ID is the primary key, GROUP BY c.CLIENT_ID is equivalent and way cleaner. PostgreSQL will automatically allow selecting all other client columns without listing them.
• SQL Server: GROUP BY c.* isn’t supported directly. But like the others, if CLIENT_ID is the primary key, you can GROUP BY c.CLIENT_ID and still select all c.* columns—SQL Server respects functional dependencies in higher compatibility levels (100+).

The Best Approach (Avoid GROUP BY c.* Altogether) #

Instead of relying on database-specific behavior with GROUP BY c.*, use the primary key of the client table for grouping. This is standard SQL, works across all databases, and is way more efficient. Here’s how to adjust your query:

SELECT c.*, MAX(TLO.BILL_DATE) AS latest_bill_date
FROM client c
LEFT OUTER JOIN TLORDER TLO 
  ON TLO.CUSTOMER = c.CLIENT_ID 
  OR TLO.ORIGIN = c.CLIENT_ID 
  OR TLO.DESTINATION = c.CLIENT_ID
GROUP BY c.CLIENT_ID;

If CLIENT_ID Isn’t the Primary Key? #

If for some reason CLIENT_ID doesn’t uniquely identify a client row, use a window function instead of GROUP BY to avoid listing all columns:

SELECT DISTINCT 
  c.*, 
  MAX(TLO.BILL_DATE) OVER (PARTITION BY c.CLIENT_ID) AS latest_bill_date
FROM client c
LEFT OUTER JOIN TLORDER TLO 
  ON TLO.CUSTOMER = c.CLIENT_ID 
  OR TLO.ORIGIN = c.CLIENT_ID 
  OR TLO.DESTINATION = c.CLIENT_ID;

The PARTITION BY clause calculates the max bill date per CLIENT_ID, and DISTINCT ensures you get one row per client (since the join might create duplicates).

Final Takeaway #

GROUP BY c.* is either unsupported, not recommended, or unnecessary depending on your database. Stick to grouping by the primary key (or a unique identifier) for a clean, portable solution that avoids typing 200+ column names.

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