伏龙阁资源网

SQL按照日、周、月、年统计数据的方法分享
站长资源 2024/11/25 佚名
2 0

--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])

--按周quarter
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])

--按月
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])

--按季
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date])
 

--按年
select sum(consume),year([date]) from consume_record where  group by year([date])

DATE_FORMAT

select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks; 
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; 
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

本文只是记录在项目中用到的统计的SQL语句，记一笔以防忘了

 /// <summary>
    /// 获取统计数据
    /// </summary>
    /// <param name="CKEY">店面ckey</param>
    /// <param name="type">统计类型（日、周、月、年）</param>
    /// <returns></returns>
    [WebMethod(true)]
    public static string GetData3(string CKEY, string type)
    {
      StringBuilder strSql = new StringBuilder();
      
      #region SQL语句

      if (type == "0")
      {
        #region 日
        strSql.AppendFormat(" WITH  WeekDate ");
        strSql.AppendFormat("     AS ( SELECT  DATEADD(d, -DAY(GETDATE()) + 1, GETDATE()) AS riqi ");
        strSql.AppendFormat("       UNION ALL ");
        strSql.AppendFormat("       SELECT  riqi + 1 FROM   WeekDate ");
        strSql.AppendFormat("       WHERE  riqi + 1 <= ( SELECT  DATEADD(d, -DAY(GETDATE()), DATEADD(m, 1, GETDATE())) ) ");
        strSql.AppendFormat("      ) ");
        strSql.AppendFormat("  SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 ,DAY (CONVERT(CHAR(8), a.riqi, 112)) AS DDay, ");
        strSql.AppendFormat("      ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
        strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("        THEN NULL ");
        strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("        THEN ISNULL(tbB.日成交量, 0) ");
        strSql.AppendFormat("      END AS 日成交数量 , ");
        strSql.AppendFormat("      tbB.日实收金额 , ");
        strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("        THEN NULL ");
        strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("        THEN ISNULL(tbB.日实收金额, 0) ");
        strSql.AppendFormat("      END AS 日实收金额2 ");
        strSql.AppendFormat("  FROM  WeekDate a ");
        strSql.AppendFormat("      LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
        strSql.AppendFormat("                 FROM   dbo.CustomerBase base ");
        strSql.AppendFormat("                 WHERE   CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                      AND " + impomo.TotalConsumptionMon + " > 0 ");
        strSql.AppendFormat("                      AND TargetDate = cus.TargetDate ");
        strSql.AppendFormat("                ) 日成交量 , ");
        strSql.AppendFormat("                ISNULL(( SELECT SUM(Total) ");
        strSql.AppendFormat("                    FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                         FROM   PaymentContent AS pay ");
        strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                              AND pay.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                         UNION ALL ");
        strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
        strSql.AppendFormat("                         FROM   dbo.CardRecharge8 AS recharge ");
        strSql.AppendFormat("                         WHERE   RechargDate = cus.TargetDate ");
        strSql.AppendFormat("                              AND recharge.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                         UNION ALL ");
        strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                         FROM   dbo.PaymentSwimming AS payswim ");
        strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                              AND payswim.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                         UNION ALL ");
        strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
        strSql.AppendFormat("                         FROM   WarePaymentContent AS ware ");
        strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                              AND ware.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                        ) B ");
        strSql.AppendFormat("                   ), 0) AS 日实收金额 , ");
        strSql.AppendFormat("                TargetDate 日 ");
        strSql.AppendFormat("            FROM  dbo.CustomerBase cus ");
        strSql.AppendFormat("            WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
        strSql.AppendFormat("                AND MONTH(TargetDate) = MONTH(GETDATE()) ");
        strSql.AppendFormat("            GROUP BY TargetDate ");
        strSql.AppendFormat("           ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
        #endregion
      }
      else if (type == "1")
      {
        #region 周
        strSql.AppendFormat(" WITH  WeekDate ");
        strSql.AppendFormat("       AS ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AS riqi ");
        strSql.AppendFormat("         UNION ALL ");
        strSql.AppendFormat("         SELECT  riqi + 1 FROM   WeekDate ");
        strSql.AppendFormat("         WHERE  riqi + 1 <= ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6) ) ");
        strSql.AppendFormat("        ) ");
        strSql.AppendFormat("    SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 , ");
        strSql.AppendFormat("        DATENAME(weekday,CONVERT(CHAR(8), a.riqi, 112)) DDay, ");
        strSql.AppendFormat("        ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
        strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("          THEN NULL ");
        strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("          THEN ISNULL(tbB.日成交量, 0) ");
        strSql.AppendFormat("        END AS 日成交数量 , ");
        strSql.AppendFormat("        tbB.日实收金额 , ");
        strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("          THEN NULL ");
        strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
        strSql.AppendFormat("          THEN ISNULL(tbB.日实收金额, 0) ");
        strSql.AppendFormat("        END AS 日实收金额2 ");
        strSql.AppendFormat("    FROM  WeekDate a ");
        strSql.AppendFormat("        LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
        strSql.AppendFormat("                   FROM   dbo.CustomerBase base ");
        strSql.AppendFormat("                   WHERE   CKEY = '{0}'", CKEY);
        strSql.AppendFormat("                        AND " + impomo.TotalConsumptionMon + " > 0 ");
        strSql.AppendFormat("                        AND TargetDate = cus.TargetDate ");
        strSql.AppendFormat("                  ) 日成交量 , ");
        strSql.AppendFormat("                  ISNULL(( SELECT SUM(Total) ");
        strSql.AppendFormat("                      FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                           FROM   PaymentContent AS pay ");
        strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                                AND pay.CKEY = '{0}'", CKEY);
        strSql.AppendFormat("                           UNION ALL ");
        strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
        strSql.AppendFormat("                           FROM   dbo.CardRecharge8 AS recharge ");
        strSql.AppendFormat("                           WHERE   RechargDate = cus.TargetDate ");
        strSql.AppendFormat("                                AND recharge.CKEY = '{0}'", CKEY);
        strSql.AppendFormat("                           UNION ALL ");
        strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                           FROM   dbo.PaymentSwimming AS payswim ");
        strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                                AND payswim.CKEY = '{0}'", CKEY);
        strSql.AppendFormat("                           UNION ALL ");
        strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
        strSql.AppendFormat("                           FROM   WarePaymentContent AS ware ");
        strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
        strSql.AppendFormat("                                AND ware.CKEY = '{0}'", CKEY);
        strSql.AppendFormat("                          ) B ");
        strSql.AppendFormat("                     ), 0) AS 日实收金额 , ");
        strSql.AppendFormat("                  TargetDate 日 ");
        strSql.AppendFormat("              FROM  dbo.CustomerBase cus ");
        strSql.AppendFormat("              WHERE  DATEPART(wk, TargetDate) = DATEPART(wk, GETDATE()) ");
        strSql.AppendFormat("                  AND DATEPART(yy, TargetDate) = DATEPART(yy, GETDATE()) ");
        strSql.AppendFormat("              GROUP BY TargetDate ");
        strSql.AppendFormat("             ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
        #endregion
      }
      else if (type == "2")
      {
        #region 月

        strSql.AppendFormat("SELECT YearMonth.月 , ");
        strSql.AppendFormat("    tb.月成交量 , ");
        strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
        strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量, 0) ");
        strSql.AppendFormat("    END AS 月成交数量 , ");
        strSql.AppendFormat("    tb.月实收总金额 , ");
        strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
        strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额, 0) ");
        strSql.AppendFormat("    END AS 月实收总金额2 ");
        strSql.AppendFormat(" FROM   ( SELECT 1 AS 月 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ");
        strSql.AppendFormat("       UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ");
        strSql.AppendFormat("      ) AS YearMonth ");
        strSql.AppendFormat("    LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
        strSql.AppendFormat("               FROM   dbo.CustomerBase base ");
        strSql.AppendFormat("               WHERE   CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                    AND " + impomo.TotalConsumptionMon + " > 0 ");
        strSql.AppendFormat("                    AND MONTH(TargetDate) = MONTH(cus.TargetDate) ");
        strSql.AppendFormat("              ) 月成交量 , ");
        strSql.AppendFormat("              ISNULL(( SELECT SUM(Total) ");
        strSql.AppendFormat("                  FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                       FROM   PaymentContent AS pay ");
        strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
        strSql.AppendFormat("                            AND pay.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                       UNION ALL ");
        strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
        strSql.AppendFormat("                       FROM   dbo.CardRecharge8 AS recharge ");
        strSql.AppendFormat("                       WHERE   MONTH(RechargDate) = MONTH(cus.TargetDate) ");
        strSql.AppendFormat("                            AND recharge.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                       UNION ALL ");
        strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("                       FROM   dbo.PaymentSwimming AS payswim ");
        strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
        strSql.AppendFormat("                            AND payswim.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                       UNION ALL ");
        strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
        strSql.AppendFormat("                       FROM   WarePaymentContent AS ware ");
        strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
        strSql.AppendFormat("                            AND ware.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("                      ) B ");
        strSql.AppendFormat("                 ), 0) AS 月实收总金额 , ");
        strSql.AppendFormat("              MONTH(TargetDate) 月 ");
        strSql.AppendFormat("          FROM  dbo.CustomerBase cus ");
        strSql.AppendFormat("          WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
        strSql.AppendFormat("          GROUP BY MONTH(cus.TargetDate) ");
        strSql.AppendFormat("         ) AS tb ON YearMonth.月 = tb.月 ");
        #endregion
      }
      else if (type == "3")
      {
        #region 年
        strSql.AppendFormat("SELECT ( SELECT  COUNT(1) ");
        strSql.AppendFormat("       FROM   dbo.CustomerBase base ");
        strSql.AppendFormat("       WHERE   CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("            AND " + impomo.TotalConsumptionMon + " > 0 ");
        strSql.AppendFormat("            AND YEAR(TargetDate) = YEAR(cus.TargetDate) ");
        strSql.AppendFormat("      ) 年成交量 , ");
        strSql.AppendFormat("      CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
        strSql.AppendFormat("          FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("               FROM   PaymentContent AS pay ");
        strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
        strSql.AppendFormat("                    AND pay.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("               UNION ALL ");
        strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
        strSql.AppendFormat("               FROM   dbo.CardRecharge8 AS recharge ");
        strSql.AppendFormat("               WHERE   YEAR(RechargDate) = YEAR(cus.TargetDate) ");
        strSql.AppendFormat("                    AND recharge.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("               UNION ALL ");
        strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
        strSql.AppendFormat("               FROM   dbo.PaymentSwimming AS payswim ");
        strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
        strSql.AppendFormat("                    AND payswim.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("               UNION ALL ");
        strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
        strSql.AppendFormat("               FROM   WarePaymentContent AS ware ");
        strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
        strSql.AppendFormat("                    AND ware.CKEY = '{0}' ", CKEY);
        strSql.AppendFormat("              ) B ");
        strSql.AppendFormat("         ), 0))) AS 年实收总金额 , ");
        strSql.AppendFormat("      YEAR(TargetDate) 年 ");
        strSql.AppendFormat("  FROM  dbo.CustomerBase cus ");
        strSql.AppendFormat("  GROUP BY YEAR(TargetDate) ");
        #endregion
      }

      #endregion

      DataTable table = DBHelper.GetDateTable(strSql.ToString());
      string rs = Newtonsoft.Json.JsonConvert.SerializeObject(table);
      return rs;
    }

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
www.wwsws.com 伏龙阁资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接