选择ID，统计ID数量并按日期分组

Question

我有一张文章表格，其中包括id和日期（月/年）两列。首先，我想按日期对id进行计数并分组，然后我想在单个查询中查看哪个id属于哪个日期组，如下所示：

id   date   count
-----------------
1    01/2015   2
2    01/2015   2
3    02/2015   1
4    03/2015   4
5    03/2015   4
6    03/2015   4
7    03/2015   4

我有两个查询

Select Count(id) 
from article 
group by date

并且。

Select id 
from article

提供结果;

count date            id  date
-------------         ----------
2     01/2015         1  01/2015
1     02/2015         2  01/2015 
4     03/2015         3  02/2015

我需要一条像下面这样的查询语句：

select count(id), id, date 
from....

请问有人能帮我将id、count、date列翻译成适用于我的C#代码的内容吗？

谢谢！

- Gokhan Turkben

---

关联子查询用于计数。 - Tab Alleman

Answer 1

SELECT id,
       date,
       COUNT(*) OVER (PARTITION BY date) AS Count
FROM article

Sql fiddle

- Martin Smith

Answer 2

无法通过一个查询完成，但可以使用公共表表达式(CTE)生成单个结果集:

create table #tt (id int null, dt varchar(8))
insert #tt values
(1,'01/2015'),
(2,'01/2015'),
(3,'02/2015'),
(4,'03/2015'),
(5,'03/2015'),
(6,'03/2015'),
(7,'03/2015')

;with cteCount(d, c) AS
(
    select dt, count(id) from #tt group by dt
)
select id, dt, c
from #tt a
inner join cteCount cc
on a.dt = cc.d

drop table #tt

结果：

id  dt      c
1   01/2015 2
2   01/2015 2
3   02/2015 1
4   03/2015 4
5   03/2015 4
6   03/2015 4
7   03/2015 4

- Dan Field

Answer 3

if not exists(select * from TEST.sys.objects where type=N'U' and name=N'article')
begin
    create table article(
    [id] int,
    [date] date)
end

使用这些数据：

insert into article(id,date) values(1,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(5,convert(date,'15/02/2015',103));
insert into article(id,date) values(1,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(3,convert(date,'15/01/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));

select id,[date], count(id) [count] from article
group by [date],[id]

the result:

id  date    count
1   2015-01-15  1
1   2015-02-15  2
1   2015-03-15  1
2   2015-01-15  2
2   2015-03-15  2
3   2015-01-15  1
3   2015-02-15  2
4   2015-03-15  3
5   2015-01-15  2
5   2015-02-15  1

- Eric Estrada Gómez

---

尝试使用原始结果集进行操作，这样就会更清楚为什么这不起作用。请注意，您的示例数据有17行，但查询仅返回10行。 - Dan Field

Answer 4

不清楚您想如何生成结果中的id字段。如果您想手动生成，则可以使用RANK()，如果您想从表格id值获取它，则可以使用max()或min()（取决于您期望的结果）。请使用RANK()Fiddle Demo Here。

尝试：

create table tt (id int null, dt varchar(8),count int)
insert tt values
(1,'01/2015',2),
(2,'01/2015',2),
(3,'02/2015',1),
(4,'03/2015',4),
(5,'03/2015',4),
(6,'03/2015',4),
(7,'03/2015',4)

查询：

 select count(id) as count,dt,RANK() 
    over(order by count(id)) as id from tt group by dt

编辑2： 或者你可以使用MAX()或MIN()

例如：

select count(id) as count,dt,Min(id) as id from tt group by dt

或者

select count(id) as count,dt,MAX(id) as id from tt group by dt

- A_Sk