(1)select 'a',count(fn) where fn='a' union .....
解决方案 »
- SQL SERVER 中怎么写筛选条件啊在收付表中有收付类型和交(提)货时间,想把收付类型为收款并且距离当前日期小于十天的合同找到,怎么写啊
- 请教如何对用户控件进行错误处理
- vb6 报表问题
- 如何用recordset返回不止一条的记录?
- asp(vbscript)写一个过程,如何定义后面几个为可选参数?
- 横向循环的问题!
- 我的程序是不停的向Text中写数据,因为Text容量有限(好象是64KB吧),写到一定的时候,Text中就不会发生变化了!我想在这个时候将Text1.Text="
- 在sql语句中:.....where 收费表.收费日期=#2003-5# 这样写对吗?
- 为什么VB6对话框中的文字变得又小又难看?
- 如何用ComboBOX控件或DBCombo控件来显示数据库某字段中的数据?
- 有谁知道mixerGetLineControls函数的详细用法,有范例最好
- ADODB.Connection是在需要时连接好,还是程序中一直连着好?
1、
select fn,count(*) as ccc from t1 group bu fn
2、
select 'a' as a,sum(case when fn='a' then 1 else 0 end) as ac
'b' as b,sum(case when fn='b' then 1 else 0 end) as bc,
'c' as c,sum(case when fn='c' then 1 else 0 end) as cc,
'd' as d,sum(case when fn='d' then 1 else 0 end) as dc,
'e' as e,sum(case when fn='e' then 1 else 0 end) as ec
from t1
1、
select fn,count(*) as ccc from t1 group bu fn
2、
select 'a' as a,sum(iif(fn='a',1,0)) as ac
'b' as b,sum(iif(fn='b',1,0)) as bc,
'c' as c,sum(iif(fn='c',1,0)) as cc,
'd' as d,sum(iif(fn='d',1,0)) as dc,
'e' as e,sum(iif(fn='e',1,0)) as ec
from t1
1、
select fn,count(fn) as ccc from t1 group bu fn;
2、
select 'a' as a,sum(decode(fn,'a',1,0)) as ac
'b' as b,sum(decode(fn,'b',1,0)) as bc,
'c' as c,sum(decode(fn,'c',1,0)) as cc,
'd' as d,sum(decode(fn,'d',1,0)) as dc,
'e' as e,sum(decode(fn,'e',1,0)) as ec
from t1
第一问一样,
第二问看:
http://www.csdn.net/expert/topic/508/508081.xml?temp=7.961673E-02
加上百分比统计:5行:
a,7,10%
b,3,XX%1行:
a,7,XX%,b,3,XX%,.....
过奖!
select fn,ccc,ccc/sum(ccc) as p from
(
select fn,count(*) as ccc from t1 group bu fn
) as a2、
select 'a' as a,sum(iif(fn='a',1,0)) as ac,sum(iif(fn='a',1,0))/count(*) as ap,
'b' as b,sum(iif(fn='b',1,0)) as bc,sum(iif(fn='b',1,0))/count(*) as bp,
'c' as c,sum(iif(fn='c',1,0)) as cc,sum(iif(fn='c',1,0))/count(*) as cp,
'd' as d,sum(iif(fn='d',1,0)) as dc,sum(iif(fn='d',1,0))/count(*) as dp,
'e' as e,sum(iif(fn='e',1,0)) as ec,sum(iif(fn='e',1,0))/count(*) as ep
from t1
再想想!
方法很多是很多,那个贴是比较通用的方法。
再想想!
1、
select fn,ccc,convert(varchar(10),(convert(numeric(10,2),ccc/sum(ccc)*100))+'%' as p from
(
select fn,count(*) as ccc from t1 group bu fn
) as a第二问也一样用convert格式化,格式化不一定要放在SQL 语句里,在客户端处理也不错。
谢谢了!
Select Fn,Count(*) as Nums From T1 Group by Fn第二问:
select 'a' as a,(Select Count(*) from T1 where Fn='a') as anum,'b' as b,(Select Count(*) from T1 where Fn='b') as bnum,'c' as c,(Select Count(*) from T1 where Fn='c') as cnum,'d' as d,(Select Count(*) from T1 where Fn='d') as dnum,'e' as e,(Select Count(*) from T1 where Fn='e') as enum第三问:
select fn,nums,cast(cast(nums/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5))+'%' from (Select Fn,Count(*) as Nums From T1 Group by Fn) as T2select 'a' as a,(Select Count(*) from T1 where Fn='a') as anum,
cast(cast(cast((Select Count(*) from T1 where Fn='a') as decimal(8,2))/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5)) + '%' as ap,
'b' as b,(Select Count(*) from T1 where Fn='b') as bnum,
cast(cast(cast((Select Count(*) from T1 where Fn='b') as decimal(8,2))/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5)) + '%' as bp,
'c' as c,(Select Count(*) from T1 where Fn='c') as cnum,
cast(cast(cast((Select Count(*) from T1 where Fn='c') as decimal(8,2))/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5)) + '%' as cp,
'd' as d,(Select Count(*) from T1 where Fn='d') as dnum,
cast(cast(cast((Select Count(*) from T1 where Fn='d') as decimal(8,2))/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5)) + '%' as dp,
'e' as e,(Select Count(*) from T1 where Fn='e') as enum,
cast(cast(cast((Select Count(*) from T1 where Fn='e') as decimal(8,2))/cast((Select Count(*) from T1) as decimal(8,2)) as decimal(8,2))*100 as nvarchar(5)) + '%' as ep