100分求教两条SQL 【谢谢】

Question

简化下如下：
表 
ID  User      project(参与项目ID)  开发日期      state
1   jack        1                  2010-6-1
2   jack        1                  2010-6-2
3   jack        1                  2010-6-3
4   jack        1                  2010-6-8
5   jack        2                  2010-6-3
6   jack        2                  2010-6-4
7   jack        2                  2010-6-5
8   jack        3                  2010-6-3
9   jack        3                  2010-6-8
10  jack        3                  2010-6-9
11  robin       1                  2010-6-1
12  robin       2                  2010-6-1
13  robin       2                  2010-6-2
14  robin       3                  2010-6-10
15  clark       1                  2010-6-1想得到的结果是1.
(根据用户同一天最大参与项目个数返回用户名 即：因为2010-6-3 jack同时参与3个项目 所以返回3行jack 同理 2010-6-1 robin最多参与2个项目 所以返回2行robin)返回结果为：
jack
jack
jack
robin
robin
clark想得到的结果是2.（在1的基础上）
返回结果为:
ID  User      project(参与项目ID)  开发日期      state(标示一天内的第几个项目)
1   jack        1                  2010-6-1        1
2   jack        1                  2010-6-2        1
3   jack        1                  2010-6-3        1
4   jack        1                  2010-6-8        1
5   jack        2                  2010-6-3        2
6   jack        2                  2010-6-4        1
7   jack        2                  2010-6-5        1
8   jack        3                  2010-6-3        3
9   jack        3                  2010-6-8        2
10  jack        3                  2010-6-9        1
11  robin       1                  2010-6-1        1
12  robin       2                  2010-6-1        2
13  robin       2                  2010-6-2        1
14  robin       3                  2010-6-10       1
15  clark       1                  2010-6-1        1表结构目前是这样的 需求应该表达的还清楚 不足的地方各位见谅 先谢谢各位

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[User] varchar(5),[project] int,[state] datetime)
insert [tb]
select 1,'jack',1,'2010-6-1' union all
select 2,'jack',1,'2010-6-2' union all
select 3,'jack',1,'2010-6-3' union all
select 4,'jack',1,'2010-6-8' union all
select 5,'jack',2,'2010-6-3' union all
select 6,'jack',2,'2010-6-4' union all
select 7,'jack',2,'2010-6-5' union all
select 8,'jack',3,'2010-6-3' union all
select 9,'jack',3,'2010-6-8' union all
select 10,'jack',3,'2010-6-9' union all
select 11,'robin',1,'2010-6-1' union all
select 12,'robin',2,'2010-6-1' union all
select 13,'robin',2,'2010-6-2' union all
select 14,'robin',3,'2010-6-10' union all
select 15,'clark',1,'2010-6-1'
 
-->查询:
--1.根据用户同一天最大参与项目个数返回用户名
;with josy as
(
  select [user],count(1)  as cnt,convert(varchar(10),state,120) as state
  from tb
  group by [user],convert(varchar(10),state,120)
)
select 
  a.[user]
from tb a
join
  (select * from josy t where not exists(select 1 from josy where [user]=t.[user] and cnt>t.cnt)) b
on a.[user]=b.[user] and convert(varchar(10),a.state,120)=b.state
order by a.id
/**
user
-----
jack
jack
jack
robin
robin
clark(6 行受影响)
**/--2.标示一天内的第几个项目
select
  ID,[User],project,
  convert(varchar(10),state,120) as state,
  rn=row_number() over(partition by [user],convert(varchar(10),state,120) order by id)
from
  tb
order by 
  id
/**
ID          User  project     state      rn
----------- ----- ----------- ---------- --------------------
1           jack  1           2010-06-01 1
2           jack  1           2010-06-02 1
3           jack  1           2010-06-03 1
4           jack  1           2010-06-08 1
5           jack  2           2010-06-03 2
6           jack  2           2010-06-04 1
7           jack  2           2010-06-05 1
8           jack  3           2010-06-03 3
9           jack  3           2010-06-08 2
10          jack  3           2010-06-09 1
11          robin 1           2010-06-01 1
12          robin 2           2010-06-01 2
13          robin 2           2010-06-02 1
14          robin 3           2010-06-10 1
15          clark 1           2010-06-01 1(15 行受影响)
**/

Answer 2

按照sql2000可执行 写得 
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[User] varchar(5),[project] int,[开发日期] datetime ,state int)
insert [tb]
select 1,'jack',1,'2010-6-1',null union all
select 2,'jack',1,'2010-6-2',null union all
select 3,'jack',1,'2010-6-3',null union all
select 4,'jack',1,'2010-6-8',null union all
select 5,'jack',2,'2010-6-3',null union all
select 6,'jack',2,'2010-6-4',null union all
select 7,'jack',2,'2010-6-5',null union all
select 8,'jack',3,'2010-6-3',null union all
select 9,'jack',3,'2010-6-8',null union all
select 10,'jack',3,'2010-6-9',null union all
select 11,'robin',1,'2010-6-1',null union all
select 12,'robin',2,'2010-6-1',null union all
select 13,'robin',2,'2010-6-2',null union all
select 14,'robin',3,'2010-6-10',null union all
select 15,'clark',1,'2010-6-1',null
 
