有一组数据是这样:
2012-1-10
2012-1-15
2012-1-20
2012-2-20
2012-2-25
2012-3-5
2012-3-6
现在我想以上这些数据合并成这样:
月份 数量
1月 3
2月 2
3月 2
求高手,分数不是问题,可以追加。麻烦了
18 个解决方案
#1
select rtrim(month(time))+'月' as [month],count(1) from tb group by month(time)
#2
select MONTH(time),COUNT(*) from aaa group by MONTH(time)
#3
#4
select rtrim(month(time))+'月' as [month],count(*) as ttCOunt from tb group by month(time) order by ttCOunt desc
#5
分组求和
前面已经贴出很多代码了,
在这里就不在贴了
前面已经贴出很多代码了,
在这里就不在贴了
#6
select rtrim(MONTH(SignDate))+'月' tmonth,COUNT(*) countnum from MISApp_TemporaryAccoutnOpeningContract group by MONTH(SignDate)
#7
如果仅仅是学习sql语法,请最好到相关的论坛。流下空间给.net问题。
#8
版本多多,再接再厉
#9
关键我认为高.NET的一般都用sql,所以在这里请教了。多谢各位了,我研究下
#10
牛人遍地。
#12
select rtrim(MONTH(月份字段名))+'月',COUNT(*) from yh group by MONTH(月份字段名)
select rtrim(month(月份字段名))+'月' as [month],count(*) as ttCOunt from yh group by month(月份字段名) order by month asc
2楼4楼的学习了。
select rtrim(month(月份字段名))+'月' as [month],count(*) as ttCOunt from yh group by month(月份字段名) order by month asc
2楼4楼的学习了。
#13
--有一组数据是这样:
--2012-1-10
--2012-1-15
--2012-1-20
--2012-2-20
--2012-2-25
--2012-3-5
--2012-3-6
--现在我想以上这些数据合并成这样:
--月份 数量
--1月 3
--2月 2
--3月 2
DECLARE @T TABLE (D DATETIME)
INSERT INTO @T
SELECT '2012-1-10' UNION
SELECT '2012-1-15' UNION
SELECT '2012-1-20' UNION
SELECT '2012-2-20' UNION
SELECT '2012-2-25' UNION
SELECT '2012-3-5' UNION
SELECT '2012-3-6'
--注:必须是年月,只用月份是无法区分出年份的!
SELECT CONVERT(VARCHAR(7), D, 23) AS 年月,
COUNT(1) AS 数量
FROM @T
GROUP BY
CONVERT(VARCHAR(7), D, 23)
#14
好的,下次我直接进SQL模块发帖,谢谢了
#15
得了吧你,在asp.net版块连发仨贴都是问sql的
#16
SELECT
ISNULL(SUM([Jan]), 0) AS '1月',
ISNULL(SUM([Feb]), 0) AS '2月',
ISNULL(SUM([Mar]), 0) AS '3月',
ISNULL(SUM([Apr]), 0) AS '4月',
ISNULL(SUM([May]), 0) AS '5月',
ISNULL(SUM([Jun]), 0) AS '6月',
ISNULL(SUM([Jul]), 0) AS '7月',
ISNULL(SUM([Aug]), 0) AS '8月',
ISNULL(SUM([Sep]), 0) AS '9月',
ISNULL(SUM([Oct]), 0) AS '10月',
ISNULL(SUM([Nov]), 0) AS '11月',
ISNULL(SUM([Dec]), 0) AS '12月'
FROM ( SELECT
CASE DATEPART(mm,billdate) WHEN '1' THEN count(billdate) ELSE 0 END AS 'Jan',
CASE DATEPART(mm,billdate) WHEN '2' THEN count(billdate) ELSE 0 END AS 'Feb',
CASE DATEPART(mm,billdate) WHEN '3' THEN count(billdate) ELSE 0 END AS 'Mar',
CASE DATEPART(mm,billdate) WHEN '4' THEN count(billdate) ELSE 0 END AS 'Apr',
CASE DATEPART(mm,billdate) WHEN '5' THEN count(billdate) ELSE 0 END AS 'May',
CASE DATEPART(mm,billdate) WHEN '6' THEN count(billdate) ELSE 0 END AS 'Jun',
CASE DATEPART(mm,billdate) WHEN '7' THEN count(billdate) ELSE 0 END AS 'Jul',
CASE DATEPART(mm,billdate) WHEN '8' THEN count(billdate) ELSE 0 END AS 'Aug',
CASE DATEPART(mm,billdate) WHEN '9' THEN count(billdate) ELSE 0 END AS 'Sep',
CASE DATEPART(mm,billdate) WHEN '10' THEN count(billdate) ELSE 0 END AS 'Oct',
CASE DATEPART(mm,billdate) WHEN '11' THEN count(billdate) ELSE 0 END AS 'Nov',
CASE DATEPART(mm,billdate) WHEN '12' THEN count(billdate) ELSE 0 END AS 'Dec'
FROM test GROUP BY billdate) T
ISNULL(SUM([Jan]), 0) AS '1月',
ISNULL(SUM([Feb]), 0) AS '2月',
ISNULL(SUM([Mar]), 0) AS '3月',
ISNULL(SUM([Apr]), 0) AS '4月',
ISNULL(SUM([May]), 0) AS '5月',
ISNULL(SUM([Jun]), 0) AS '6月',
ISNULL(SUM([Jul]), 0) AS '7月',
ISNULL(SUM([Aug]), 0) AS '8月',
ISNULL(SUM([Sep]), 0) AS '9月',
ISNULL(SUM([Oct]), 0) AS '10月',
ISNULL(SUM([Nov]), 0) AS '11月',
ISNULL(SUM([Dec]), 0) AS '12月'
FROM ( SELECT
CASE DATEPART(mm,billdate) WHEN '1' THEN count(billdate) ELSE 0 END AS 'Jan',
CASE DATEPART(mm,billdate) WHEN '2' THEN count(billdate) ELSE 0 END AS 'Feb',
CASE DATEPART(mm,billdate) WHEN '3' THEN count(billdate) ELSE 0 END AS 'Mar',
CASE DATEPART(mm,billdate) WHEN '4' THEN count(billdate) ELSE 0 END AS 'Apr',
CASE DATEPART(mm,billdate) WHEN '5' THEN count(billdate) ELSE 0 END AS 'May',
CASE DATEPART(mm,billdate) WHEN '6' THEN count(billdate) ELSE 0 END AS 'Jun',
CASE DATEPART(mm,billdate) WHEN '7' THEN count(billdate) ELSE 0 END AS 'Jul',
CASE DATEPART(mm,billdate) WHEN '8' THEN count(billdate) ELSE 0 END AS 'Aug',
CASE DATEPART(mm,billdate) WHEN '9' THEN count(billdate) ELSE 0 END AS 'Sep',
CASE DATEPART(mm,billdate) WHEN '10' THEN count(billdate) ELSE 0 END AS 'Oct',
CASE DATEPART(mm,billdate) WHEN '11' THEN count(billdate) ELSE 0 END AS 'Nov',
CASE DATEPART(mm,billdate) WHEN '12' THEN count(billdate) ELSE 0 END AS 'Dec'
FROM test GROUP BY billdate) T
#17
数据库表
id name billdate
1 NULL 2012-01-10 00:00:00.000
2 NULL 2012-01-15 00:00:00.000
3 NULL 2012-01-20 00:00:00.000
4 NULL 2012-02-20 00:00:00.000
5 NULL 2012-02-25 00:00:00.000
6 NULL 2012-03-05 00:00:00.000
7 NULL 2012-03-06 00:00:00.000
NULL NULL NULL
id name billdate
1 NULL 2012-01-10 00:00:00.000
2 NULL 2012-01-15 00:00:00.000
3 NULL 2012-01-20 00:00:00.000
4 NULL 2012-02-20 00:00:00.000
5 NULL 2012-02-25 00:00:00.000
6 NULL 2012-03-05 00:00:00.000
7 NULL 2012-03-06 00:00:00.000
NULL NULL NULL
#18
运行结果图
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
3 2 2 0 0 0 0 0 0 0 0 0
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
3 2 2 0 0 0 0 0 0 0 0 0
#1
select rtrim(month(time))+'月' as [month],count(1) from tb group by month(time)
#2
select MONTH(time),COUNT(*) from aaa group by MONTH(time)
#3
#4
select rtrim(month(time))+'月' as [month],count(*) as ttCOunt from tb group by month(time) order by ttCOunt desc
#5
分组求和
前面已经贴出很多代码了,
在这里就不在贴了
前面已经贴出很多代码了,
在这里就不在贴了
#6
select rtrim(MONTH(SignDate))+'月' tmonth,COUNT(*) countnum from MISApp_TemporaryAccoutnOpeningContract group by MONTH(SignDate)
#7
如果仅仅是学习sql语法,请最好到相关的论坛。流下空间给.net问题。
#8
版本多多,再接再厉
#9
关键我认为高.NET的一般都用sql,所以在这里请教了。多谢各位了,我研究下
#10
牛人遍地。
#11
#12
select rtrim(MONTH(月份字段名))+'月',COUNT(*) from yh group by MONTH(月份字段名)
select rtrim(month(月份字段名))+'月' as [month],count(*) as ttCOunt from yh group by month(月份字段名) order by month asc
2楼4楼的学习了。
select rtrim(month(月份字段名))+'月' as [month],count(*) as ttCOunt from yh group by month(月份字段名) order by month asc
2楼4楼的学习了。
#13
--有一组数据是这样:
--2012-1-10
--2012-1-15
--2012-1-20
--2012-2-20
--2012-2-25
--2012-3-5
--2012-3-6
--现在我想以上这些数据合并成这样:
--月份 数量
--1月 3
--2月 2
--3月 2
DECLARE @T TABLE (D DATETIME)
INSERT INTO @T
SELECT '2012-1-10' UNION
SELECT '2012-1-15' UNION
SELECT '2012-1-20' UNION
SELECT '2012-2-20' UNION
SELECT '2012-2-25' UNION
SELECT '2012-3-5' UNION
SELECT '2012-3-6'
--注:必须是年月,只用月份是无法区分出年份的!
SELECT CONVERT(VARCHAR(7), D, 23) AS 年月,
COUNT(1) AS 数量
FROM @T
GROUP BY
CONVERT(VARCHAR(7), D, 23)
#14
好的,下次我直接进SQL模块发帖,谢谢了
#15
得了吧你,在asp.net版块连发仨贴都是问sql的
#16
SELECT
ISNULL(SUM([Jan]), 0) AS '1月',
ISNULL(SUM([Feb]), 0) AS '2月',
ISNULL(SUM([Mar]), 0) AS '3月',
ISNULL(SUM([Apr]), 0) AS '4月',
ISNULL(SUM([May]), 0) AS '5月',
ISNULL(SUM([Jun]), 0) AS '6月',
ISNULL(SUM([Jul]), 0) AS '7月',
ISNULL(SUM([Aug]), 0) AS '8月',
ISNULL(SUM([Sep]), 0) AS '9月',
ISNULL(SUM([Oct]), 0) AS '10月',
ISNULL(SUM([Nov]), 0) AS '11月',
ISNULL(SUM([Dec]), 0) AS '12月'
FROM ( SELECT
CASE DATEPART(mm,billdate) WHEN '1' THEN count(billdate) ELSE 0 END AS 'Jan',
CASE DATEPART(mm,billdate) WHEN '2' THEN count(billdate) ELSE 0 END AS 'Feb',
CASE DATEPART(mm,billdate) WHEN '3' THEN count(billdate) ELSE 0 END AS 'Mar',
CASE DATEPART(mm,billdate) WHEN '4' THEN count(billdate) ELSE 0 END AS 'Apr',
CASE DATEPART(mm,billdate) WHEN '5' THEN count(billdate) ELSE 0 END AS 'May',
CASE DATEPART(mm,billdate) WHEN '6' THEN count(billdate) ELSE 0 END AS 'Jun',
CASE DATEPART(mm,billdate) WHEN '7' THEN count(billdate) ELSE 0 END AS 'Jul',
CASE DATEPART(mm,billdate) WHEN '8' THEN count(billdate) ELSE 0 END AS 'Aug',
CASE DATEPART(mm,billdate) WHEN '9' THEN count(billdate) ELSE 0 END AS 'Sep',
CASE DATEPART(mm,billdate) WHEN '10' THEN count(billdate) ELSE 0 END AS 'Oct',
CASE DATEPART(mm,billdate) WHEN '11' THEN count(billdate) ELSE 0 END AS 'Nov',
CASE DATEPART(mm,billdate) WHEN '12' THEN count(billdate) ELSE 0 END AS 'Dec'
FROM test GROUP BY billdate) T
ISNULL(SUM([Jan]), 0) AS '1月',
ISNULL(SUM([Feb]), 0) AS '2月',
ISNULL(SUM([Mar]), 0) AS '3月',
ISNULL(SUM([Apr]), 0) AS '4月',
ISNULL(SUM([May]), 0) AS '5月',
ISNULL(SUM([Jun]), 0) AS '6月',
ISNULL(SUM([Jul]), 0) AS '7月',
ISNULL(SUM([Aug]), 0) AS '8月',
ISNULL(SUM([Sep]), 0) AS '9月',
ISNULL(SUM([Oct]), 0) AS '10月',
ISNULL(SUM([Nov]), 0) AS '11月',
ISNULL(SUM([Dec]), 0) AS '12月'
FROM ( SELECT
CASE DATEPART(mm,billdate) WHEN '1' THEN count(billdate) ELSE 0 END AS 'Jan',
CASE DATEPART(mm,billdate) WHEN '2' THEN count(billdate) ELSE 0 END AS 'Feb',
CASE DATEPART(mm,billdate) WHEN '3' THEN count(billdate) ELSE 0 END AS 'Mar',
CASE DATEPART(mm,billdate) WHEN '4' THEN count(billdate) ELSE 0 END AS 'Apr',
CASE DATEPART(mm,billdate) WHEN '5' THEN count(billdate) ELSE 0 END AS 'May',
CASE DATEPART(mm,billdate) WHEN '6' THEN count(billdate) ELSE 0 END AS 'Jun',
CASE DATEPART(mm,billdate) WHEN '7' THEN count(billdate) ELSE 0 END AS 'Jul',
CASE DATEPART(mm,billdate) WHEN '8' THEN count(billdate) ELSE 0 END AS 'Aug',
CASE DATEPART(mm,billdate) WHEN '9' THEN count(billdate) ELSE 0 END AS 'Sep',
CASE DATEPART(mm,billdate) WHEN '10' THEN count(billdate) ELSE 0 END AS 'Oct',
CASE DATEPART(mm,billdate) WHEN '11' THEN count(billdate) ELSE 0 END AS 'Nov',
CASE DATEPART(mm,billdate) WHEN '12' THEN count(billdate) ELSE 0 END AS 'Dec'
FROM test GROUP BY billdate) T
#17
数据库表
id name billdate
1 NULL 2012-01-10 00:00:00.000
2 NULL 2012-01-15 00:00:00.000
3 NULL 2012-01-20 00:00:00.000
4 NULL 2012-02-20 00:00:00.000
5 NULL 2012-02-25 00:00:00.000
6 NULL 2012-03-05 00:00:00.000
7 NULL 2012-03-06 00:00:00.000
NULL NULL NULL
id name billdate
1 NULL 2012-01-10 00:00:00.000
2 NULL 2012-01-15 00:00:00.000
3 NULL 2012-01-20 00:00:00.000
4 NULL 2012-02-20 00:00:00.000
5 NULL 2012-02-25 00:00:00.000
6 NULL 2012-03-05 00:00:00.000
7 NULL 2012-03-06 00:00:00.000
NULL NULL NULL
#18
运行结果图
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
3 2 2 0 0 0 0 0 0 0 0 0
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
3 2 2 0 0 0 0 0 0 0 0 0