以下是mysql 8+的实现方法：

SELECT
    t2.Invoice,
    t2.`lines`,
    ROW_NUMBER() OVER (PARTITION BY t2.Invoice ORDER BY t2.`lines`) line_order,
    DENSE_RANK() OVER (ORDER BY t2.Invoice) ordination
FROM table2 t2
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.Invoice = t2.Invoice AND t1.valid = 'yes');

演示

如果您使用的mysql版本早于8，那么您可能必须使用会话变量。这会导致一个难看的查询。如果您长期需要这样的查询，那么我建议您升级到mysql 8+。
编辑：
我突然意识到，我们可以使用相关子查询来模拟 ROW_NUMBER 以及 DENSE_RANK 要求。以下是在MySQL5.7或更早版本中执行此查询的一种方法：

SELECT
    t2.Invoice,
    t2.detail,
    (SELECT COUNT(*) FROM table2 t
    WHERE t.Invoice = t2.Invoice AND t.detail <= t2.detail) ordination,
    t.dr AS ordinationb
FROM table2 t2
INNER JOIN
(
    SELECT DISTINCT
        t2.Invoice,
        (SELECT COUNT(*)
         FROM (SELECT DISTINCT Invoice FROM table2) t
         WHERE t.Invoice <= t2.Invoice) dr
    FROM table2 t2
) t
    ON t.Invoice = t2.Invoice
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.Invoice = t2.Invoice AND t1.valid = 'yes')
ORDER BY
    t2.Invoice,
    t2.detail;

演示