SQL按照日、周、月、年统计数据的方法分享

前端之家收集整理的这篇文章主要介绍了SQL按照日、周、月、年统计数据的方法分享前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

--按日
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

sql;"> 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语句,记一笔以防忘了

sql;"> /// /// 获取统计数据 /// /// /// 统计类型(日、周、月、年) /// [WebMethod(true)] public static string GetData3(string CKEY,string type) { StringBuilder strsql = new StringBuilder();
  1. #region <a href="/tag/sql/" target="_blank" class="keywords">sql</a>语句
  2. if (type == "0")
  3. {
  4. #region 日
  5. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WITH WeekDate ");
  6. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AS ( SELECT DATEADD(d,-DAY(GETDATE()) + 1,GETDATE()) AS riqi ");
  7. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  8. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT riqi + 1 FROM WeekDate ");
  9. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE riqi + 1 <= ( SELECT DATEADD(d,-DAY(GETDATE()),DATEADD(m,1,GETDATE())) ) ");
  10. strSql.AppendFormat(" ) ");
  11. strSql.AppendFormat(" SELECT CONVERT(CHAR(8),a.riqi,112) AS 日,DAY (CONVERT(CHAR(8),112)) AS DDay,");
  12. strSql.AppendFormat(" ISNULL(tbB.日成交量,0) AS 日成交量,");
  13. strSql.AppendFormat(" CASE WHEN CONVERT(CHAR(8),112) > CONVERT(CHAR(8),GETDATE(),112) ");
  14. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" THEN NULL ");
  15. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHEN CONVERT(CHAR(8),112) <= CONVERT(CHAR(8),112) ");
  16. strSql.AppendFormat(" THEN ISNULL(tbB.日成交量,0) ");
  17. strSql.AppendFormat(" END AS 日成交数量,");
  18. strSql.AppendFormat(" tbB.日实收金额,112) ");
  19. strSql.AppendFormat(" THEN ISNULL(tbB.日实收金额,0) ");
  20. strSql.AppendFormat(" END AS 日实收金额2 ");
  21. strSql.AppendFormat(" FROM WeekDate a ");
  22. strSql.AppendFormat(" LEFT JOIN ( SELECT ( SELECT COUNT(1) ");
  23. strSql.AppendFormat(" FROM dbo.CustomerBase base ");
  24. strSql.AppendFormat(" WHERE CKEY = '{0}' ",CKEY);
  25. strSql.AppendFormat(" AND " + impomo.TotalConsumptionMon + " > 0 ");
  26. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND TargetDate = cus.TargetDate ");
  27. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) 日成交量,");
  28. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ISNULL(( SELECT SUM(Total) ");
  29. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM ( SELECT SUM(CONVERT(FLOAT,ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ),0))) AS Total ");
  30. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM PaymentContent AS pay ");
  31. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  32. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND pay.CKEY = '{0}' ",CKEY);
  33. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  34. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT SUM(CONVERT(FLOAT,ISNULL(RecMoney,0))) AS Total ");
  35. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CardRecharge8 AS recharge ");
  36. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE RechargDate = cus.TargetDate ");
  37. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND recharge.CKEY = '{0}' ",0))) AS Total ");
  38. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.PaymentSwimming AS payswim ");
  39. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  40. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND payswim.CKEY = '{0}' ",ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ),0))) AS Total ");
  41. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM WarePaymentContent AS ware ");
  42. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  43. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND ware.CKEY = '{0}' ",CKEY);
  44. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) B ");
  45. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ),0) AS 日实收金额,");
  46. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" TargetDate 日 ");
  47. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CustomerBase cus ");
  48. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(TargetDate) = YEAR(GETDATE()) ");
  49. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND MONTH(TargetDate) = MONTH(GETDATE()) ");
  50. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" GROUP BY TargetDate ");
  51. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) AS tbB ON CONVERT(CHAR(8),112) = tbB.日 ");
  52. #endregion
  53. }
  54. else if (type == "1")
  55. {
  56. #region 周
  57. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WITH WeekDate ");
  58. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AS ( SELECT DATEADD(wk,DATEDIFF(wk,GETDATE()),0) AS riqi ");
  59. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  60. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT riqi + 1 FROM WeekDate ");
  61. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE riqi + 1 <= ( SELECT DATEADD(wk,6) ) ");
  62. strSql.AppendFormat(" ) ");
  63. strSql.AppendFormat(" SELECT CONVERT(CHAR(8),");
  64. strSql.AppendFormat(" DATENAME(weekday,CONVERT(CHAR(8),112)) DDay,");
  65. strSql.AppendFormat(" ISNULL(tbB.日成交量,");
  66. strSql.AppendFormat(" CASE WHEN CONVERT(CHAR(8),112) ");
  67. strSql.AppendFormat(" THEN NULL ");
  68. strSql.AppendFormat(" WHEN CONVERT(CHAR(8),112) ");
  69. strSql.AppendFormat(" THEN ISNULL(tbB.日成交量,0) ");
  70. strSql.AppendFormat(" END AS 日成交数量,");
  71. strSql.AppendFormat(" tbB.日实收金额,112) ");
  72. strSql.AppendFormat(" THEN ISNULL(tbB.日实收金额,0) ");
  73. strSql.AppendFormat(" END AS 日实收金额2 ");
  74. strSql.AppendFormat(" FROM WeekDate a ");
  75. strSql.AppendFormat(" LEFT JOIN ( SELECT ( SELECT COUNT(1) ");
  76. strSql.AppendFormat(" FROM dbo.CustomerBase base ");
  77. strSql.AppendFormat(" WHERE CKEY = '{0}'",CKEY);
  78. strSql.AppendFormat(" AND " + impomo.TotalConsumptionMon + " > 0 ");
  79. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND TargetDate = cus.TargetDate ");
  80. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) 日成交量,");
  81. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ISNULL(( SELECT SUM(Total) ");
  82. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM ( SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  83. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM PaymentContent AS pay ");
  84. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  85. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND pay.CKEY = '{0}'",CKEY);
  86. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  87. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  88. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CardRecharge8 AS recharge ");
  89. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE RechargDate = cus.TargetDate ");
  90. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND recharge.CKEY = '{0}'",0))) AS Total ");
  91. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.PaymentSwimming AS payswim ");
  92. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  93. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND payswim.CKEY = '{0}'",0))) AS Total ");
  94. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM WarePaymentContent AS ware ");
  95. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE PayDate = cus.TargetDate ");
  96. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND ware.CKEY = '{0}'",CKEY);
  97. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) B ");
  98. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ),");
  99. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" TargetDate 日 ");
  100. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CustomerBase cus ");
  101. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE DATEPART(wk,TargetDate) = DATEPART(wk,GETDATE()) ");
  102. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND DATEPART(yy,TargetDate) = DATEPART(yy,GETDATE()) ");
  103. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" GROUP BY TargetDate ");
  104. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) AS tbB ON CONVERT(CHAR(8),112) = tbB.日 ");
  105. #endregion
  106. }
  107. else if (type == "2")
  108. {
  109. #region 月
  110. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat("SELECT YearMonth.月,");
  111. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" tb.月成交量,");
  112. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
  113. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量,0) ");
  114. strSql.AppendFormat(" END AS 月成交数量,");
  115. strSql.AppendFormat(" tb.月实收总金额,");
  116. strSql.AppendFormat(" CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
  117. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额,0) ");
  118. strSql.AppendFormat(" END AS 月实收总金额2 ");
  119. strSql.AppendFormat(" FROM ( SELECT 1 AS 月 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ");
  120. strSql.AppendFormat(" UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ");
  121. strSql.AppendFormat(" ) AS YearMonth ");
  122. strSql.AppendFormat(" LEFT JOIN ( SELECT ( SELECT COUNT(1) ");
  123. strSql.AppendFormat(" FROM dbo.CustomerBase base ");
  124. strSql.AppendFormat(" WHERE CKEY = '{0}' ",CKEY);
  125. strSql.AppendFormat(" AND " + impomo.TotalConsumptionMon + " > 0 ");
  126. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND MONTH(TargetDate) = MONTH(cus.TargetDate) ");
  127. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) 月成交量,");
  128. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ISNULL(( SELECT SUM(Total) ");
  129. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM ( SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  130. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM PaymentContent AS pay ");
  131. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE MONTH(PayDate) = MONTH(cus.TargetDate) ");
  132. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND pay.CKEY = '{0}' ",CKEY);
  133. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  134. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  135. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CardRecharge8 AS recharge ");
  136. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE MONTH(RechargDate) = MONTH(cus.TargetDate) ");
  137. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND recharge.CKEY = '{0}' ",0))) AS Total ");
  138. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.PaymentSwimming AS payswim ");
  139. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE MONTH(PayDate) = MONTH(cus.TargetDate) ");
  140. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND payswim.CKEY = '{0}' ",0))) AS Total ");
  141. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM WarePaymentContent AS ware ");
  142. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE MONTH(PayDate) = MONTH(cus.TargetDate) ");
  143. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND ware.CKEY = '{0}' ",CKEY);
  144. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) B ");
  145. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ),0) AS 月实收总金额,");
  146. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" MONTH(TargetDate) 月 ");
  147. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CustomerBase cus ");
  148. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(TargetDate) = YEAR(GETDATE()) ");
  149. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" GROUP BY MONTH(cus.TargetDate) ");
  150. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) AS tb ON YearMonth.月 = tb.月 ");
  151. #endregion
  152. }
  153. else if (type == "3")
  154. {
  155. #region 年
  156. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat("SELECT ( SELECT COUNT(1) ");
  157. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CustomerBase base ");
  158. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE CKEY = '{0}' ",CKEY);
  159. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND " + impomo.TotalConsumptionMon + " > 0 ");
  160. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND YEAR(TargetDate) = YEAR(cus.TargetDate) ");
  161. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) 年成交量,");
  162. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
  163. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM ( SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  164. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM PaymentContent AS pay ");
  165. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(PayDate) = YEAR(cus.TargetDate) ");
  166. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND pay.CKEY = '{0}' ",CKEY);
  167. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" UNION ALL ");
  168. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" SELECT SUM(CONVERT(FLOAT,0))) AS Total ");
  169. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CardRecharge8 AS recharge ");
  170. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(RechargDate) = YEAR(cus.TargetDate) ");
  171. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND recharge.CKEY = '{0}' ",0))) AS Total ");
  172. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.PaymentSwimming AS payswim ");
  173. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(PayDate) = YEAR(cus.TargetDate) ");
  174. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND payswim.CKEY = '{0}' ",0))) AS Total ");
  175. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM WarePaymentContent AS ware ");
  176. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" WHERE YEAR(PayDate) = YEAR(cus.TargetDate) ");
  177. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" AND ware.CKEY = '{0}' ",CKEY);
  178. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ) B ");
  179. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" ),0))) AS 年实收总金额,");
  180. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" YEAR(TargetDate) 年 ");
  181. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" FROM dbo.CustomerBase cus ");
  182. str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.AppendFormat(" GROUP BY YEAR(TargetDate) ");
  183. #endregion
  184. }
  185. #endregion
  186. DataTable table = DBHelper.GetDateTable(str<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.ToString());
  187. string rs = Newtonsoft.Json.JsonConvert.SerializeObject(table);
  188. return rs;
  189. }</pre>

猜你在找的MsSQL相关文章