问题描述:

I'm trying to create a random name generator query using a set of test data from my database.

The field name stores a customers full name, I however would like the query to grab a random first name from the name field and a random last name from the name field.

Query:

select concat(first_name, ' ', last_name) from

((select lcase(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1)) as first_name

from customers

where SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mrs'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mrs'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mr'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%.%'

and length(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1)) > 1

order by rand()

limit 10) as first_name_tbl,

(select lcase(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1)) as last_name

from customers

where SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mrs'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mrs'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mr'

and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%.%'

and length(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1)) > 1

order by rand()

limit 10) as last_name_tbl);

The problem with my query is that it returns duplicate names and not the right number of records.

Current results:

100 rows in set

| sabrina mole |

| daniel mole |

| helen mole |

| jenny mole |

| caroline mole |

| catherine mole |

| julia mole |

| carmella mole |

| mark mole |

| catharine mole |

| sabrina salgado |

| daniel salgado |

| helen salgado |

| jenny salgado |

| caroline salgado |

| catherine salgado |

| julia salgado |

| carmella salgado |

.....

Desired results

10 rows in set

| sabrina mole |

| daniel salgado |

| helen oinn |

| jenny hird |

| caroline thompson |

| catherine helena |

| julia taylor |

| carmella spectrum |

| mark storrie |

| catharine pat |

网友答案:

The problem is you are creating a cross join with two 10 row tables.

So 10 x 10 = 100 rows.

You need use a session variable rowid to each table
See rowid on MySql

( SELECT @rowidFirst:[email protected]+1 as rowid, first_name_tbl.*
  FROM  
    ( SELECT .... ) as first_name_tbl
) as firstWithRowID

( SELECT @rowidLast:[email protected]+1 as rowid, last_name_tbl.*
  FROM  
    ( SELECT .... ) as last_name_tbl
) as lastWithRowID

then join by row_id

SELECT *
FROM firstWithRowID, lastWithRowID
WHERE firstWithRowID.rowid = lastWithRowID.rowid
相关阅读:
Top