有一表 A字段:问卷编号 问题编号 问题答案 答题人编号
23 51(是否喝过红酒) 1(是) 1
23 52 (喜欢喝什么红酒) 21(干红) 1
23 52 (喜欢喝什么红酒) 22(甜红) 1
23 51 1(是) 2
23 52 21(干红) 2
23 53 23(白兰地) 2需要得到喝过红酒并且喜欢喝白兰地的人 也就是 编号为2字段:问卷编号 问题编号 问题答案 答题人编号
23 51 1(是) 2
23 52 21(干红) 2
23 53 23(白兰地) 2
23 51(是否喝过红酒) 1(是) 1
23 52 (喜欢喝什么红酒) 21(干红) 1
23 52 (喜欢喝什么红酒) 22(甜红) 1
23 51 1(是) 2
23 52 21(干红) 2
23 53 23(白兰地) 2需要得到喝过红酒并且喜欢喝白兰地的人 也就是 编号为2字段:问卷编号 问题编号 问题答案 答题人编号
23 51 1(是) 2
23 52 21(干红) 2
23 53 23(白兰地) 2
select 答题人编号
from A
where (case when 问题编号=51 then 问题答案 else 0 end) = 1 and (case when 问题编号=52 then 问题答案 else 0 end ) = 21 and (case when 问题编号=53 then 问题答案 else 0 end) =23
select 答题人编号
from A
where (cast (case when 问题编号=51 then 问题答案 else 0 end) as varchar(10)) = '1' and cast( (case when 问题编号=52 then 问题答案 else 0 end ) as varchar(10)) = '21' and cast( (case when 问题编号=53 then 问题答案 else 0 end) as varchar(10)) ='23'
视你的数据类型而定。当然可能还会有空格的问题。
'cast' 附近有语法错误,需要 'AS'。
from A
where cast( (case when 问题编号=51 then 问题答案 else 0 end) as varchar(10)) = '1' and cast( (case when 问题编号=52 then 问题答案 else 0 end ) as varchar(10)) = '21' and cast( (case when 问题编号=53 then 问题答案 else 0 end) as varchar(10)) ='23'
括号比较多,都有点晕了。
drop table A
go
create table A
(
[PK] [int] IDENTITY(1,1) NOT NULL,
[问卷编号] [int] NULL,
[问题编号] [int] NULL,
[问题答案] [int] NULL,
[答题人编号] [int] NULL
)
go
insert into A(问卷编号,问题编号,问题答案,答题人编号)
(
select 23,51,1,1 union
select 23,52,21,1 union
select 23,52,22,1 union
select 23,51,1,2 union
select 23,52,21,2 UNION
select 23,52,23,2 UNION
select 23,51,1,3 union
select 23,52,21,3 UNION
select 23,52,23,3
)
go
SELECT * FROM A
select * from tabA t
where exists (select * from tabA where [答题人编号]=t.[答题人编号] and [问题编号]=51 and [问题答案]=1)
and exists (select * from tabA where [答题人编号]=t.[答题人编号] and [问题编号]=53 and [问题答案]=23);
drop table A
go
create table A
(
[PK] [int] IDENTITY(1,1) NOT NULL,
[问卷编号] [int] NULL,
[问题编号] [int] NULL,
[问题答案] [int] NULL,
[答题人编号] [int] NULL
)
go
insert into A(问卷编号,问题编号,问题答案,答题人编号)
(
select 23,51,1,1 union
select 23,52,21,1 union
select 23,52,22,1 union
select 23,51,1,2 union
select 23,52,21,2 UNION
select 23,53,23,2 UNION
select 23,51,1,3 union
select 23,52,21,3 UNION
select 23,52,23,3
)
go
SELECT * FROM A t1
where exists(select 1 from A t2 where t1.答题人编号=t2.答题人编号 and t2.问题编号=52 and t2.问题答案=21)
and exists(select 1 from A t3 where t1.答题人编号=t3.答题人编号 and t3.问题编号=53 and t3.问题答案=23)/*(9 行受影响)
PK 问卷编号 问题编号 问题答案 答题人编号
----------- ----------- ----------- ----------- -----------
2 23 51 1 2
5 23 52 21 2
9 23 53 23 2(3 行受影响)
*/
where exists(select 1 from A t2 where t1.问卷编号=t2.问卷编号 and t1.答题人编号=t2.答题人编号 and t2.问题编号=52 and t2.问题答案=21)
and exists(select 1 from A t3 where t1.问卷编号=t3.问卷编号 and t1.答题人编号=t3.答题人编号 and t3.问题编号=53 and t3.问题答案=23)