search

Home  >  Q&A  >  body text

UNION cannot combine two results with the same column

Enter image description here I tried to combine these two queries in the same display result, but the Mysql system keeps saying that UNION cannot be at this location. If union doesn't work, how can I combine these two queries?

P粉647504283P粉647504283231 days ago496

reply all(1)I'll reply

  • P粉329425839

    P粉3294258392024-04-03 11:58:44

    https://dev.mysql.com/doc/refman /8.0/en/union.html said:

    In your case it would look like this:

    (select customer_id, points, state from customers where state = 'CA' order by points desc limit 3)
    union
    (select customer_id, points, state from customers where state = 'FL' order by points desc limit 3)

    You may also want to knowWindow functions:

    select customer_id, points, state
    from (
      select customer_id, points, state, 
        row_number() over (partition by state order by points desc) as rownum
      from customers where state in ('CA','FL')
    ) as t
    where rownum <= 3

    reply
    0
  • Cancelreply