求sql语句

kevn 2010-04-24 10:45:21
id p_id s_id amt flag
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8448 104 02 0.00 1
8449 104 03 0.00 1
8450 104 19 20.00 1
8451 104 100 80.00 1
8458 1539 03 0.00 1
8795 421 02 0.00 1
8796 421 03 0.00 1
8797 421 01 0.00 1

我想找出当p_id相等的情况下，只包含02的，或者只包含03的p_id，s_id
...全文
160 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2010-04-24
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 kevinwen0413 的回复:]
各位的会将421 03
421 02
列出，但这个是不想要的[/Quote]
2楼结果对不对?
xman_78tom 2010-04-24
  • 打赏
  • 举报
 回复
请 lz 给出完整的结果，这样猜好累的。
kevn 2010-04-24
  • 打赏
  • 举报
 回复
当我的数据是
id p_id s_id amt flag
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8448 104 02 0.00 1
8449 104 03 0.00 1
8450 104 19 20.00 1
8451 104 100 80.00 1
8458 1539 03 0.00 1
8795 421 02 0.00 1
8796 421 03 0.00 1
9999 345 01 0 1

各位的会将421 03
421 02
列出，但这个是不想要的
stevehxh 2010-04-24
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 kevinwen0413 的回复:]

楼上几位都不满足我的要求，可能我给的测试数据有问题，如果数据了多一条
这样的数据
id p_id s_id amt flag
9999 345 01 0 1
即下面这样，那是不是各位的语句就会把这条也提取出来呢

id p_id s_id amt flag
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8448 104 02 0……
[/Quote]


insert into tb 

select 9999, 345, '01', 0, 1



select * from tb

/*

id          p_id        s_id       amt                                     flag

----------- ----------- ---------- --------------------------------------- ----

8423        1532        02         0.00                                    1

8438        1235        02         0.00                                    1

8448        104         02         0.00                                    1

8449        104         03         0.00                                    1

8450        104         19         20.00                                   1

8451        104         100        80.00                                   1

8458        1539        03         0.00                                    1

8795        421         02         0.00                                    1

8796        421         03         0.00                                    1

8797        421         01         0.00                                    1

9999        345         01         0.00                                    1

*/



select tb1.* from tb as tb1 where tb1.p_id in

(select tb2.p_id from tb as tb2 group by tb2.p_id having count(*)=1) and

tb1.s_id in ('02','03')



/*

结果：



id          p_id        s_id       amt                                     flag

----------- ----------- ---------- --------------------------------------- ----

8423        1532        02         0.00                                    1

8438        1235        02         0.00                                    1

8458        1539        03         0.00                                    1



(3 行受影响)





*/
kevn 2010-04-24
  • 打赏
  • 举报
 回复
楼上几位都不满足我的要求，可能我给的测试数据有问题，如果数据了多一条
这样的数据
id p_id s_id amt flag
9999 345 01 0 1
即下面这样，那是不是各位的语句就会把这条也提取出来呢

id p_id s_id amt flag
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8448 104 02 0.00 1
8449 104 03 0.00 1
8450 104 19 20.00 1
8451 104 100 80.00 1
8458 1539 03 0.00 1
8795 421 02 0.00 1
8796 421 03 0.00 1
8797 421 01 0.00 1
9999 345 01 0 1
stevehxh 2010-04-24
  • 打赏
  • 举报
 回复 


if object_id('tb') is not null

drop table tb

go

create table tb(id int,p_id int,s_id varchar(10), amt numeric(8,2), flag tinyint)

insert into tb

select 8423,1532,'02', 0.00 ,1 union all

select 8438,1235,'02', 0.00 ,1  union all

select 8448 ,104 ,'02' ,0.00 ,1 union all

select 8449 ,104 ,'03' ,0.00 ,1 union all

select 8450 ,104 ,'19' ,20.00 ,1 union all

select 8451 ,104, '100' ,80.00 ,1 union all

select 8458 ,1539 ,'03', 0.00 ,1 union all

select 8795 ,421 ,'02' ,0.00, 1 union all

select 8796 ,421, '03', 0.00 ,1 union all

select 8797, 421 ,'01' ,0.00, 1





select tb1.* from tb as tb1 where tb1.p_id in

(select tb2.p_id from tb as tb2 group by tb2.p_id having count(*)=1) and

tb1.s_id in ('02','03')



/*

结果:



id          p_id        s_id       amt                                     flag

----------- ----------- ---------- --------------------------------------- ----

8423        1532        02         0.00                                    1

8438        1235        02         0.00                                    1

8458        1539        03         0.00                                    1



(3 行受影响)

*/
--小F-- 2010-04-24
  • 打赏
  • 举报
 回复 
--理解错意思了

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-04-24 11:10:44

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)

insert [tb]

