我有个表是这样的
字段1 字段2 字段3
x x
y y y
z z
y y
我要计算每一个字段 每种值有几个 需要的结果是
字段1 字段2 字段3
1 1 0 ------>x的个数
2 2 1 ------->Y的个数
1 0 1 ------->Z的个数
sql查询语句如何写呢....
字段1 字段2 字段3
x x
y y y
z z
y y
我要计算每一个字段 每种值有几个 需要的结果是
字段1 字段2 字段3
1 1 0 ------>x的个数
2 2 1 ------->Y的个数
1 0 1 ------->Z的个数
sql查询语句如何写呢....
if object_id('tempdb.dbo.#') is not null drop table #
create table #(C1 varchar(8), C2 varchar(8), C3 varchar(8))
insert into #
select 'x', 'x', null union all
select 'y', 'y', 'y' union all
select 'z', 'z', null union all
select 'y', 'y', nullselect
t=C1,
C1=sum(case g when 'C1' then 1 else 0 end),
C2=sum(case g when 'C2' then 1 else 0 end),
C3=sum(case g when 'C3' then 1 else 0 end)
from
(
select g='C1',C1 from # union all
select g='C2',C2 from # union all
select g='C3',C3 from #
) t
where C1 is not null group by C1/*
t C1 C2 C3
-------- ----------- ----------- -----------
x 1 1 0
y 2 2 1
z 1 1 0
*/
from
(select 字段1,sum(1) as ct from table1 group by 字段1) t1
full join
(select 字段2,sum(1) as ct from table1 group by 字段2) t2
on t1.字段1=t2.字段2
full join
(select 字段3,sum(1) as ct from table1 group by 字段3) t3
on t3.字段3=t2.字段2
if object_id('table1') is not null drop table table1
create table table1(字段1 varchar(8), 字段2 varchar(8), 字段3 varchar(8))
insert into table1
select 'x', 'x', null union all
select 'y', 'y', 'y' union all
select 'z', 'z', null union all
select 'y', 'y', null
select * from table1select isnull(t1.字段1,isnull(t2.字段2,t3.字段3)) as 字段,t1.ct as 字段1,t2.ct as 字段2,t3.ct as 字段3
from
(select 字段1,sum(1) as ct from table1 group by 字段1) t1
full join
(select 字段2,sum(1) as ct from table1 group by 字段2) t2
on t1.字段1=t2.字段2
full join
(select 字段3,sum(1) as ct from table1 group by 字段3) t3
on t3.字段3=t2.字段2--结果
(4 行受影响)
字段1 字段2 字段3
-------- -------- --------
x x NULL
y y y
z z NULL
y y NULL(4 行受影响)字段 字段1 字段2 字段3
-------- ----------- ----------- -----------
x 1 1 NULL
y 2 2 1
z 1 1 NULL
NULL NULL NULL 3(4 行受影响)
INSERT @TB SELECT 'X','x',''
UNION ALL SELECT 'Y','Y','Y'
UNION ALL SELECT 'Z','','Z'
UNION ALL SELECT 'Y','Y',''
SELECT
FD1=(SELECT COUNT(FD1) FROM @TB WHERE FD1=A.FD1),
FD2=(SELECT COUNT(FD2) FROM @TB WHERE FD2=A.FD1),
FD3=(SELECT COUNT(FD3) FROM @TB WHERE FD3=A.FD1)
FROM @TB A
GROUP BY FD1
/*
FD1 FD2 FD3
----------- ----------- -----------
1 1 0
2 2 1
1 0 1(所影响的行数为 3 行)
*/
if object_id('tempdb.dbo.#') is not null drop table #
create table #(C1 varchar(8), C2 varchar(8), C3 varchar(8))
insert into #
select 'x', 'x', null union all
select 'y', 'y', 'y' union all
select 'z', 'z', null union all
select 'y', 'y', null-- 2 要控制 full join 字段越多,匹配条件越难写,见b和c的匹配条件
select
t=coalesce(a.t,b.t,c.t),
C1=isnull(a.cn,0),
C2=isnull(b.cn,0),
C3=isnull(c.cn,0)
from
(select t=C1, cn=count(1) from # where C1 is not null group by C1) a
full join
(select t=C2, cn=count(1) from # where C2 is not null group by C2) b
on a.t=b.t
full join
(select t=C3, cn=count(1) from # where C3 is not null group by C3) c
on isnull(a.t,b.t)=c.t/*
t C1 C2 C3
-------- ----------- ----------- -----------
x 1 1 0
y 2 2 1
z 1 1 0
*/
INSERT @TB SELECT 'X','x',''
UNION ALL SELECT 'Y','Y','Y'
UNION ALL SELECT 'Z','','Z'
UNION ALL SELECT 'Y','Y',''
SELECT
FD=A.FD1,
FD1=(SELECT COUNT(FD1) FROM @TB WHERE FD1=A.FD1),
FD2=(SELECT COUNT(FD2) FROM @TB WHERE FD2=A.FD1),
FD3=(SELECT COUNT(FD3) FROM @TB WHERE FD3=A.FD1)
FROM @TB A
GROUP BY FD1/*
FD FD1 FD2 FD3
---------- ----------- ----------- -----------
X 1 1 0
Y 2 2 1
Z 1 0 1(所影响的行数为 3 行)*/
青锋大侠的匹配条件应该是有问题的,测试以下数据就明白了:if object_id('table1') is not null drop table table1
create table table1(字段1 varchar(8), 字段2 varchar(8), 字段3 varchar(8))
insert into table1
select 'x', 'x', null union all
select 'y', null, 'y' union all
select 'z', null, null union all
select 'y', null, null
declare @tb table([字段1] nvarchar(1),[字段2] nvarchar(1),[字段3] nvarchar(1))
Insert @tb
select N'x',N'x',N' ' union all
select N'y',N'y',N'y' union all
select N'z',N' ',N'z' union all
select N'y',N'y',N' '
Select isnull(sum(case when [字段1] ='x' then 1 end),0) as a,isnull(sum(case when [字段2] ='x' then 1 end),0) as b,isnull(sum(case when [字段3] ='x' then 1 end),0) as c from @tb
union all
Select isnull(sum(case when [字段1] ='y' then 1 end),0) as a,isnull(sum(case when [字段2] ='y' then 1 end),0) as b,isnull(sum(case when [字段3] ='y' then 1 end),0) as c from @tb
union all
Select isnull(sum(case when [字段1] ='z' then 1 end),0) as a,isnull(sum(case when [字段2] ='z' then 1 end),0) as b,isnull(sum(case when [字段3] ='z' then 1 end),0) as c from @tb
/*
a b c
----------- ----------- -----------
1 1 0
2 2 1
1 0 1
*/
只要GROUP BY下就好啊
select isnull(t.字段,t3.字段3),字段1,字段2,ct as 字段3 from (select isnull(t1.字段1,字段2) as 字段,t1.ct as 字段1,t2.ct as 字段2
from
(select 字段1,sum(1) as ct from table1 where 字段1 is not null group by 字段1) t1
full join
(select 字段2,sum(1) as ct from table1 where 字段2 is not null group by 字段2) t2
on t1.字段1=t2.字段2) t
full join
(select 字段3,sum(1) as ct from table1 where 字段3 is not null group by 字段3) t3
on t3.字段3=t.字段
INSERT @TB SELECT 'X','x',''
UNION ALL SELECT 'Y','Y','Y'
UNION ALL SELECT '','','Z'
UNION ALL SELECT 'Y','Y',''IF OBJECT_ID('TEMPDB..#TB') IS NOT NULL DROP TABLE #TB
CREATE TABLE #TB(FD VARCHAR(10))
INSERT INTO #TB
SELECT DISTINCT FD FROM
(
SELECT DISTINCT FD1 AS FD FROM @TB
UNION ALL SELECT DISTINCT FD2 AS FD FROM @TB
UNION ALL SELECT DISTINCT FD3 AS FD FROM @TB
) ASELECT
FD=A.FD,
FD1=(SELECT COUNT(FD1) FROM @TB WHERE FD1=A.FD),
FD2=(SELECT COUNT(FD2) FROM @TB WHERE FD2=A.FD),
FD3=(SELECT COUNT(FD3) FROM @TB WHERE FD3=A.FD)
FROM #TB A
LEFT JOIN @TB B
ON A.FD=B.FD1
WHERE A.FD<>''
GROUP BY A.FD/*
FD FD1 FD2 FD3
---------- ----------- ----------- -----------
X 1 1 0
Y 2 2 1
Z 0 0 1(所影响的行数为 3 行)
*/
declare @t table(字段1 varchar(10),字段2 varchar(10),字段3 varchar(10))
insert into @t
select 'x','x','' union all
select 'y','y','y' union all
select 'z','','z' union all
select 'y','y',''
select max('x'),sum(case 字段1 when 'x' then 1 else 0 end),sum(case 字段2 when 'x' then 1 else 0 end),sum(case 字段3 when 'x' then 1 else 0 end) from @t
union all
select max('y'),字段1=sum(case 字段1 when 'y' then 1 else 0 end),字段2=sum(case 字段2 when 'y' then 1 else 0 end),字段3=sum(case 字段3 when 'y' then 1 else 0 end) from @t
union all
select max('z'),字段1=sum(case 字段1 when 'z' then 1 else 0 end),字段2=sum(case 字段2 when 'z' then 1 else 0 end),字段3=sum(case 字段3 when 'z' then 1 else 0 end) from @t
FD FD1 FD2 FD3
---------- ----------- ----------- -----------
X 1 1 0
Y 2 2 1
Z 0 0 1
总计 3 3 2
怎么实现呢....
INSERT @TB
SELECT 'x','x','' UNION ALL
SELECT 'y','y','y' UNION ALL
SELECT 'z','', 'Z' UNION ALL
SELECT 'Y','Y',''select [值]='x',
[字段一]=sum(case when [字段一]='x' then 1 else 0 end),
[字段二]=sum(case when [字段二]='x' then 1 else 0 end),
[字段三]=sum(case when [字段三]='x' then 1 else 0 end)
from @tb
union all
select [值]='y',
[字段一]=sum(case when [字段一]='y' then 1 else 0 end),
[字段二]=sum(case when [字段二]='y' then 1 else 0 end),
[字段三]=sum(case when [字段三]='y' then 1 else 0 end)
from @tb
union all
select [值]='z',
[字段一]=sum(case when [字段一]='z' then 1 else 0 end),
[字段二]=sum(case when [字段二]='z' then 1 else 0 end),
[字段三]=sum(case when [字段三]='z' then 1 else 0 end)
from @tb
union all
select [值]='总计',
[字段一]=sum(case when [字段一]='x' or [字段一]='y' or [字段一]='z' then 1 else 0 end),
[字段二]=sum(case when [字段二]='x' or [字段二]='y' or [字段二]='z' then 1 else 0 end),
[字段三]=sum(case when [字段三]='x' or [字段三]='y' or [字段三]='z' then 1 else 0 end)
from @tb
FD1
FD2
FD3
FD4
这该怎么办啊~~~~~~
其实我最终的目的是这样的, 需要得到一个表 用水晶报表画柱状统计图, 之前的结果我忽然发觉 行列正好反一反, 水晶报表里又没有选项可以反过来..., (excel里倒是有的,很强大)...所以 搞到现在还没搞出来~~~另一个帖子我已经结了......这个帖子希望 高手们 再 帮帮忙~~~~~还有一个问题是,我想问,一般 你们用 水晶报表 做统计报表 是什么思路呢?
我现在就是用一个存储过程 把我需要的东西 写成一个表, 然后 再用报表去 读它 做成柱状图...请问一般是不是这样的??