sql嵌套语句问题，产品计数

8xiog9wr 于 2021-06-25 发布在 Mysql

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

我正在学习sql嵌套语句，但遇到了一个问题。我想做的是使用图书ID获取上个月图书的总销量，并将其与本月的销量进行比较。所有的购买都记录在一个book\u download\u log表中，表的结构如下

Timestamp    Book ID   Price    
May 1st      10        100
May 2nd      20        200
May 3rd      30        300

这是我当前的查询

SELECT b.book_id, CONCAT(FORMAT(((this_month/last_month)*100)-100,2),'%') AS Diff, this_month, last_month
FROM 
book_download_log b
JOIN
    (SELECT count(*) AS last_month FROM
        book_download_log b
            WHERE b.book_price > 0 AND
            date(b.timestamp) BETWEEN '2018-04-01' AND '2018-04-08') t1
 JOIN
    (SELECT count(*) AS this_month FROM
        book_download_log b
            WHERE b.book_price > 0 AND
            date(b.timestamp) BETWEEN '2018-05-01' AND '2018-05-08') t2
GROUP BY b.book_id
ORDER BY Diff Desc

我成功地做的是提取这段时间内购买的所有书籍的总计数，而不是单个书籍购买的计数。下面是我现在的结果

Book ID    Diff     This Month     Last Month
1          17.6%    652            577
69         17.6%    652            577
102        17.6%    652            577

我需要编辑什么来显示这两个时期的单个图书销量？

mysql

来源：https://stackoverflow.com/questions/50234806/sql-nested-statement-issue-with-count-of-products

2条答案

按热度按时间

wnavrhmk1#

您可以选择：

SELECT
b.book_id as id,
SUM(IF(MONTH(b.timestamp) = MONTH(CURDATE()), b.book_price, 0)) as thisMonth,
SUM(IF(MONTH(b.timestamp) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), b.book_price, 0)) as lastMonth,
CONCAT(SUM(IF(MONTH(b.timestamp) = MONTH(CURDATE()), b.book_price, 0)) - SUM(IF(MONTH(b.timestamp) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), b.book_price, 0)), '%') as diff
FROM book_download_log b
WHERE DATE_FORMAT(b.timestamp, '%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AND DATE_FORMAT(CURDATE(), '%Y-%m-31')
GROUP BY b.book_id

这也适用于跨年（例如，你在一月，上个月是另一年），如果你在数据库中有当前日期之后的数据（例如，你在一月，并且你在数据库中存储了同一年二月的数据，则可能发生这种情况）
请参见此处的sql fiddle来测试它：http://sqlfiddle.com/#!9月4日

赞(0）回复(0）举报 2021-06-25

dldeef672#

首先，你要按月份分组购买。我选择了摘录（从“2017-06-15”开始的月份）。然后使用sum（），而不是count（）获取每个月的所有购买量。所以你应该有这样的东西：

Select EXTRACT(MONTH FROM "2017-06-15") as Month, Book_id, sum(price) from 
book_download_log group by Book_id, Month

然后提取上个月和本月。

SELECT b.book_id, CONCAT(FORMAT(((this_month/last_month)*100)-100,2),'%') 
    AS Diff
    FROM 
    (Select EXTRACT(MONTH FROM "2017-06-15") as Month, Book_id, sum(price) from 
     book_download_log group by Book_id, Month)
    join
   Select EXTRACT(MONTH FROM "2017-04-15") as Last_Month, Book_id, sum(price) from 
     book_download_log download group by Book_id, Last_Month
    on download.Book_id = book_download_log.Book_id

赞(0）回复(0）举报 2021-06-25

我来回答

sql嵌套语句问题，产品计数

2条答案

相关问题

热门标签

最新问答