有两个表,x表,y表,都是4个字段,x表有记录若干条,y表只有一条,现在想从x表中显示记录,条件是x表记录的奇数和等于y表记录的奇数和,我用了这样的命令:select * from x where
(
(select a from x where a in (1,3,5,7,9,11,13)) +
(select b from x where b in (1,3,5,7,9,11,13)) +
(select c from x where c in (1,3,5,7,9,11,13)) +
(select d from x where d in (1,3,5,7,9,11,13))
)
=
(
(select a from y where a in (1,3,5,7,9,11,13)) +
(select b from y where b in (1,3,5,7,9,11,13)) +
(select c from y where c in (1,3,5,7,9,11,13)) +
(select d from y where d in (1,3,5,7,9,11,13))
)
提示错误:服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。特请大虾们帮忙,先谢谢。
(
(select a from x where a in (1,3,5,7,9,11,13)) +
(select b from x where b in (1,3,5,7,9,11,13)) +
(select c from x where c in (1,3,5,7,9,11,13)) +
(select d from x where d in (1,3,5,7,9,11,13))
)
=
(
(select a from y where a in (1,3,5,7,9,11,13)) +
(select b from y where b in (1,3,5,7,9,11,13)) +
(select c from y where c in (1,3,5,7,9,11,13)) +
(select d from y where d in (1,3,5,7,9,11,13))
)
提示错误:服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。特请大虾们帮忙,先谢谢。
where a in (1,3,5,7,9,11,13)
and b in (1,3,5,7,9,11,13)
and c in (1,3,5,7,9,11,13)
and d in (1,3,5,7,9,11,13)
and (a+b+c+d)
=(select top 1 (a+b+c+d) from y
where a in (1,3,5,7,9,11,13)
and b in (1,3,5,7,9,11,13)
and c in (1,3,5,7,9,11,13)
and d in (1,3,5,7,9,11,13))
(
case when a in (1,3,5,7,9,11,13) then a else 0 end +
case when b in (1,3,5,7,9,11,13) then a else 0 end +
case when c in (1,3,5,7,9,11,13) then a else 0 end +
case when d in (1,3,5,7,9,11,13) then a else 0 end
)
=
(
(select a from y where a in (1,3,5,7,9,11,13)) +
(select b from y where b in (1,3,5,7,9,11,13)) +
(select c from y where c in (1,3,5,7,9,11,13)) +
(select d from y where d in (1,3,5,7,9,11,13))
)
(
case when a in (1,3,5,7,9,11,13) then a else 0 end +
case when b in (1,3,5,7,9,11,13) then a else 0 end +
case when c in (1,3,5,7,9,11,13) then a else 0 end +
case when d in (1,3,5,7,9,11,13) then a else 0 end
)
=
(
(select
case when a in (1,3,5,7,9,11,13) then a else 0 end +
case when b in (1,3,5,7,9,11,13) then a else 0 end +
case when c in (1,3,5,7,9,11,13) then a else 0 end +
case when d in (1,3,5,7,9,11,13) then a else 0 end
from y
)
(
case when a in (1,3,5,7,9,11,13) then a else 0 end +
case when b in (1,3,5,7,9,11,13) then b else 0 end +
case when c in (1,3,5,7,9,11,13) then c else 0 end +
case when d in (1,3,5,7,9,11,13) then d else 0 end
)
=
(
(select
case when a in (1,3,5,7,9,11,13) then a else 0 end +
case when b in (1,3,5,7,9,11,13) then b else 0 end +
case when c in (1,3,5,7,9,11,13) then c else 0 end +
case when d in (1,3,5,7,9,11,13) then d else 0 end
from y
)
(
a int,
b int,
c int,
d int
)
create table #K
(
a int,
b int,
c int,
d int
)
insert into #G
select 1,3,5,13
insert into #K
select 1,3,5,7 union all
select 3,5,7,7 union all
select 2,6,7,7
select B.* from #G A,#K B where A.a+A.b+A.c+A.d=B.a+B.b+B.c+B.d and B.a in (1,3,5,7,9,11,13)
and B.b in (1,3,5,7,9,11,13) and B.c in (1,3,5,7,9,11,13) and B.d in (1,3,5,7,9,11,13)
drop table #G
drop table #K
(
a int,
b int,
c int,
d int
)
create table #K
(
a int,
b int,
c int,
d int
)
insert into #G
select 1,3,5,13
insert into #K
select 1,3,5,7 union all
select 3,5,7,7 union all
select 2,6,7,7 select * from #K B
where a%2*a + b%2*b + c%2*c + d%2*d
= (select a%2*a + b%2*b + c%2*c + d%2*d from #G)/*
a b c d
----------- ----------- ----------- -----------
3 5 7 7(1 行受影响)
*/drop table #G, #K
如果a是偶数: a%2*a = 0*a = 0
select * from #K B
where a%2*a + b%2*b + c%2*c + d%2*d
= (select a%2*a + b%2*b + c%2*c + d%2*d from #G)/*
a b c d
----------- ----------- ----------- -----------
3 5 7 7(1 行受影响)
*/
select * from #K B
where a%2*a + b%2*b + c%2*c + d%2*d
in (select a%2*a + b%2*b + c%2*c + d%2*d from #G)
(
a int,
b int,
c int,
d int
)
create table #K
(
a int,
b int,
c int,
d int
)
insert into #G
select 1,3,5,13
insert into #K
select 1,3,5,7 union all
select 3,5,7,7 union all
select 2,6,7,7
select B.* from #G A,#K B where A.a+A.b+A.c+A.d=B.a+B.b+B.c+B.d and B.a in (1,3,5,7,9,11,13)
and B.b in (1,3,5,7,9,11,13) and B.c in (1,3,5,7,9,11,13) and B.d in (1,3,5,7,9,11,13)
drop table #G
drop table #K