我正在建立一个图表,需要有一个月的所有天代表申请提交。在某些日子里,可能有5个,而在其他日子里,可能有0个。我正在从应用程序表中获取数据,但我需要为没有值的日期追加字符串。例如今天是05/29,有3个人申请了。5月21日,2人申请。在此期间(及之前)没有其他人申请。所以我需要得到这些日期并为它们存储一个0值。
下面是我用来带回和分组数据的:
SELECT DISTINCT to_char ("applicationdate",'mm') AS applicationmonth
, to_char ("applicationdate",'dd') AS applicationdate
, to_char ("applicationdate", 'yyyy') AS applicationyear
, term
, COUNT(*) AS count
FROM campapplications.basketball
WHERE term = 'SU'
GROUP BY applicationdate, term
ORDER BY applicationdate, term
最后,我要做一个插入。
我查看了其他地方,但除了使用
listAppend()
函数之外,无法真正获得任何输入。 最佳答案
现在还不清楚“附加字符串”与图表或插入的关系。。。但是,只要关注查询,就可以使用日历表或CTE生成特定日期范围内的所有日期。(对于较大的数据集,日历表通常更好)。一旦有了日期,outer join
它们就会返回到应用程序表中,以获取每个日期的计数。
您没有提到您使用的是哪个DBMS,但从语法上我猜是Oracle。假设“applicationDate”列只包含日期(没有时间),下面的查询将返回给定范围内所有日期的计数:#startDate#
和#endDate#
。根据需要调整日期。
SQL:http://sqlfiddle.com/#!4/c09877/2
WITH CTE(TheDate) AS
(
SELECT FromDate+level-1
FROM (
SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS FromDate
, <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date"> AS ToDate
FROM dual
) d
CONNECT BY LEVEL <= ToDate - FromDate + 1
)
SELECT TheDate
, COUNT(b.applicationDate) AS count
FROM cte LEFT JOIN basketball b ON b.applicationDate = cte.TheDate
GROUP BY TheDate
ORDER BY TheDate
;
样本结果:
| THEDATE | COUNT |
|----------------------|-------|
| 2019-05-21T00:00:00Z | 2 |
| 2019-05-22T00:00:00Z | 0 |
| 2019-05-23T00:00:00Z | 0 |
| 2019-05-24T00:00:00Z | 0 |
| 2019-05-25T00:00:00Z | 0 |
| 2019-05-26T00:00:00Z | 0 |
| 2019-05-27T00:00:00Z | 0 |
| 2019-05-28T00:00:00Z | 0 |
| 2019-05-29T00:00:00Z | 3 |
更新:
我对PostgreSQL不是很在行,但是this thread演示了如何生成日期范围。只需用cfqueryparam替换硬编码日期。
SQL:http://sqlfiddle.com/#!15/2a0a7/4
SELECT TheDate
, COUNT(b.applicationDate) AS count
FROM generate_series
( '2019-05-21'::date
, '2019-05-29'::date
, '1 day'::interval) TheDate
LEFT JOIN basketball b ON b.applicationDate = TheDate
GROUP BY TheDate
ORDER BY TheDate