时间戳

首页 > 解决方案 > 如何减少每个月的查询次数

mysql - 如何减少每个月的查询次数

问题描述

我正在使用 My Sql 5.7 和 CF11。我想从下面的查询中获取每个月的总 qTotalPrice 和 quoteKeyID。但不是一整年。我想从当月到最近六个月的数据（从 7 月到 2 月）获取它。如果特定月份没有数据，我需要将特定月份显示为 0。

<cfquery name="thisYearsQuotes" datasource="myDB">
    SELECT * , MONTH(qDateTime) as qMonth
    FROM Quotes
    INNER JOIN Accounts
    ON Quotes.aID = Accounts.aID
    WHERE Accounts.aID = <cfqueryparam value="#VNAI.aID#" cfsqltype="cf_sql_clob" maxlength="255">
    AND Quotes.qDateTime > DATE_ADD(NOW(), INTERVAL -365 DAY)
</cfquery>

这些查询的结果查询结果

为了获取每个月的记录，我编写了如下查询查询。

获取当前月份

<cfquery name="SalesTotalThisMonth" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(NOW())#
</cfquery>

为了获得一个月的回报

<cfquery name="SalesTotal1MonthAgo" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(DateAdd("m",-1,NOW()))#
</cfquery>

为了获得两个月的回报，

<cfquery name="SalesTotal2MonthAgo" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(DateAdd("m",-2,NOW()))#
</cfquery>

等等......就像我必须得到最后六个月一样明智。所以我每个月都写了一些查询。

有没有可能减少代码？

标签： mysqlcoldfusioncfmlcoldfusion-11

解决方案

UPDATE

The solution will be to create a query for all the months you need and then use a left join.

SELECT 8 AS monthNum UNION SELECT 7 AS m UNION SELECT 6 AS m UNION SELECT 5 AS m UNION SELECT 4 AS m UNION SELECT 3 AS m

So to make it automated, I added some CF code to create the above query.

<cfset today=now()>
<cfset start = 0>
<cfset numberOfMonths = 6>
<cfset listOfMonths = ''>
<cfloop condition="numberOfMonths+start GT 0">
  <cfset listOfMonths = listappend(listOfMonths, ' SELECT ' & Month(DateAdd('m', start, today)) & ' AS m ')>
  <cfset start-->
</cfloop>
<cfoutput>
<cfquery>
  SELECT SUM(O.order_Total) AS totalOrder, COUNT(O.order_ID) AS numOrders, MONTH(O.order_Date) AS qMonth
  FROM tbl_orders O 
    RIGHT JOIN (
      #ListChangeDelims(listOfMonths, 'UNION')#
    ) monthList ON monthList.m = MONTH(O.order_Date) AND O.order_Date > DATE_ADD(NOW(), INTERVAL -6 MONTH)
  GROUP BY qMonth;
</cfquery>
<cfoutput>

INITIAL

I think you can do it in a single query with proper group by.

<cfquery name="thisYearsQuotesPerMonth" datasource="mySQL_MILESTONEBMDB">
    SELECT 
      SUM(qTotalPrice) AS DollarTotal, COUNT(quoteKeyID) AS QuoteCount, MONTH(qDateTime) as qMonth
    FROM 
      Quotes
      INNER JOIN Accounts ON Quotes.aID = Accounts.aID
    WHERE 
      Accounts.aID = <cfqueryparam value="#VNAI.aID#" cfsqltype="cf_sql_clob" maxlength="255">
      AND Quotes.qDateTime > DATE_ADD(NOW(), INTERVAL -6 MONTH)
    GROUP BY qMonth
</cfquery>

推荐阅读