加入吧 materialAppliers 你的问题。

SELECT *
       FROM
(
select transactions.timestamp, products.productHash
from transactions
inner join transactionItems on transactionItems.transactionHash = tra nsactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash
) x
LEFT JOIN materialAppliers
          ON materialAppliers.productHash = x.productHash;

但是列列表中有一列，没有 GROUP BY 你没有在上面应用任何聚合函数。尽管mysql在较低的版本或某些设置中接受这一点，但这并不是一件好事。我建议你把它修好。使用例如。 max() 获取最新的时间戳。

SELECT *
       FROM
(
select max(transactions.timestamp), products.productHash
from transactions
inner join transactionItems on transactionItems.transactionHash = tra nsactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash
) x
LEFT JOIN materialAppliers
          ON materialAppliers.productHash = x.productHash;

我将尝试对整个第一个查询使用别名，然后将最后一个连接添加到materialappliers表中

Select * from (select transactions.timestamp, products.productHash
    from transactions
    inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
    inner join products on products.productHash = transactionItems.productHash
    where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
        and (products.type = 'applier' and products.gender = 'f')
        and products.additionalData->'$."skintone"' in ('-1', -1)
        and exists
        (
            select 1 from materialAppliers
            where materialAppliers.productHash = products.productHash
                and materialAppliers.applierType = 'skin'
        )
    group by transactionItems.productHash) as x

inner join materialAppliers as ma on x.producthash = ma.productHash

看起来很直截了当：使用materialappliers添加一个内部连接，然后再向select添加两个选定值。。。我错过了什么吗？

select transactions.timestamp, products.productHash, ma.applierHash, ma.asset
from transactions
inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
inner join materialAppliers ma on ma.productHash = products.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash

你应该能够摆脱存在，并简单地添加 and ma.applierType = 'skin' 相反。我之所以说应该，是因为我不完全理解appliertype和producthash之间的关系，但看起来应该可以。
例如：

select transactions.timestamp, products.productHash, ma.applierHash, ma.asset
from transactions
inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
inner join materialAppliers ma on ma.productHash = products.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
  and products.type = 'applier' 
  and products.gender = 'f'
  and products.additionalData->'$."skintone"' in ('-1', -1)
  and ma.applierType = 'skin'