select 8423,1532,'02',0.00,1 union all

select 8438,1235,'02',0.00,1 union all

select 8448,104,'02',0.00,1 union all

select 8449,104,'03',0.00,1 union all

select 8450,104,'19',20.00,1 union all

select 8451,104,'100',80.00,1 union all

select 8458,1539,'03',0.00,1 union all

select 8795,421,'02',0.00,1 union all

select 8796,421,'03',0.00,1 union all

select 8797,421,'01',0.00,1

--------------开始查询--------------------------

select

 * 

from

 tb t

where 

 not exists (select 1 from tb where s_id <> '02' and s_id <> '03' and p_id=t.p_id )



-------------------------

/* id          p_id        s_id amt                                     flag

----------- ----------- ---- --------------------------------------- -----------

8423        1532        02   0.00                                    1

8438        1235        02   0.00                                    1

8458        1539        03   0.00                                    1



(3 行受影响)

*/
xman_78tom 2010-04-24
  • 打赏
  • 举报
 回复 


select * from tab t where exists (select * from tab where p_id=t.p_id and t.s_id in ('03','02'))
--小F-- 2010-04-24
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-04-24 11:10:44

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)

insert [tb]

select 8423,1532,'02',0.00,1 union all

select 8438,1235,'02',0.00,1 union all

select 8448,104,'02',0.00,1 union all

select 8449,104,'03',0.00,1 union all

select 8450,104,'19',20.00,1 union all

select 8451,104,'100',80.00,1 union all

select 8458,1539,'03',0.00,1 union all

select 8795,421,'02',0.00,1 union all

select 8796,421,'03',0.00,1 union all

select 8797,421,'01',0.00,1

--------------开始查询--------------------------

select 

  *

from

  tb t

where

  exists(select 1 from tb where p_id=t.p_id  and s_id ='02' or s_id ='03')

and

  s_id in ('02','03')

----------------结果----------------------------

/* id          p_id        s_id amt                                     flag

----------- ----------- ---- --------------------------------------- -----------

8423        1532        02   0.00                                    1

8438        1235        02   0.00                                    1

8448        104         02   0.00                                    1

8449        104         03   0.00                                    1

8458        1539        03   0.00                                    1

8795        421         02   0.00                                    1

8796        421         03   0.00                                    1



(7 行受影响)

*/
dawugui 2010-04-24
  • 打赏
  • 举报
 回复 
create table tb(id int,p_id int,s_id varchar(10),amt decimal(18,2),flag int)

insert into tb values(8423 ,1532 ,'02' , 0.00  ,1)

insert into tb values(8438 ,1235 ,'02' , 0.00  ,1)

insert into tb values(8448 ,104  ,'02' , 0.00  ,1)

insert into tb values(8449 ,104  ,'03' , 0.00  ,1)

insert into tb values(8450 ,104  ,'19' , 20.00 ,1)

insert into tb values(8451 ,104  ,'100', 80.00 ,1)

insert into tb values(8458 ,1539 ,'03' , 0.00  ,1)

insert into tb values(8795 ,421  ,'02' , 0.00  ,1)

insert into tb values(8796 ,421  ,'03' , 0.00  ,1)

insert into tb values(8797 ,421  ,'01' , 0.00  ,1)

go



select * from tb where p_id not in 

(select distinct p_id from tb where s_id <> '02' and s_id <> '03'  )



drop table tb



/*

id          p_id        s_id       amt                  flag        

----------- ----------- ---------- -------------------- ----------- 

8423        1532        02         .00                  1

8438        1235        02         .00                  1

8458        1539        03         .00                  1



（所影响的行数为 3 行）



*/
--小F-- 2010-04-24
  • 打赏
  • 举报
 回复 
select 

  *

from

  tb t

where

  exists(select 1 from tb where p_id=t.p_id  and s_id ='02')

or

  exists(select 1 from tb where p_id=t.p_id  and s_id ='03')
freeLZQ 2010-04-24
  • 打赏
  • 举报
 回复 
if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)

insert [tb]

select 8799,105,'200',50.00,1 union all

select 8800,106,'02',30.00,1 union all

select 8801,106,'22',30.00,1 union all

select 8423,1532,'02',0.00,1 union all

select 8438,1235,'02',0.00,1 union all

select 8450,104,'19',20.00,1 union all

select 8451,104,'100',80.00,1 union all

select 8458,1539,'03',0.00,1 union all

select 8795,421,'02',0.00,1 union all

select 8796,421,'03',0.00,1 union all

select 8789,876,'02',0.00,1



--------------开始查询--------------------------

select  *  from  tb where p_id not in(select p_id from tb where s_id not in('02','03')) 



