如:
A B DATE5 3 2005-1-1
6 2 2005-1-2
3 3 2005-1-3
4 2 2005-1-8
1 7 2005-2-2
1 2 2005-2-8执行显示为:
A B DATE18 10 2005-1
2 9 2005-2
A B DATE5 3 2005-1-1
6 2 2005-1-2
3 3 2005-1-3
4 2 2005-1-8
1 7 2005-2-2
1 2 2005-2-8执行显示为:
A B DATE18 10 2005-1
2 9 2005-2
FROM TABLE
GROUP BY MONTH(DATE)
select a,b,date=convert(char(4),year(date'-'+convert(char(2),month(date))
from table1) ls
group by ls.date
FROM TABLE
GROUP BY convert(char(7),cast([DATE] as datetime),120)
SELECT SUM(A) AS A,SUM(B) AS B,convert(char(7),convert(datetime,[DATE],112),120) as [DATE]
FROM TABLE
GROUP BY convert(char(7),convert(datetime,[DATE],112),120)
表中数据:
A B DATE
1 3 2005-1-1 09:50
1 3 2005-1-2 09:50
1 3 2005-1-5 09:50
1 4 2005-1-5 09:50
2 5 2005-1-10 09:50
3 6 2005-2-10 09:50
2 7 2005-2-22 09:50
3 8 2005-3-1 09:50
2 9 2005-4-9 09:50
2 9 2005-4-14 09:50
1 10 2005-4-30 09:50
1 11 2005-6-5 09:50其实里面的A和B只是表示类别(A是B的父类)而已
我想算出每个月每一类产品同其父类中其他类别出现次数的比例
以A中1为例,A:1表示一类,在1月出现4四,其中B:3出现了3次,B:4出现1次,所以在1月显示B:3为75%,B:4为25%
每月B中的记录每个只出现一次
不知道说清楚没有
A B DATE RESULT1 3 2005-1 75%
1 4 2005-1 25%
2 5 2005-1 100%
3 6 2005-2 100%
2 7 2005-2 100%
3 8 2005-3 100%
2 9 2005-4 100%
1 10 2005-4 100%
1 11 2005-6 100%
CAST(CAST(COUNT(B) AS float) * 100 / CAST((SELECT COUNT(B) FROM tb_0120 WHERE A = a.A AND CONVERT(CHAR(7), [DATE], 120) = CONVERT(CHAR(7), a.DATE, 120)) AS float) AS VARCHAR(10)) + '%'
FROM tb_0120 a
GROUP BY A, B, CONVERT(CHAR(7), [DATE], 120)
select sum(a) as a,sum(b) as b,left(convert(vchar(10),DATA),6) as data from table1 group by left(convert(vchar(10),DATA),6)