try itselect count(subyearmonth) as countmonth,count(subjyear) as countyear,subjdbsc from table group by subjdbsc order by id desc
解决方案 »
- TablePanel太多一行放不下怎么办
- 关于c# post登陆的问题,郁闷啊
- 动态加载用户控件的问题
- TREEVIEW的数据刷新问题。。。。。
- 有谁知道邵志东老师的E-mail
- 如何限制文本只输数字不能输字母和汉字?
- 一个超现实的问题,csdn上很多人问,但一直没有很好解答!高手进来看看!
- 父窗口调用iframe中数据的问题,把Iframe的數據傳囘父頁面(需然有很多朋友问过了,但是我试过都不行啊)
- 微软提供的TREEVIEW,难道就这么垃圾,这几天快气死我了,真没有别的办法解决闪烁了吗
- 水晶报表:估计是重装IIS后,无法注册水晶报表了,如何在IIS里重新注册水晶报表??
- 在ASP.NET中如何将XML中的数据邦订到DataGrid上。
- 请教关于 AD 验证的问题
edobnet(oоОoоО)
这样不行的,统计出来的年数据和月数据一样了
yohomonkey(关在笼子里的猴)
小弟不才,能不能给个例子
select count(distinct subyearmonth) as countmonth,count(distinct subjyear) as countyear,subjdbsc from table group by subjdbsc order by id desc
countmonth countyear subjdbsc
2 3 ww
问题:2---是2003-07有2个还是有两种不同的月份,还是取相同月份的最大值可以用UNION实现!
取完数据后生成统计图
取完数据后生成统计图
安年:
ww有3条,qq有两条
安月:
2003-07:
ww有两条,qq就一条
Right?
排序的你自己改改吧。
if EXISTS( SELECT name,type FROM sysobjects
where name='t'and type='U')
begin
drop table t
end
create table t ( countmonth nvarchar(50),countyear nvarchar(50),subjdbsc nvarchar(50))declare @subjdbsc nvarchar(50)declare @CounYM nvarchar(10)
declare @CounY nvarchar(10)
declare crColumns cursor for
select distinct subjdbsc from table2
open crColumns
fetch next from crColumns into @subjdbsc
while (@@Fetch_status<>-1)
begin
if (@@Fetch_status<>-2)
begin
select @CounYM= count(subyearmonth) from table2 where subjdbsc=@subjdbsc and subyearmonth='2003-07'
select @CounY=count(subyear) from table2 where subjdbsc=@subjdbsc and subyear='2003'
insert into t (countmonth ,countyear,subjdbsc)
values(@CounYM,@CounY,@subjdbsc)
end
fetch next from crColumns into @subjdbsc
end
close crColumns
deallocate crColumnsselect * from t
依稀记得这个应该属于矩阵转置之类的问题(PIVOT),具体方法还要回头查一查!
DECLARE @year AS char(4)
DECLARE @month AS char(7)
--SET @year = '2003'
--SET @month = '2003-07'
SELECT SUM(CASE pyear WHEN @year THEN 1 ELSE 0 end )AS countpyear,
SUM(CASE pmonth WHEN @month THEN 1 ELSE 0 end)
AS countmonth, puser
FROM tt
GROUP BY puser
ORDER BY countpyear
create procedure sp @ym varchar(7) = '2003-07'
asselect subjdbsc,
sum(case when subyear = left(@ym,4) then 1 else 0 end) countyear,
sum(case when subyearmonth = @ym then 1 else 0 end) countyearmonth
from talbename
group by subjdbsc
不知楼主想要的结果是否这样:
id title subjdbsc subyear subyearmonth
1 qw qq 2003 2003-06
2 we ww 2003 2003-06
3 rt qq 2003 2003-07
4 ty ww 2003 2003-07
5 er ww 2003 2003-07
6 qw ww 2002 2003-07
7 we ww 2002 2003-05统计的得到的结果:
subjdbc countyear countmonth
ww 2 3
qq 1 2如果是的话可参考以下的SQL语句(直接COPY到SQL查询分析器运行即可)/*
CREATE TABLE table1(
[id] int,
title varchar(10),
subjdbsc varchar(10),
subyear varchar(10),
subyearmonth varchar(10)
)INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(1,'qw','qq','2003','2003-06')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(2,'we','ww','2003','2003-06')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(3,'rt','qq','2003','2003-07')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(4,'ty','ww','2003','2003-07')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(5,'er','ww','2003','2003-07')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(6,'qw','ww','2002','2003-07')
INSERT table1([id],title, subjdbsc, subyear, subyearmonth) VALUES(7,'we','ww','2002','2003-05')
*/SELECT a.subjdbsc,
(SELECT COUNT(*) FROM (SELECT subyear FROM table1
WHERE subjdbsc = a.subjdbsc
GROUP BY subyear) b) AS countyear,
(SELECT COUNT(*) FROM (SELECT subyearmonth FROM table1
WHERE subjdbsc = a.subjdbsc
GROUP BY subyearmonth) c) AS countmonth
FROM table1 a
GROUP BY subjdbsc
ORDER BY countmonth desc,countyear desc
create function stat(@year char(4), @month char(2))
returns table
as
return
select subjdbsc,
yearqty=(select count(1) from table1 where subyear=@year and subjdbsc=t.subjdbsc),
monthqty=(select count(1) from table1 where subyearmonth=@year+'-'+@month and subjdbsc=t.subjdbsc)
from table1 t group by subjdbsc查询语句:
select * from dbo.stat('2003', '07')
可以改为:
from table1 t where subyear=@year group by subjdbsc
以避免非计算非本年数据