/*

8423	1532	02	.00	1

8438	1235	02	.00	1

8458	1539	03	.00	1

8795	421	02	.00	1

8796	421	03	.00	1

8789	876	02	.00	1

*/
freeLZQ 2010-04-24
  • 打赏
  • 举报
 回复 
if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)

insert [tb]

select 8423,1532,'02',0.00,1 union all

select 8438,1235,'02',0.00,1 union all

select 8448,104,'02',0.00,1 union all

select 8449,104,'03',0.00,1 union all

select 8450,104,'19',20.00,1 union all

select 8451,104,'100',80.00,1 union all

select 8458,1539,'03',0.00,1 union all

select 8795,421,'02',0.00,1 union all

select 8796,421,'03',0.00,1 union all

select 8797,421,'01',0.00,1

--------------开始查询--------------------------

select  *  from  tb where p_id not in(select p_id from tb where s_id not in('02','03')) 



/*

结果：



id          p_id        s_id       amt                                     flag

----------- ----------- ---------- --------------------------------------- ----

8423        1532        02         0.00                                    1

8438        1235        02         0.00                                    1

8458        1539        03         0.00                                    1



(3 行受影响)





*/
我是小数位 2010-04-24
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 beirut 的回复:]
SQL code
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)
insert [tb]
select 8799,105,'200……
[/Quote]
他的这个应该是对的吧？
黄_瓜 2010-04-24
  • 打赏
  • 举报
 回复 
--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([id] int,[p_id] int,[s_id] varchar(3),[amt] numeric(4,2),[flag] int)

insert [tb]

select 8799,105,'200',50.00,1 union all

select 8800,106,'02',30.00,1 union all

select 8801,106,'22',30.00,1 union all

select 8423,1532,'02',0.00,1 union all

select 8438,1235,'02',0.00,1 union all

select 8450,104,'19',20.00,1 union all

select 8451,104,'100',80.00,1 union all

select 8458,1539,'03',0.00,1 union all

select 8795,421,'02',0.00,1 union all

select 8796,421,'03',0.00,1 union all

select 8789,876,'02',0.00,1



--------------------------------查询开始------------------------------



select * from [tb] a where s_id in('02','03')

and  not exists (select 1 from tb where s_id in('02','03') and p_id=a.p_id  and s_id<>a.s_id)

/*

id          p_id        s_id amt                                     flag

----------- ----------- ---- --------------------------------------- -----------

8800        106         02   30.00                                   1

8423        1532        02   0.00                                    1

8438        1235        02   0.00                                    1

8458        1539        03   0.00                                    1

8789        876         02   0.00                                    1



(5 行受影响)





*/
Leshami 2010-04-24
  • 打赏
  • 举报
 回复
select id p_id s_id amt flag
from tb
where exists
(select 1 from tb where b.p_id = a.p_id)
and s_id in ('02','03')
kevn 2010-04-24
  • 打赏
  • 举报
 回复
6楼的接近我的要求，是我给的测试数据和描述没弄好吧，不好意思，再重说一下

id p_id s_id amt flag
8799 105 200 50.00 1
8800 106 02 30.00 1
8801 106 22 30.00 1
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8450 104 19 20.00 1
8451 104 100 80.00 1
8458 1539 03 0.00 1
8795 421 02 0.00 1
8796 421 03 0.00 1
8789 876 02 0.00 1

如上，我要的是当p_id相等的情况下，列出包括s_id=02或s_id=03的 ，但同时出现的不要。。也就是要下面的结果

8800 106 02 30.00 1
8423 1532 02 0.00 1
8438 1235 02 0.00 1
8458 1539 03 0.00 1
8789 876 02 0.00 1
wqmgxj 2010-04-24
  • 打赏
  • 举报
 回复
至少要给出你想要的结果吧，
SQL语句基础教程
SQL语句基础教程
SQL学习资料（包含所有常用的语句例子）
sql学习必备，内含sql一些常用语句的例子，有图有表，通过结果来理解sql语句的实现与结果。
PLSQL执行SQL语句
PLSQL执行SQL语句 1、执行中量SQL语句 当执行SQL语句较大时，除运行SQL语句外可以点击工具-------&gt;导入表---------&gt;（出现界面如下图所示） 点击导入就会出现执行语句的窗口如下图所示，把语句...
oracle sql developer 执行sql语句,如何使用SQL Developer执行SQL语句（上）
在这一部分，我们将会学习如何使用SQL Developer来输入并执行SQL语句及脚本。如何使用摘录窗口，解决常见的语法错误等内容。在你回顾数据库设计的同时，SQL Developer还是一个很好的可输入并运行SQL语句的工具。在接...
SQL语句五种分类
在介绍SQL语句五种分类前，先来简单的认识一下SQL。SQL的英文全称为Structured Query Language，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准，但是每一个数据库都有自己的特性，当使用这个数据库特性...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章