导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求查询几个并列条件的SQL语句写法?

asdw001 2007-12-07 10:22:37
表a的结构如下:
id text a1 a2 a3
1 ta 0 1 9
2 tc 4 8 4
3 tt 7 3 3
4 rt 3 0 3
5 gg 7 3 6
6 sr 2 5 5
7 qq 4 5 9
8 sd 5 5 8
9 vs 5 0 4
10 ws 4 0 8
11 rf 9 3 4

要求查询含有0并且含有4的所有记录。
显示如下:

id text a1 a2 a3

9 vs 5 0 4
10 ws 4 0 8

方便大家,表a的代码如下:

create table a(id int,t text,a1 int,a2 int,a3 int)
insert into a values(1,'ta',0,1,9)
insert into a values(2,'tc',4,8,4)
insert into a values(3,'tt',7,3,3)
insert into a values(4,'rt',3,0,3)
insert into a values(5,'gg',7,3,6)
insert into a values(6,'sr',2,5,5)
insert into a values(7,'qq',4,5,9)
insert into a values(8,'sd',5,5,8)
insert into a values(9,'vs',5,0,4)
insert into a values(10,'ws',4,0,8)
insert into a values(11,'rf',9,3,4)
go
...全文
904 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
fa_ge 2007-12-07

create table a(id int,t text,a1 int,a2 int,a3 int)
insert into a values(1,'ta',0,1,9)
insert into a values(2,'tc',4,8,4)
insert into a values(3,'tt',7,3,3)
insert into a values(4,'rt',3,0,3)
insert into a values(5,'gg',7,3,6)
insert into a values(6,'sr',2,5,5)
insert into a values(7,'qq',4,5,9)
insert into a values(8,'sd',5,5,8)
insert into a values(9,'vs',5,0,4)
insert into a values(10,'ws',4,0,8)
insert into a values(11,'rf',9,3,4)
go

select a.* from a
join
(
select id,t,cast(a1 as varchar)+','+cast(a2 as varchar)+','+cast(a3 as varchar) as aa from a
)b
on a.id=b.id
where charindex(',0,',','+b.aa+',')>0 and charindex(',4,',','+b.aa+',')>0

/*
id t a1 a2 a3
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
9 vs 5 0 4
10 ws 4 0 8

(所影响的行数为 2 行)
*/

回复
fa_ge 2007-12-07
我来写一个简单的算法
回复
qinhl99 2007-12-07
哈哈,最简单的,往往是最好的,楼主要找奇异的算法吗?哈哈
回复
dawugui 2007-12-07
应该没有.
回复
asdw001 2007-12-07
有没有其它好的方案啊,那样输入累死了:(
回复
-狙击手- 2007-12-07
create       table       a(id       int,t       text,a1       int,a2       int,a3       int) 
insert into a values(1,'ta',0,1,9)
insert into a values(2,'tc',4,8,4)
insert into a values(3,'tt',7,3,3)
insert into a values(4,'rt',3,0,3)
insert into a values(5,'gg',7,3,6)
insert into a values(6,'sr',2,5,5)
insert into a values(7,'qq',4,5,9)
insert into a values(8,'sd',5,5,8)
insert into a values(9,'vs',5,0,4)
insert into a values(10,'ws',4,0,8)
insert into a values(11,'rf',9,3,4)
go


select *
from a
where (case when a1=0 then 1 else 0 end +case when a2=0 then 1 else 0 end+case when a3=0 then 1 else 0 end) > 0 and
(case when a1=4 then 1 else 0 end +case when a2=4 then 1 else 0 end+case when a3=4 then 1 else 0 end) > 0
drop table a
/*

id t a1 a2 a3
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
9 vs 5 0 4
10 ws 4 0 8

(所影响的行数为 2 行)
*/
回复
dawugui 2007-12-07
select * from a where (a1 = 0 and (a2 = 4 or a3 = 4)) or (a2 = 0 and (a1 = 4 or a3 = 4)) or (a3 = 0 and (a1 = 4 or a2 = 4)) or 
(a1 = 4 and (a2 = 0 or a3 = 0)) or (a2 = 4 and (a1 = 0 or a3 = 0)) or (a3 = 4 and (a1 = 0 or a2 = 0))


/*
id t a1 a2 a3
----------- ---------- ----------- ----------- -----------
9 vs 5 0 4
10 ws 4 0 8

(所影响的行数为 2 行)
*/
回复
-狙击手- 2007-12-07
5,5也搞得定


create       table       a(id       int,t       text,a1       int,a2       int,a3       int) 
insert into a values(1,'ta',0,1,9)
insert into a values(2,'tc',4,8,4)
insert into a values(3,'tt',7,3,3)
insert into a values(4,'rt',3,0,3)
insert into a values(5,'gg',7,3,6)
insert into a values(6,'sr',2,5,5)
insert into a values(7,'qq',4,5,9)
insert into a values(8,'sd',5,5,8)
insert into a values(9,'vs',5,0,4)
insert into a values(10,'ws',4,0,8)
insert into a values(11,'rf',9,3,4)
go

select a.* from a
join
(
select id,t,cast(a1 as varchar)+','+cast(a2 as varchar)+','+cast(a3 as varchar) as aa from a
)b
on a.id=b.id
where charindex(',0,',','+b.aa+',')>0 and charindex(',4,',','+b.aa+',')>0
and charindex(',0,',','+b.aa+',') <> charindex(',4,',','+b.aa+',')


drop table a

/*

id t a1 a2 a3
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
9 vs 5 0 4
10 ws 4 0 8

(所影响的行数为 2 行)

*/
回复
qinhl99 2007-12-07
SELECT *
FROM a
WHERE (0 IN (a1, a2, a3)) AND (4 IN (a1, a2, a3))
回复
asdw001 2007-12-07
楼上的朋友
若查询5,5这两个条件,就不对了!
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告