34,837
社区成员
发帖
与我相关
我的任务
分享求查询几个并列条件的SQL语句写法？
表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
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
...全文
请发表友善的回复…
发表回复
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))
FROM a
WHERE (0 IN (a1, a2, a3)) AND (4 IN (a1, a2, a3))
asdw001 2007-12-07
- 打赏
- 举报
楼上的朋友
若查询5，5这两个条件，就不对了！
若查询5，5这两个条件，就不对了！
