mysql日报表查询

Question

下面的查询给出了一家设备租赁公司的项目报告。这是一个超级复杂的查询，几乎需要20秒才能运行。这显然不是获取我正在寻找的数据的正确方法。我从PHP构建这个查询，并添加开始日期02-01-2011和结束日期03-01-2011、产品代码(p_code =1)和产品池(i_pool = 1)。这4条信息被传递给PHP函数并注入到下面的sql中，以返回日历控件所需的报告，该报告显示了有多少项未完成。我的问题是:有没有办法简化或做得更好，或者更高效地运行，使用更好的连接或更好的方式来显示单个日期。

SELECT   DISTINCT reportdays.reportday, count(*)
FROM 
(SELECT '2011-02-01' + INTERVAL a + b DAY reportday
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m 
WHERE '2011-02-01' + INTERVAL a + b DAY  <  '2011-03-01'
ORDER BY a + b) as reportdays
 JOIN rental_inv as line 
    ON DATE(FROM_UNIXTIME(line.ri_delivery_dte)) <= reportdays.reportday
  AND  DATE(FROM_UNIXTIME(line.ri_pickup_dte)) >= reportdays.reportday
 LEFT OUTER JOIN rental_in as rent on line.ri_num = rent.ri_num
 LEFT OUTER JOIN rental_cancels cancelled on rent.ri_num = cancelled.ri_num
 LEFT OUTER JOIN inv inventory on line.i_num = inventory.i_num
 LEFT OUTER JOIN product ON inventory.p_code = product.p_code
 WHERE rent.ri_extend = 0      -- disregard extended rentals
  AND cancelled.ri_num is null -- disregard cancelled rentals
  AND inventory.p_code = 1
  AND inventory.i_pool = 1

 GROUP BY reportdays.reportday

如果有任何其他需要的信息，请让我知道，我会张贴出来。

Answer 1

您可以使用：

SELECT DATE(ri_delivery) as day,
       count(*) as itemsout,
  FROM rental_inv
GROUP BY day;

我不确定你是需要这个还是别的东西。

  SELECT dates.day, count (*)
    FROM rental_inv line
         INNER JOIN (SELECT DATE(ri_delivery_dte) as day FROM rental_inv  
                      WHERE ri_delivery_dte >= '2011/02/01' 
                        AND ri_delivery_dte <= '2011/02/28' 
                     GROUP BY day
                     UNION 
                     SELECT DATE(ri_pickup_dte) as day FROM rental_inv 
                      WHERE ri_pickup_dte >= '2011/02/01' 
                        AND ri_pickup_dte <= '2011/02/28'
                    GROUP BY day) dates
                 ON line.ri_delivery_dte <= dates.day and line.ri_pickup_dte >= dates.day
          LEFT JOIN rental_cancels canc on line.ri_num =  canc.ri_num 
          LEFT JOIN rental_in rent on line.ri_num = rent.ri_num
    WHERE  canc.ri_num is  null
       AND rent.ri_extend = 0
 GROUP BY dates.day

Answer 2

要查找所有天：

SELECT DATE(IFNULL( ri_delivery，ri_pickup)) AS date FROM rental_inv AS dateindex WHERE YEAR-MONTH-1 <= ri_delivery <= LAST_DAY( YEAR-MONTH-1 ) OR YEAR-MONTH-1 <= ri_pickup <= LAST_DAY(YEAR-MONTH-1) GROUP BY date HAVING NOT ISNULL(date)

查找项目的步骤

选择COUNT(id) FROM rental_inv WHERE ri_pickup = DATE；以在以下位置查找项目

选择COUNT(id) FROM rental_inv WHERE ri_delivery = DATE；以查找余额

SELECT COUNT(out.id) - COUNT(in.id) FROM rental_inv AS out INNER JOIN rental_inv AS in ON DATE( out.ri_pickup ) = DATE( in.ri_delivery ) WHERE out.ri_pickup= DATE OR in.ri_delivery= DATE

你可能可以把所有的东西都连接起来，但因为它的过程更清晰；

Answer 3

我不确定这是否是你问题的确切答案，但我想我会这样做。(我没有使用任何SQL编辑器，所以我想你需要检查语法)

SELECT 
    reportdays.d3 as d, 
    ( COALESCE(outgoing.c1,0) - COALESCE(incoming.c2,0) ) as c
FROM
-- get report dates 
(
    SELECT DATE(FROM_UNIXTIME(COALESCE(l3.ri_delivery_dte, l3.ri_pickup_dte)) d3
    FROM rental_inv l3
    WHERE
        (l3.ri_delivery_dte >= UNIX_TIMESTAMP('2011-02-01') 
            AND l3.ri_delivery_dte < UNIX_TIMESTAMP('2011-03-01'))
        OR (l3.ri_pickup_dte >= UNIX_TIMESTAMP('2011-02-01') 
            AND l3.ri_pickup_dte < UNIX_TIMESTAMP('2011-03-01'))
    GROUP BY d3
) as reportdays
-- get outgoing
LEFT JOIN ( 
    SELECT DATE(FROM_UNIXTIME(l1.ri_delivery_dte)) as d1, count(*) as c1
    FROM rental_inv l1
    LEFT JOIN rental_cancels canc1 on l.ri_num =  canc1.ri_num 
    LEFT JOIN rental_in rent1 on l.ri_num = rent1.ri_num
    WHERE
       l1.ri_delivery_dte >= UNIX_TIMESTAMP('2011-02-01')
       AND l1.ri_delivery_dte < UNIX_TIMESTAMP('2011-03-01')
       AND  canc1.ri_num is  null
       AND rent1.ri_extend = 0
    GROUP BY d1
) as outgoing ON reportdays.d3 = outgoing.d1
-- get incoming
LEFT JOIN ( 
    SELECT DATE(FROM_UNIXTIME(l2.ri_pickup_dte)) as d2, count(*) as c2
    FROM rental_inv l2
    LEFT JOIN rental_cancels canc2 on l2.ri_num =  canc2.ri_num 
    LEFT JOIN rental_in rent2 on l2.ri_num = rent2.ri_num
    WHERE
       l2.ri_pickup_dte >= UNIX_TIMESTAMP('2011-02-01')
       AND l2.ri_pickup_dte < UNIX_TIMESTAMP('2011-03-01')
       AND  canc2.ri_num is  null
       AND rent2.ri_extend = 0
    GROUP BY d2
) as incoming ON reportdays.d3 = incoming.d2