Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: pls help ~ query sql stmt

    Hi,

    any 1 can help me.. sql statement

    Table User
    userlogin|username|
    ---------|--------|
    ken |Ken |
    lam |Ah Lam |
    man |Ah Man |

    Table Game
    game_name|gamedetail |
    ---------|--------------|
    3fun |play for fun |
    twinbee |shooting game |
    1943 |1943 |

    Table Score
    userlogin|score|game_name|playdatetime|
    ---------|-----|---------|------------|
    ken | 4|3fun |2004-01-01 |
    ken | 2|twinbee |2004-01-02 |
    ken | 1|3fun |2004-03-01 |
    ken | 2|twinbee |2004-03-02 |
    ken | 3|twinbee |2004-03-11 |
    ken | 4|3fun |2004-03-11 |
    ken | 0|3fun |2004-03-12 |
    ken | 3|twinbee |2004-03-12 |
    ken | 4|1943 |2004-03-15 |
    ken | 4|1943 |2004-03-17 |


    Result Table
    Ken's Stat

    |Last 3 days |Last 7 days |All |
    |---------------------|---------------------|---------------------|
    |Play Count|Game Count|Play Count|Game Count|Play Count|Game Count|
    score=4 | 2| 1| 3| 2| 4| 2|
    score=3 | 0| 0| 2| 1| 2| 1|
    score=2 | 0| 0| 0| 0| 2| 1|
    score=1 | 0| 0| 0| 0| 1| 1|
    score=0 | 0| 0| 0| 0| 1| 1|


    thx first
    Last edited by kenTsang77; 03-16-04 at 13:13.

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    What exactly do you need to get out of the query?

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    Originally posted by trieder
    What exactly do you need to get out of the query?
    I want to gruop the records by 2 fields, score and playdatetime

    finally I solved the problem.

    Thanks anyway

    set @now:=now();
    set @cut1:=date_add(@now, interval -3 day);
    set @cut2:=date_add(@now, interval -7 day);
    set @userlogin:='buyer';
    select
    score,
    playdatetime > @cut1 as d1,
    playdatetime > @cut2 && playdatetime <= @cut1 as d2,
    playdatetime <= @now as d3,
    count(*) as playcount,
    count(distinct(game_name)) as gamecount
    from TableScore
    where userlogin = @userlogin
    group by score,
    playdatetime > @cut1,
    playdatetime > @cut2 && playdatetime <= @cut1,
    playdatetime <= @now;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •