Question

我的技能很强,足以说明这一点,希望有人能够提供帮助。 on:

我有一张桌子试图:

attempt_id | word | score | attempt_time
----------------------------------------
1          | w1   | 1     | 2011-09-01
2          | w1   | 2     | 2011-09-02
3          | w2   | 1     | 2011-09-02
4          | w3   | 1     | 2011-09-03
5          | w4   | 0     | 2011-09-03
6          | w1   | 0     | 2011-09-04

每一字都有与其相关的分数;我想找到有分数的字数;=1个字数。日复一日保留了计票,日记t。页: 1 当一字到分0时,就不应计算。结果是:

attempt_time | count(word)
--------------------------
2011-09-01   | 1              // w1 (1)
2011-09-02   | 2              // w1, w2 (2)
2011-09-03   | 3              // w1, w2, w3 (3)
2011-09-04   | 2              // w2, w3 (2) (w1 now has score 0)

有许多尝试——时间,因此,你可以单独通过。我怀疑,我需要用一个子座来总结每个日期和以前的日期,但仍然需要一种方式来抛弃重复(例如,仅计重一)。

它应当这样做,但需要所有日期,而不仅仅是一个日期:

select count(distinct word) from attempts where score > 0 
and attempt_time <=  2011-09-03 ;

> 3

我的另一种选择是建立一个单独的表格,仅每天追踪总数,但我不得不将数据放在一栏中。

非常感谢。

<><>Edit>: 我试图增加一些细节,使问题更加清楚。希望会有所助益。

Answer 1

似乎没有好这样做。

Answer 2

如果你需要根据你再次希望使用“小组”条款的日期来汇总和显示各项内容。摘录如下。

Select attempt_time, count(*) from attempts where score > 0 group by attempt_time

Here s another example of someone solving a similar problem. SELECT *, COUNT(*) in SQLite

Answer 3

首先,我认为你的结果是:

attempt_time | count(word)
--------------------------
2011-09-01   | 1              // w1 has score 1
2011-09-02   | 3              // w1 increased to 2, plus w2 has score 1
2011-09-03   | 5              // w1 and w2 same, w3 has score 1, w4 has no score
2011-09-04   | 5              // no change

But you d not get a Sept 4th date as it s not in the DB. But anyhoo, You need this:

http://www.ohchr.org。

select attempt_time, (@csum := @csum + score) as score from attempts where score > 0 and attempt_time <= 2011-09-03 group by attempt_time;

Which I just simply stole from this: Create a Cumulative Sum Column in MySQL (Big up to https://stackoverflow.com/users/50552/andomar)

[......]你需要什么?

页: 1

友情链接