27,579
社区成员
发帖
与我相关
我的任务
分享
create table answer(
id int identity(1,1),
p_id int,
photo int,
p_type int,
c_name int,
e_name int,
o_name int
)
go
insert into answer values(235,0,1,0,2,0)
insert into answer values(236,0,1,1,1,0)
insert into answer values(237,0,2,0,2,1)
insert into answer values(238,0,1,0,1,2)
insert into answer values(239,0,1,2,0,2)
insert into answer values(240,0,0,0,1,1)
insert into answer values(241,0,0,0,2,2)
insert into answer values(242,0,0,0,1,0)
insert into answer values(243,0,1,0,2,1)
insert into answer values(244,0,2,1,2,0)
insert into answer values(245,0,0,0,0,2)
select p_id
,sum(case when photo =1 then 1 else 0 end
+case when p_type =1 then 1 else 0 end
+case when c_name =1 then 1 else 0 end
+case when e_name =1 then 1 else 0 end
+case when o_name =1 then 1 else 0 end) as '正确的'
,sum(case when photo =2 then 1 else 0 end
+case when p_type =2 then 1 else 0 end
+case when c_name =2 then 1 else 0 end
+case when e_name =2 then 1 else 0 end
+case when o_name =2 then 1 else 0 end) as '错误的'
from answer
group by p_id
p_id 正确的 错误的
----------- ----------- -----------
235 1 1
236 3 0
237 1 2
238 2 1
239 1 2
240 2 0
241 0 2
242 1 0
243 2 1
244 1 2
245 0 1
(11 行受影响)
create table answer(
id int identity(1,1),
p_id int,
photo int,
p_type int,
c_name int,
e_name int,
o_name int
)
go
insert into answer values(235,0,1,0,2,0)
insert into answer values(236,0,1,1,1,0)
insert into answer values(237,0,2,0,2,1)
insert into answer values(238,0,1,0,1,2)
insert into answer values(239,0,1,2,0,2)
insert into answer values(240,0,0,0,1,1)
insert into answer values(241,0,0,0,2,2)
insert into answer values(242,0,0,0,1,0)
insert into answer values(243,0,1,0,2,1)
insert into answer values(244,0,2,1,2,0)
insert into answer values(245,0,0,0,0,2)
declare @s varchar(8000)
select @s=ISNULL(@s+' union all select p_id,['+name+ '] from answer','select p_id,['+name+'] from answer')
from syscolumns
where OBJECT_ID('answer')=ID
and name not in('id','p_id','photo')
set @s='select p_id,
ltrim(sum(case p_type when 2 then 1 else 0 end))+''次'' [正确],
ltrim(sum(case p_type when 1 then 1 else 0 end))+''次'' [错误]
from ('+@s+') t
group by p_id'
exec(@s)
/*
p_id 正确 错误
----------- -------------- --------------
235 1次 1次
236 0次 3次
237 2次 1次
238 1次 2次
239 2次 1次
240 0次 2次
241 2次 0次
242 0次 1次
243 1次 2次
244 2次 1次
245 1次 0次
(11 行受影响)
*/
declare @str nvarchar(4000)
select @str=isnull(@str+' from answer union all ' +char(13)+' select p_id,',' select p_id,') + + [name] + ' as [type]' from syscolumns where name!= 'p_id' and name != 'id' and ID = object_id('answer')
select @str=@str+' from answer'
--print(@str)
exec('select p_id,
ltrim(sum(case type when 1 then 1 else 0 end))+''次'' as [right],
ltrim(sum(case type when 2 then 1 else 0 end))+''次'' as [wrong]
from ('+@str +') answer group by p_id
')
p_id right wrong
----------- -------------- --------------
235 1次 1次
236 3次 0次
237 1次 2次
238 2次 1次
239 1次 2次
240 2次 0次
241 0次 2次
242 1次 0次
243 2次 1次
244 1次 2次
245 0次 1次
declare @str nvarchar(4000)
select @str=isnull(@str+' from answer union all ' +char(13)+' select p_id,',' select p_id,') + + [name] + ' as [type]' from syscolumns where name!= 'p_id' and name != 'id' and ID = object_id('answer')
select @str=@str+' from answer'
print(@str)
exec('select p_id,
ltrim(sum(case type when 1 then 1 else 0 end))+''次'' as [right],
ltrim(sum(case type when 2 then 1 else 0 end))+''次'' as [wrong]
from ('+@str +') answer group by p_id
')
select p_id,c_name as [type] from answer union all
select p_id,e_name as [type] from answer union all
select p_id,o_name as [type] from answer union all
select p_id,p_type as [type] from answer union all
select p_id,photo as [type] from answer
p_id right wrong
----------- -------------- --------------
235 1次 1次
236 3次 0次
237 1次 2次
238 2次 1次
239 1次 2次
240 2次 0次
241 0次 2次
242 1次 0次
243 2次 1次
244 1次 2次
245 0次 1次
create table tb(
id int identity(1,1),
p_id int,
photo int,
p_type int,
c_name int,
e_name int,
o_name int
)
go
insert into tb values(235,0,1,0,2,0)
insert into tb values(236,0,1,1,1,0)
insert into tb values(237,0,2,0,2,1)
insert into tb values(238,0,1,0,1,2)
insert into tb values(239,0,1,2,0,2)
insert into tb values(240,0,0,0,1,1)
insert into tb values(241,0,0,0,2,2)
insert into tb values(242,0,0,0,1,0)
insert into tb values(243,0,1,0,2,1)
insert into tb values(244,0,2,1,2,0)
insert into tb values(245,0,0,0,0,2)
select * from tb
--drop table tb
select
p_id ,
sum(case id when 1 then 1 else 0 end) as '正确',
sum(case id when 2 then 1 else 0 end) as '错误'
from
(
select p_id,photo as id from tb
union all
select p_id,p_type as id from tb
union all
select p_id,c_name as id from tb
union all
select p_id,e_name as id from tb
union all
select p_id,o_name as id from tb
)t
group by
p_id
/*p_id 正确 错误
----------- ----------- -----------
235 1 1
236 3 0
237 1 2
238 2 1
239 1 2
240 2 0
241 0 2
242 1 0
243 2 1
244 1 2
245 0 1
(所影响的行数为 11 行)*/
create table answer(
id int identity(1,1),
p_id int,
photo int,
p_type int,
c_name int,
e_name int,
o_name int
)
go
insert into answer values(235,0,1,0,2,0)
insert into answer values(236,0,1,1,1,0)
insert into answer values(237,0,2,0,2,1)
insert into answer values(238,0,1,0,1,2)
insert into answer values(239,0,1,2,0,2)
insert into answer values(240,0,0,0,1,1)
insert into answer values(241,0,0,0,2,2)
insert into answer values(242,0,0,0,1,0)
insert into answer values(243,0,1,0,2,1)
insert into answer values(244,0,2,1,2,0)
insert into answer values(245,0,0,0,0,2)
select p_id,
ltrim(sum(case type when 1 then 1 else 0 end))+'次' as [right],
ltrim(sum(case type when 2 then 1 else 0 end))+'次' as [wrong]
from
(
select p_id,p_type type from answer union all
select p_id,c_name from answer union all
select p_id,e_name from answer union all
select p_id,o_name from answer
) tb
group by p_id
p_id right wrong
----------- -------------- --------------
235 1次 1次
236 3次 0次
237 1次 2次
238 2次 1次
239 1次 2次
240 2次 0次
241 0次 2次
242 1次 0次
243 2次 1次
244 1次 2次
245 0次 1次
(所影响的行数为 11 行)
刚反了select p_id,
ltrim(sum(case type when 2 then 1 else 0 end))+'次' as [right],
ltrim(sum(case type when 1 then 1 else 0 end))+'次' as [wrong]
from
(
select p_id,p_type type from answer union all
select p_id,c_name from answer union all
select p_id,e_name from answer union all
select p_id,o_name from answer
) tb
group by p_id
p_id right wrong
----------- -------------- --------------
235 1次 1次
236 0次 3次
237 2次 1次
238 1次 2次
239 2次 1次
240 0次 2次
241 2次 0次
242 0次 1次
243 1次 2次
244 2次 1次
245 1次 0次
(所影响的行数为 11 行)
select
p_id ,
sum(case id when 1 then 1 else 0 end) as '正确',
sum(case id when 2 then 1 else 0 end) as '错误'
from
(
select photo as id from tb
union all
select p_type as id from tb
union all
select c_name as id from tb
union all
select e_name as id from tb
union all
select o_name as id from tb
)t
group by
p_id