Java开发网 - [转帖]SQL 很难面试题，求助！

Topic: [转帖]SQL 很难面试题，求助！

1.[转帖]SQL 很难面试题，求助！

Posted by: ranchgirl
Posted on: 2005-12-30 13:08

This is copied from ChinaJavaWorld.com. I want to know the answer too. It seems real hard. I put the several posts of original question together here. If somebody is able to find the answer of it.

Here we are:

=============
A real interview question from a famous company which I got today.
I still don't know the answer, please help.
It is easy to use Java to do it, but how to use SQL to get the result???


Table T

  USER_ID   event   date
  1         on      2003-12-30
  1         off     2004-01-02
  3         on      2003-12-31
  3         off     2004-01-05
  5         on      2004-01-03
  5         off     2003-01-04
  1         on      2004-01-03
  1         off     2004-01-09
  2         on      2004-01-08
  2         off     2004-01-10
  7         on      2004-01-12
  7         off     2004-01-13

How to use SQL to get the statistics of count(USER_ID) each date covered in the table???
The "Actual Users (not needed)" is for you to understand. The third column is not part of the result.
They are just my understanding about what USER_IDs are counted.

on is starting time, off is end time. That is why it is hard, since you must count the date in-between too.
For example


  3         on      2003-12-31  
  3         off     2004-01-05

means USER_ID 3 turned on the service on at 2003-12-31, turned off the service on 2004-01-05, used the service for 6 days.
Partial days are counted as full day.


count   date            Actual User_IDs (not needed)
1       2003-12-30      (1)
2       2003-12-31      (1, 3)
2       2004-01-01      (1, 3)
2       2004-01-02      (1, 3)
3       2004-01-03      (3, 5, 1)
3       2004-01-04      (3, 5, 1)
2       2004-01-05      (3, 1)
1       2004-01-06      (1)
1       2004-01-07      (1)
2       2004-01-08      (1, 2)
2       2004-01-09      (1, 2)
1       2004-01-10      (2)
0       2004-01-11      ()
1       2004-01-12      (7)
1       2004-01-13      (7)

Thanks a lot!
=============

2.Re:[转帖]SQL 很难面试题，求助！ [Re: gongshi]	Copy to clipboard
Posted by: ranchgirl Posted on: 2005-12-30 13:10 Here is the link if someone wants to see the original discussion: http://bbs.chinajavaworld.com/post/view?bid=8&id=701494&sty=1&tpg=1&age=0 It is hard

3.Re:[转帖]SQL 很难面试题，求助！ [Re: ranchgirl]	Copy to clipboard
Posted by: niugy Posted on: 2006-02-13 10:09 没明白楼主表达的意识，能否用中文再重新描述一下？

4.Re:[转帖]SQL 很难面试题，求助！ [Re: ranchgirl]	Copy to clipboard
Posted by: whilezf Posted on: 2006-02-13 12:36 这不是一个很困难的问题，用SQL实现比用JAVA实现要容易