从SQL计数数据 [英] count data from sql

问题描述

我需要计算来自sql的数据
可以，但是有问题.

我的数据在我的桌子上是这样的:

coldate(varcharmax)(数据来自datetimepicker短格式)
2010年2月23日
30.04.2010
15.04.2010

如何从sql中按月计数?

我尝试了这个:select * from datepart(month, coldate)=04

但它不起作用.

我需要按月份计数并将结果添加到label1.text

我该怎么办?

I need to count data from sql
It''s ok, but there is a problem.

My data is like this on my table:

coldate (varcharmax) (data comes datetimepicker short format)
23.02.2010
30.04.2010
15.04.2010

How do I count by month from sql?

I tried this: select * from datepart(month, coldate)=04

but it doesnt work.

I need to count by month and to add result to label1.text

What should I do?

推荐答案

您可能正在寻找GROUP BY以及一些区分不同年份中月份的方法.

该解决方案涵盖了两种情况(您关心的年份和您不关心的年份): SQL Hacks解决方案 [ ^ ]

干杯.

You are probably looking for GROUP BY and something to differentiate between months in different years.

This solution covers both scenarios (where you care about the year, and where you don''t): SQL Hacks Solution[^]

Cheers.

如何...

如果我有一个具有以下结构的表Table1:

CurrentDate

2010/5/2

3/6/2009

3/7/2008

4/5/2010

6/5/2010

7/5/2009

2/7/2001

2/9/2010

然后运行以下SQL语句:

How about this...

If I have a table called Table1 with the following structure:

CurrentDate

2/5/2010

3/6/2009

3/7/2008

4/5/2010

6/5/2010

7/5/2009

2/7/2001

2/9/2010

and I run the following SQL Statement:

SELECT Part1.MyMonth, Count(Part1.MyMonth) AS CountOfMyMonth
FROM (SELECT Month(CurrentDate) AS MyMonth
      FROM Table1) AS Part1
GROUP BY Part1.MyMonth;

我得到:

MyMonth	CountOfMyMonth
2	3
3	2
4	1
6	1
7	1

I get:

MyMonth	CountOfMyMonth
2	3
3	2
4	1
6	1
7	1

选择
子串(coldate，7,4)+''-''+子串(coldate，4,2)，
COUNT(*)
来自
TableName
按
分组 子串(coldate，7,4)+''-''+子串(coldate，4,2)
按1排序

select
Substring(coldate,7,4) + ''-'' + Substring(coldate,4,2),
COUNT(*)
from
TableName
group by
Substring(coldate,7,4) + ''-'' + Substring(coldate,4,2)
order by 1

这篇关于从SQL计数数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！