如何将预订信息查询结果合并为单行输出而非两行？

Hey there! Let's figure out why you're getting two rows instead of one for that same-day booking scenario. This is a common issue when queries aren't set up to aggregate multiple bookings into a single row per date. Here's how to fix and optimize it:

1. Your Query Isn't Aggregating Multiple Bookings #

If you're aiming for one row per date that summarizes all bookings that day, your current query is probably returning individual booking rows instead of grouping them. For example, a basic query like this will always return one row per booking:

SELECT booking_date, user_name, room_number
FROM bookings
WHERE booking_date = '2018-03-12';

To combine these into a single row, use an aggregate function to concatenate the booking details. Depending on your SQL dialect:

• PostgreSQL: Use STRING_AGG

  SELECT 
    booking_date,
    STRING_AGG(CONCAT(user_name, ' (Room ', room_number, ')'), ', ') AS daily_bookings
  FROM bookings
  WHERE booking_date = '2018-03-12'
  GROUP BY booking_date;
  

• MySQL: Use GROUP_CONCAT

  SELECT 
    booking_date,
    GROUP_CONCAT(CONCAT(user_name, ' (Room ', room_number, ')') SEPARATOR ', ') AS daily_bookings
  FROM bookings
  WHERE booking_date = '2018-03-12'
  GROUP BY booking_date;
  

This will output a single row for 2018-03-12 with both User A (Room 2) and User B (Room 16) listed in one column.

2. Unnecessary Joins Are Creating Duplicates #

If your query joins other tables (like a rooms or users table) without proper grouping, it might be generating duplicate rows. Double-check if those joins are actually needed for your output. If they are, make sure you're aggregating the relevant fields instead of returning each joined row. For example, if you join to get room names, include that in your concatenated string instead of leaving it as a separate column.

3. Your GROUP BY Clause Is Too Granular #

If you're grouping by booking_date but also including non-aggregated columns like user_name or room_number in your SELECT, the database will return one row per unique combination of those columns. Remove any non-aggregated columns from the SELECT (or wrap them in an aggregate function) to get a single row per date.

• Keep it focused: Only select the columns you actually need—don't clutter the query with unused fields from joined tables.
• Leverage built-in functions: SQL's aggregate string functions are optimized and way cleaner than writing custom logic to combine rows.
• Consider app-layer formatting: If you need a more structured output (like a list of objects), it's often better to fetch individual booking rows and format them in your application code rather than trying to do it all in SQL. This keeps your query simple and maintainable.

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