MySQL子查询从一个表中获取ID列表以查询第二个表 [英] MySQL subquery to get a list of IDs from one table to query a second table

问题描述

我有两张表，一张是products，一张是salesRecords

I have two tables, one is products and one salesRecords

我正在使用这个查询:

SELECT 
    DAY(FROM_UNIXTIME(saleDate)) as day,
    MONTH(FROM_UNIXTIME(saleDate)) as mnth,
    YEAR(FROM_UNIXTIME(saleDate)) as yr,   
    COUNT(id) as invCount, SUM(quantity) as qty 
 FROM salesRecords WHERE itemNo IN 
      (SELECT GROUP_CONCAT(convert(id, CHAR(8))) as ids FROM products WHERE brand =100 GROUP by brand) 
    GROUP BY 
       mnth, yr 
    ORDER BY saleDate

产品表包含我需要了解的关于产品的所有信息，而 salesRecords 包含详细信息，例如销售日期、销售数量、给出的折扣等，所以我在这里尝试实现的是所有产品的销售列表品牌 id 100 按月和年来自名为saleDate"的 Unix 时间字段

The products table contains all I need to know about a product, and salesRecords contains details such as the saleDate, quantity sold, discounts given etc, so what I am trying to achieve here is a list of sales for all products with the brand id 100 by month and year from a Unix Time field called "saleDate"

它有效，但速度很慢.

谁能建议一种更快的方法?

Can anyone advise a faster way of doing this?

如果我手动将 ID 列表插入查询中，它似乎运行得更快，那么我应该运行两个查询吗?

If I manually plug the list of IDs into the query it seems to work faster, so should I run two queries?

推荐答案

我用单个内部联接重写了您的查询.试试看，然后回来提供新闻:

I have rewrote your query with a single inner join. Try it and come back with news:

SELECT 
    DAY(FROM_UNIXTIME(saleDate)) as day,
    MONTH(FROM_UNIXTIME(saleDate)) as mnth,
    YEAR(FROM_UNIXTIME(saleDate)) as yr,   
    COUNT(id) as invCount, 
    SUM(quantity) as qty 
 FROM salesRecords s inner join 
      products on s.itemNo = p.id 
 WHERE 
      p.brand =100
 GROUP BY 
       `day`, mnth, yr 
 ORDER BY 
       saleDate

另外，为了避免where子句，你可以在on操作中加入限制:

Also, to avoid where clause, you can put restrinction in on operation:

    ...
    SUM(quantity) as qty 
 FROM salesRecords s inner join 
      products on p.brand =100 and s.itemNo = p.id 
 GROUP BY 
    ...

这篇关于MySQL子查询从一个表中获取ID列表以查询第二个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！