--不知道你有没有用2005,所以写的是2000中也可以的
--1
select [User] from tb t 
where 开发日期=(select top 1 开发日期 from tb 
where [user]=t.[user] group by 开发日期 order by count(1)desc)/*
User  
----- 
jack
jack
jack
robin
robin
clark（所影响的行数为 6 行）*/
--2
select
  ID,[User],project,开发日期,
  state=(select count(1) from tb where [user]=t.[user] and 开发日期=t.开发日期 and id<=t.id)
from  tb t order by   id
/*
ID          User  project     开发日期                                                   state       
----------- ----- ----------- ------------------------------------------------------ ----------- 
1           jack  1           2010-06-01 00:00:00.000                                1
2           jack  1           2010-06-02 00:00:00.000                                1
3           jack  1           2010-06-03 00:00:00.000                                1
4           jack  1           2010-06-08 00:00:00.000                                1
5           jack  2           2010-06-03 00:00:00.000                                2
6           jack  2           2010-06-04 00:00:00.000                                1
7           jack  2           2010-06-05 00:00:00.000                                1
8           jack  3           2010-06-03 00:00:00.000                                3
9           jack  3           2010-06-08 00:00:00.000                                2
10          jack  3           2010-06-09 00:00:00.000                                1
11          robin 1           2010-06-01 00:00:00.000                                1
12          robin 2           2010-06-01 00:00:00.000                                2
13          robin 2           2010-06-02 00:00:00.000                                1
14          robin 3           2010-06-10 00:00:00.000                                1
15          clark 1           2010-06-01 00:00:00.000                                1（所影响的行数为 15 行）*/

Answer 3

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[User] [nvarchar](10),[project] [int],[开发日期] [datetime],[state] [int])
INSERT INTO [tb]
SELECT '1','jack','1','2010-6-1',NULL UNION ALL
SELECT '2','jack','1','2010-6-2',NULL UNION ALL
SELECT '3','jack','1','2010-6-3',NULL UNION ALL
SELECT '4','jack','1','2010-6-8',NULL UNION ALL
SELECT '5','jack','2','2010-6-3',NULL UNION ALL
SELECT '6','jack','2','2010-6-4',NULL UNION ALL
SELECT '7','jack','2','2010-6-5',NULL UNION ALL
SELECT '8','jack','3','2010-6-3',NULL UNION ALL
SELECT '9','jack','3','2010-6-8',NULL UNION ALL
SELECT '10','jack','3','2010-6-9',NULL UNION ALL
SELECT '11','robin','1','2010-6-1',NULL UNION ALL
SELECT '12','robin','2','2010-6-1',NULL UNION ALL
SELECT '13','robin','2','2010-6-2',NULL UNION ALL
SELECT '14','robin','3','2010-6-10',NULL UNION ALL
SELECT '15','clark','1','2010-6-1',NULL--SELECT * FROM [tb]-->SQL查询如下:
--1
--根据用户同一天最大参与项目个数返回用户名 即：因为2010-6-3 jack同时参与3个项目 
--所以返回3行jack 同理 2010-6-1 robin最多参与2个项目 所以返回2行robin;WITH t AS 
(
SELECT [User], rn = RANK()OVER(PARTITION BY [User] 
  ORDER BY(
               SELECT COUNT(1) 
               FROM tb
               WHERE [User] = a.[User]
                AND [开发日期] = a.[开发日期]
           ) DESC
       ) 
FROM tb a
)
SELECT [User]
FROM t
WHERE rn=1
/*
User
----------
clark
jack
jack
jack
robin
robin(6 行受影响)
*/--2.
;WITH t AS 
(
SELECT ID, [User], project, 开发日期, 
[state] = ROW_NUMBER()OVER(PARTITION BY [User], 开发日期 ORDER BY ID) 
FROM tb
)
SELECT *
FROM t
ORDER BY 1
/*ID          User       project     开发日期                    state
----------- ---------- ----------- ----------------------- --------------------
1           jack       1           2010-06-01 00:00:00.000 1
2           jack       1           2010-06-02 00:00:00.000 1
3           jack       1           2010-06-03 00:00:00.000 1
4           jack       1           2010-06-08 00:00:00.000 1
5           jack       2           2010-06-03 00:00:00.000 2
6           jack       2           2010-06-04 00:00:00.000 1
7           jack       2           2010-06-05 00:00:00.000 1
8           jack       3           2010-06-03 00:00:00.000 3
9           jack       3           2010-06-08 00:00:00.000 2
10          jack       3           2010-06-09 00:00:00.000 1
11          robin      1           2010-06-01 00:00:00.000 1
12          robin      2           2010-06-01 00:00:00.000 2
13          robin      2           2010-06-02 00:00:00.000 1
14          robin      3           2010-06-10 00:00:00.000 1
15          clark      1           2010-06-01 00:00:00.000 1(15 行受影响)
*/