mysql - SQL 连接允许给定列的多行

Question

我有以下查询：

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

给我以下结果：

+---------------------+----------------------------------------+
|      timestamp      |              productHash               |
+---------------------+----------------------------------------+
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ |
+---------------------+----------------------------------------+

我必须将它与匹配的materialAppliers位置加入：productHash

+----------------------------------------+------------------------------------------+-------+
|              productHash               |               applierHash                | asset |
+----------------------------------------+------------------------------------------+-------+
| cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2E1ZTExLjE3NDA3NjYx | val1  |
| cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2Q1MmE0LjI3NTA4Nzcx | val2  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NDdmNWQ0LjU1MzQ1NDg5 | val3  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NmY2MTA0LjQyOTkxNzY5 | val4  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5OTZlNGU3LjcxNTI1MDY1 | val5  |
+----------------------------------------+------------------------------------------+-------+

这样每个materialApplier都有自己的行，由先前的查询组成timestamp并取自先前的查询；取自：applierHash_assetmaterialAppliers

+---------------------+----------------------------------------+------------------------------------------+-------+
|      timestamp      |              productHash               |               applierHash                | asset |
+---------------------+----------------------------------------+------------------------------------------+-------+
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2E1ZTExLjE3NDA3NjYx | val1  |
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2Q1MmE0LjI3NTA4Nzcx | val2  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NDdmNWQ0LjU1MzQ1NDg5 | val3  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NmY2MTA0LjQyOTkxNzY5 | val4  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5OTZlNGU3LjcxNTI1MDY1 | val5  |
+---------------------+----------------------------------------+------------------------------------------+-------+

我怎样才能做到这一点？

标签： mysqlsqllaravel

Answer 1

只需加入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;