mysql内部联接子表，主表列的值用逗号分隔

z9smfwbn 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(373)

我想将最后插入的逗号分隔值与products表中的daily\u discounts.discount\u product table limit 1中的值联接起来。有可能在一个查询中实现这一点吗？
尝试：

SELECT * FROM `daily_discounts` INNER JOIN products WHERE FIND_IN_SET(products.product_id , daily_discounts.discount_products)

SELECT * FROM `daily_discounts` INNER JOIN products ON FIND_IN_SET(products.product_id , daily_discounts.discount_products) LIMIT 1

表日折扣：

+-------------+---------------------+
| discount_id | discount_products   |
+-------------+---------------------+
| 1           | 960,310,165,702     |
+-------------+---------------------+
| 2           | 231,822,379,420     |
+-------------+---------------------+
| 3           | 518,56,803,858      |
+-------------+---------------------+

需要表格产品结果：

+------------+--------------------+
| product_id | product_sale_price |
+------------+--------------------+
| 518        | 10.25              |
+------------+--------------------+
| 56         | 11.24              |
+------------+--------------------+
| 803        | 5.55               |
+------------+--------------------+
| 858        | 13.52              |
+------------+--------------------+

我使用了两个查询，以便首先在“每日折扣”表中选择最后插入的记录：

select `discount_products` from `daily_discounts` order by `discount_id` desc limit 1

其次，我选择products表

select `product_id`, `product_sale_price from `products` where `product_id` in (518, 56, 803, 858) order by `product_id` desc)

sql mysql

来源：https://stackoverflow.com/questions/62916750/mysql-inner-join-sub-table-with-comma-separated-values-from-main-table-column

2条答案

按热度按时间

p1tboqfb1#

您可以尝试使用行号（）

select * from
(
SELECT *,row_number() over(order by discount_id desc) as rn
FROM `daily_discounts` 
)A
INNER JOIN products WHERE FIND_IN_SET(products.product_id,A.discount_products) and rn=1

赞(0）回复(0）举报 2021-07-26

v8wbuo2f2#

如果“latest”行是折扣id值最高的行，则假定折扣id是唯一标识符（或主键）

SELECT p.product_id
     , p.product_sale_price
  FROM ( -- inline view to get identifier of latest row 
         SELECT MAX(l.discount_id) AS latest_discount_id 
           FROM `daily_discounts` l
       ) m
  JOIN `daily_discounts` d
    ON d.discount_id = m.latest_discount_id
  JOIN `products` p
    ON FIND_IN_SET(p.id,d.discount_products)
 ORDER
    BY FIND_IN_SET(p.id,d.discount_products)

如果有其他列或表达式需要对行进行排序，为了确定哪些行符合条件，哪些是最新的，我们可以修改内联视图查询

SELECT p.product_id
     , p.product_sale_price
  FROM ( -- inline view to get identifier of latest row 
         SELECT l.discount_id AS latest_discount_id 
           FROM `daily_discounts` l
          WHERE ... 
          ORDER BY ...
          LIMIT 1
       ) m
  JOIN `daily_discounts` d
    ON d.discount_id = m.latest_discount_id
  JOIN `products` p
    ON FIND_IN_SET(p.id,d.discount_products)
 ORDER
    BY FIND_IN_SET(p.id,d.discount_products)

赞(0）回复(0）举报 2021-07-26

我来回答

mysql内部联接子表，主表列的值用逗号分隔

2条答案

相关问题

热门标签

最新问答