mysql - 逐行合并两个子查询

Question

我正在尝试使用我数据库中的一组测试数据创建一个随机名称生成器查询。

name 字段存储客户的全名，但是我希望查询从 name 字段中获取随机名字，并从 name 字段。

查询:

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);

我的查询的问题是它返回重复的名称而不是正确的记录数。

当前结果:

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                   |
..... 

期望的结果

10 rows in set

| sabrina mole                       |
| daniel salgado                     |
| helen oinn                         |
| jenny hird                         |
| caroline thompson                  |
| catherine helena                   |
| julia taylor                       |
| carmella spectrum                  |
| mark storrie                       |
| catharine pat                      |

Answer 1

问题是您正在创建一个包含两个 10 行表的交叉联接。

所以 10 x 10 = 100 行。

您需要为每个表使用一个 session 变量rowid
See rowid on MySql

( SELECT @rowidFirst:=@rowidFirst+1 as rowid, first_name_tbl.*
  FROM  
    ( SELECT .... ) as first_name_tbl
) as firstWithRowID

( SELECT @rowidLast:=@rowidLast+1 as rowid, last_name_tbl.*
  FROM  
    ( SELECT .... ) as last_name_tbl
) as lastWithRowID

然后通过row_id加入

SELECT *
FROM firstWithRowID, lastWithRowID
WHERE firstWithRowID.rowid = lastWithRowID.rowid