mysql - 如何按同一张表中存在的行进行过滤和排序？

Question

我有一个包含 2 列“id”和“ink_number”的简单表格，我想创建另一个名为“nk_exists”的列，该列的值为 1 或 0，具体取决于是否有类似的行但是一个“NK”开头。并能够按“nk_exits”排序。

例如，如果有两行“123”和“NK123”，那么对于“123”行，nk_exists 为真，因为存在以 NK 开头的第 123 行。

这是我的原始表“inkasso”

+-------+--------------+
| id  1 |  ink_number  |
+-------+--------------+
|     1 | 538032S      |
|     2 | NK538032S    |
|     3 | 114702A      |
|     4 | 159631D      |
|     5 | NK9761926001 |
|     6 | 9761926001   |
|     7 | 29-00002411L |
|     8 | 42032502V    |
|     9 | NK42032502V  |
|    10 | 454339KDB    |
+-------+--------------+

我希望最终结果看起来像这样

+-------+--------------+-----------+
| id  1 |  ink_number  | nk_exists |
+-------+--------------+-----------+
|     1 | 538032S      |         1 |
|     2 | NK538032S    |         0 |
|     3 | 114702A      |         0 |
|     4 | 159631D      |         0 |
|     5 | NK9761926001 |         0 |
|     6 | 9761926001   |         1 |
|     7 | 29-00002411L |         0 |
|     8 | 42032502V    |         1 |
|     9 | NK42032502V  |         0 |
|    10 | 454339KDB    |         0 |
+-------+--------------+-----------+

我的查询出现语法错误

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax

select
    inkasso.id,
    inkasso.ink_number,
    jointable.nk_exists
from inkasso
left join (
    select
        case
            when exists 1
            when not exists 0
        end as nk_exists
    from inkasso
    where exists(
        select
            1
        from inkasso ink
        where left(ink_number, 2) = 'NK' and inkasso.id = ink.id
    ) as subjoin on 
) as jointable on inkasso
WHERE jointable.nk_exists = 1
ORDER BY jointable.nk_exists

Answer 1

SELECT *,
  CONCAT('NK', ink_number) IN (SELECT ink_number FROM inkasso) AS nk_exists
FROM inkasso;

Fiddle