TABLE1:
id name lev1 lev2 lev3
-- ---- ---- ---- ----
1 A TRUE FALSE FALSE
2 B TRUE FALSE FALSE
3 A FALSE TRUE FALSE
4 D TRUE FALSE FALSE
5 C TRUE FALSE FALSE
6 D
7 A
8 B
.
.
.
.
.
.
.每个人 只能得到一个TRUE ,就是说 针对每条记录,LEV1 LEV2 LEV3 只可能有1个是TURE 现在要统计每个人: LEV1 得到几个TRUE ,LEV2 得到几个TRUE,LEV3得到几个 TURE
id name lev1 lev2 lev3
-- ---- ---- ---- ----
1 A TRUE FALSE FALSE
2 B TRUE FALSE FALSE
3 A FALSE TRUE FALSE
4 D TRUE FALSE FALSE
5 C TRUE FALSE FALSE
6 D
7 A
8 B
.
.
.
.
.
.
.每个人 只能得到一个TRUE ,就是说 针对每条记录,LEV1 LEV2 LEV3 只可能有1个是TURE 现在要统计每个人: LEV1 得到几个TRUE ,LEV2 得到几个TRUE,LEV3得到几个 TURE
name,
(case lev1 when 'TRUE' then 1 else 0 end) as lev1,
(case lev2 when 'TRUE' then 1 else 0 end) as lev2,
(case lev3 when 'TRUE' then 1 else 0 end) as lev3
from
TABLE1
group by
name
insert into @t1 select 1,'A','TRUE' ,'FALSE','FALSE'
insert into @t1 select 2,'B','TRUE' ,'FALSE','FALSE'
insert into @t1 select 3,'A','FALSE','TRUE' ,'FALSE'
insert into @t1 select 4,'D','TRUE' ,'FALSE','FALSE'
insert into @t1 select 5,'C','TRUE' ,'FALSE','FALSE'select
name,
sum(case lev1 when 'TRUE' then 1 else 0 end) as lev1,
sum(case lev2 when 'TRUE' then 1 else 0 end) as lev2,
sum(case lev3 when 'TRUE' then 1 else 0 end) as lev3
from
@t1
group by
name/*
name lev1 lev2 lev3
------ ----------- ----------- -----------
A 1 1 0
B 1 0 0
C 1 0 0
D 1 0 0
*/
select id,name
,sum(case lev1 when 'true' then 1 else 0 end) as lev1数量
,sum(case lev2 when 'true' then 1 else 0 end) as lev2数量
,sum(case lev3 when 'true' then 1 else 0 end) as lev3数量
from t
group by id,name
Select
Name,
LEV1 = SUM(Case LEV1 When 'TRUE' Then 1 Else 0 End),
LEV2 = SUM(Case LEV2 When 'TRUE' Then 1 Else 0 End),
LEV3 = SUM(Case LEV3 When 'TRUE' Then 1 Else 0 End)
From TABLE1
Group By Name
select name
,sum(case lev1 when 'true' then 1 else 0 end) as lev1数量
,sum(case lev2 when 'true' then 1 else 0 end) as lev2数量
,sum(case lev3 when 'true' then 1 else 0 end) as lev3数量
from t
group by name
name,
sum(case lev1 when 'TRUE' then 1 else 0 end) as lev1,
sum(case lev2 when 'TRUE' then 1 else 0 end) as lev2,
sum(case lev3 when 'TRUE' then 1 else 0 end) as lev3
from
TABLE1
group by
name
(id Int,
name Char(1),
lev1 Varchar(10),
lev2 Varchar(10),
lev3 Varchar(10)
)
Insert TABLE1 Select 1, 'A', 'TRUE', 'FALSE', 'FALSE'
Union All Select 2, 'B', 'TRUE', 'FALSE', 'FALSE'
Union All Select 3, 'A', 'FALSE', 'TRUE', 'FALSE'
Union All Select 4, 'D', 'TRUE', 'FALSE', 'FALSE'
Union All Select 5, 'C', 'TRUE', 'FALSE', 'FALSE'
GO
Select
Name,
LEV1 = SUM(Case LEV1 When 'TRUE' Then 1 Else 0 End),
LEV2 = SUM(Case LEV2 When 'TRUE' Then 1 Else 0 End),
LEV3 = SUM(Case LEV3 When 'TRUE' Then 1 Else 0 End)
From TABLE1
Group By Name
Go
Drop Table TABLE1
--Result
/*
Name LEV1 LEV2 LEV3
A 1 1 0
B 1 0 0
C 1 0 0
D 1 0 0
*/
(id Int,
name Char(1),
lev1 Varchar(10),
lev2 Varchar(10),
lev3 Varchar(10)
)
Insert TABLE1 Select 1, 'A', 'TRUE', 'FALSE', 'FALSE'
Union All Select 2, 'B', 'TRUE', 'FALSE', 'FALSE'
Union All Select 3, 'A', 'FALSE', 'TRUE', 'FALSE'
Union All Select 4, 'D', 'TRUE', 'FALSE', 'FALSE'
Union All Select 5, 'C', 'TRUE', 'FALSE', 'FALSE'
GO
Select
Name,
LEV1 = (Select Count(*) From TABLE1 Where LEV1 = 'TRUE' And Name = A.Name),
LEV2 = (Select Count(*) From TABLE1 Where LEV2 = 'TRUE' And Name = A.Name),
LEV3 = (Select Count(*) From TABLE1 Where LEV3 = 'TRUE' And Name = A.Name)
From TABLE1 A
Group By Name
Go
Drop Table TABLE1
--Result
/*
Name LEV1 LEV2 LEV3
A 1 1 0
B 1 0 0
C 1 0 0
D 1 0 0
*/
create table table1(id int,name varchar(6),lev1 varchar(6),lev2 varchar(6),lev3 varchar(6))
insert into table1 select 1,'A','TRUE' ,'FALSE','FALSE'
insert into table1 select 2,'B','TRUE' ,'FALSE','FALSE'
insert into table1 select 3,'A','FALSE','TRUE' ,'FALSE'
insert into table1 select 4,'D','TRUE' ,'FALSE','FALSE'
insert into table1 select 5,'C','TRUE' ,'FALSE','FALSE'select name,
count(case when lev1='true' then 'true' end)lev1,
count(case when lev2='true' then 'true' end)lev2,
count(case when lev3='true' then 'true' end)lev3
from table1 group by name
name,
sum(iif(lev1='TRUE',1,0)) as lev1,
sum(iif(lev2='TRUE',1,0)) as lev2,
sum(iif(lev3='TRUE',1,0)) as lev3
from
@t1
group by
name
name,
sum(iif(lev1='TRUE',1,0)) as lev1,
sum(iif(lev2='TRUE',1,0)) as lev2,
sum(iif(lev3='TRUE',1,0)) as lev3
from
TABLE1
group by
name
SUM(IIF(lev1='TRUE',1,0)) AS lev1,
SUM(IIF(lev2='TRUE',1,0)) AS lev2,
SUM(IIF(lev3='TRUE',1,0)) AS lev3
FROM TABLE1
GROUP BY NAME ORDER BY NAME
select
name,
sum(iif(lev1='TRUE',1,0)) as lev1,
sum(iif(lev2='TRUE',1,0)) as lev2,
sum(iif(lev3='TRUE',1,0)) as lev3
from
TABLE1
group by
name后,提示:在查询定义的SELECT列表中,别名 ‘LEV1’循环引用
这是怎么回事啊?
但是出现新的错误:标准表达式中,数据类型不匹配
应该是 select
name,
sum(iif(lev1=TRUE,1,0)) as lev11,
sum(iif(lev2=TRUE,1,0)) as lev22,
sum(iif(lev3=TRUE,1,0)) as lev33
from
TABLE1
group by
name
id varchar2(10),
name varchar2(10),
lev1 varchar2(10),
lev2 varchar2(10),
lev3 varchar2(10));insert into table1 values('1','A','TRUE ','FALSE','FALSE');
insert into table1 values('2','B','TRUE ','FALSE','FALSE');
insert into table1 values('3','A','FALSE','TRUE ','FALSE');
insert into table1 values('4','D','TRUE ','FALSE','FALSE');
insert into table1 values('5','C','TRUE ','FALSE','FALSE');
select name
,sum(case lev1 when 'true' then 1 else 0 end) as lev1数量
,sum(case lev2 when 'true' then 1 else 0 end) as lev2数量
,sum(case lev3 when 'true' then 1 else 0 end) as lev3数量
from table1
group by name