sql - Oracle SQL - Assign queue of customers to list of Employees

Question

I need to distribute list of employees to customers.

For example:

Table 1: List of Employees: A, B & C

Table 2: List of Customers: 1, 2, 3, 4, 5, 6, 7, 8, 9

The needed result:

|------------|------------|
| Customers  |  Employees |
|------------|------------|
|   1        |     A      |
|   2        |     B      |
|   3        |     C      |
|   4        |     A      |
|   5        |     B      |
|   6        |     C      |
|   7        |     A      |
|   8        |     B      |
|   9        |     C      |
|------------|------------|

标签： sqloracle

Answer 1

You can use ROW_NUMBER() to assign a number on the fly to the employees, and the MOD() to do the rolling join. For example:

select
  c.id,
  e.name
from (
  select t.*,
    row_number() over(order by name) as rn
  from employees t
) e
join customers c on e.rn = 
  mod(rn, (select count(*) from customers)) + 1