挑战高手,这样的查询结果如何写?

jzinfo 2009-08-25 11:25:53
表:
materinfo (pno,vendor,ppl,price)
pno: varchar(20)
vendor: varchar(50)
ppl: bit
price: numeric(18,9)


如下记录:
P0001,ASD,0,0.0025
P0001,ABT,0,0.0015
P0001,ETR,1,0.0018
P0002,ETR,1,0.0015
P0002,ABT,0,0.0020
P0003,ART,0,0.0015
P0003,RYT,0,0.0030
P0003,RTU,0,0.0012

数据记录中同一个pno不超过3个记录。
查询的要求是:
查询某一pno的记录,该记录中如果有多个pno号相同的,则获取PPL为1的那条PNO的记录
如果多个pno号相同的所有PPL值都是0,则获取price最低的那个。

如上的记录,我们查询P0001这个编号的记录,则应该查得得结果是:P0001,ETR,1,0.0018
查询P0002这个PNO的记录,则查出的是P0002,ETR,1,0.0015
查询P0003这个PNO的记录,则查出的是P0003,RTU,0,0.0012 (同样的PNO的记录PPL都为0时,查询价格最低的那个)

帮忙解决下,谢谢,写了好半天都不对。。

...全文
123 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
Jamin_Liu 2009-08-25
  • 打赏
  • 举报
回复
declare @materinfo table(
pno varchar(20),
vendor varchar(50),
ppl bit,
price numeric(18,9)
)
INSERT INTO @materinfo
SELECT 'P0001','ASD',0,0.0025 UNION ALL
SELECT 'P0001','ABT',0,0.0015 UNION ALL
SELECT 'P0001','ETR',1,0.0018 UNION ALL
SELECT 'P0002','ETR',1,0.0015 UNION ALL
SELECT 'P0002','ABT',0,0.0020 UNION ALL
SELECT 'P0003','ART',0,0.0015 UNION ALL
SELECT 'P0003','RYT',0,0.0030 UNION ALL
SELECT 'P0003','RTU',0,0.0012

declare @pno varchar(20)='P0001'
select *
from @materinfo t1
where pno=@pno
and 1=(case when ppl=1 then 1 else (select 1 from @materinfo group by pno having pno=t1.pno and max(cast(ppl as int))=0 and t1.price=min(price)) end)
水族杰纶 2009-08-25
  • 打赏
  • 举报
回复
看看牛人~
  • 打赏
  • 举报
回复

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

create table tb(pno varchar(20),vendor varchar(50),ppl bit,price numeric(18,9))

insert tb
select 'P0001','ASD',0,0.0025 union all
select 'P0001','ABT',0,0.0015 union all
select 'P0001','ETR',1,0.0018 union all
select 'P0002','ETR',1,0.0015 union all
select 'P0002','ABT',0,0.0020 union all
select 'P0003','ART',0,0.0015 union all
select 'P0003','RYT',0,0.0030 union all
select 'P0003','RTU',0,0.0012


select pno,vendor,ppl,price from tb a where not exists ( select * from tb where pno=a.pno and price<a.price)
and not exists
(select distinct * from tb where ppl = 1 and pno=a.pno)
union all
(select distinct * from tb where ppl = 1) order by pno

/*-------------------------
pno vendor ppl price
P0001 ETR 1 0.001800000
P0002 ETR 1 0.001500000
P0003 RTU 0 0.001200000
---------------------------*/
--小F-- 2009-08-25
  • 打赏
  • 举报
回复
--借小麦的数据
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( pno varchar(20),
vendor varchar(50) ,
ppl bit ,
price numeric(18,9) )
go
insert tb SELECT
'P0001','ASD',0,0.0025 UNION ALL SELECT
'P0001','ABT',0,0.0015 UNION ALL SELECT
'P0001','ETR',1,0.0018 UNION ALL SELECT
'P0002','ETR',1,0.0015 UNION ALL SELECT
'P0002','ABT',0,0.0020 UNION ALL SELECT
'P0003','ART',0,0.0015 UNION ALL SELECT
'P0003','RYT',0,0.0030 UNION ALL SELECT
'P0003','RTU',0,0.0012
go

select
*
from
tb t
where
exists
(select 1 from tb where pno = t.pno and ppl = 1)and ppl = 1
or
not exists(select 1 from tb where pno = t.pno and ppl = 1)
and
not exists(select 1 from tb where pno = t.pno and price < t.price)
/*pno vendor ppl price
-------------------- -------------------------------------------------- ----- ---------------------------------------
P0001 ETR 1 0.001800000
P0002 ETR 1 0.001500000
P0003 RTU 0 0.001200000

(3 行受影响)
*/
--小F-- 2009-08-25
  • 打赏
  • 举报
回复
select 
*
from
tb t
where
exists
(select 1 from tb where pno = t.pno and ppl = 1)and ppl = 1
or
not exists(select 1 from tb where pno = t.pno and ppl = 1)
and
not exists(select 1 from tb where pno = t.pno and price < t.price)
gw6328 2009-08-25
  • 打赏
  • 举报
回复
顶个
guguda2008 2009-08-25
  • 打赏
  • 举报
回复
个人认为这算标题党
SQL77 2009-08-25
  • 打赏
  • 举报
回复
DECLARE @materinfo TABLE (pno VARCHAR(5),vendor VARCHAR(3),ppl INT,price NUMERIC(5,4))
INSERT INTO @materinfo
SELECT 'P0001','ASD',0,0.0025 UNION ALL
SELECT 'P0001','ABT',0,0.0015 UNION ALL
SELECT 'P0001','ETR',1,0.0018 UNION ALL
SELECT 'P0002','ETR',1,0.0015 UNION ALL
SELECT 'P0002','ABT',0,0.0020 UNION ALL
SELECT 'P0003','ART',0,0.0015 UNION ALL
SELECT 'P0003','RYT',0,0.0030 UNION ALL
SELECT 'P0003','RTU',0,0.0012

SELECT * FROM @materinfo T WHERE EXISTS(SELECT 1 FROM @materinfo WHERE PNO=T.PNO AND ppl=1)AND ppl=1
UNION ALL
SELECT * FROM @materinfo T WHERE NOT EXISTS(SELECT 1 FROM @materinfo WHERE PNO=T.PNO AND ppl=1) AND
price =(SELECT MIN(price) FROM (SELECT * FROM @materinfo T WHERE NOT EXISTS(SELECT 1 FROM @materinfo WHERE PNO=T.PNO AND ppl=1))AS T1 WHERE T.PNO=T1.PNO)

(所影响的行数为 8 行)

pno vendor ppl price
----- ------ ----------- -------
P0001 ETR 1 .0018
P0002 ETR 1 .0015
P0003 RTU 0 .0012

(所影响的行数为 3 行)
百年树人 2009-08-25
  • 打赏
  • 举报
回复
create table materinfo
(
pno varchar(20),
vendor varchar(50),
ppl bit ,
price numeric(18,9)
)

insert [materinfo]
select 'P0001','ASD',0,0.0025 union all
select 'P0001','ABT',0,0.0015 union all
select 'P0001','ETR',1,0.0018 union all
select 'P0002','ETR',1,0.0015 union all
select 'P0002','ABT',0,0.0020 union all
select 'P0003','ART',0,0.0015 union all
select 'P0003','RYT',0,0.0030 union all
select 'P0003','RTU',0,0.0012

select *
from materinfo t
where not exists(select 1 from materinfo where pno=t.pno and (ppl>t.ppl or ppl=t.ppl and price<t.price))

drop table materinfo

/**
pno vendor ppl price
-------------------- -------------------------------------------------- ---- --------------------
P0001 ETR 1 .001800000
P0002 ETR 1 .001500000
P0003 RTU 0 .001200000

(所影响的行数为 3 行)
**/
feixianxxx 2009-08-25
  • 打赏
  • 举报
回复
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( pno varchar(20),
vendor varchar(50) ,
ppl bit ,
price numeric(18,9) )
go
insert tb SELECT
'P0001','ASD',0,0.0025 UNION ALL SELECT
'P0001','ABT',0,0.0015 UNION ALL SELECT
'P0001','ETR',1,0.0018 UNION ALL SELECT
'P0002','ETR',1,0.0015 UNION ALL SELECT
'P0002','ABT',0,0.0020 UNION ALL SELECT
'P0003','ART',0,0.0015 UNION ALL SELECT
'P0003','RYT',0,0.0030 UNION ALL SELECT
'P0003','RTU',0,0.0012
go
select
*
from tb t
where ppl=1
or (not exists(select * from tb where t.pno=pno and price<t.price)
and not exists(select * from tb where t.pno=pno and ppl=1 ))
go
pno vendor ppl price
-------------------- -------------------------------------------------- ----- ---------------------------------------
P0001 ETR 1 0.001800000
P0002 ETR 1 0.001500000
P0003 RTU 0 0.001200000
SQL77 2009-08-25
  • 打赏
  • 举报
回复
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE PNO=T.PNO AND ppl=1)
UNION ALL
SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE PNO=T.PNO AND ppl=1) AND
price =(SELECT MIN(price) FROM (SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE PNO=T.PNO AND ppl=1))AS T1 WHERE T.PNO=T1.PNO)
liangCK 2009-08-25
  • 打赏
  • 举报
回复
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 11:28:35
-------------------------------------

--> 生成测试数据: @materinfo
DECLARE @materinfo TABLE (pno VARCHAR(5),vendor VARCHAR(3),ppl INT,price NUMERIC(5,4))
INSERT INTO @materinfo
SELECT 'P0001','ASD',0,0.0025 UNION ALL
SELECT 'P0001','ABT',0,0.0015 UNION ALL
SELECT 'P0001','ETR',1,0.0018 UNION ALL
SELECT 'P0002','ETR',1,0.0015 UNION ALL
SELECT 'P0002','ABT',0,0.0020 UNION ALL
SELECT 'P0003','ART',0,0.0015 UNION ALL
SELECT 'P0003','RYT',0,0.0030 UNION ALL
SELECT 'P0003','RTU',0,0.0012

--SQL查询如下:

SELECT * FROM @materinfo AS A
WHERE EXISTS(SELECT * FROM @materinfo WHERE pno = A.pno AND ppl = 1)
AND ppl = 1
OR NOT EXISTS(SELECT * FROM @materinfo WHERE pno = A.pno AND ppl = 1)
AND NOT EXISTS(SELECT * FROM @materinfo WHERE pno = A.pno AND price < A.price)

/*
pno vendor ppl price
----- ------ ----------- ---------------------------------------
P0001 ETR 1 0.0018
P0002 ETR 1 0.0015
P0003 RTU 0 0.0012

(3 行受影响)

*/
guguda2008 2009-08-25
  • 打赏
  • 举报
回复

IF OBJECT_ID('materinfo') IS NOT NULL DROP TABLE materinfo
CREATE TABLE materinfo (
pno varchar(20),
vendor varchar(50),
ppl bit,
price numeric(18,9)
)
INSERT INTO MATERINFO
SELECT 'P0001','ASD',0,0.0025 UNION ALL
SELECT 'P0001','ABT',0,0.0015 UNION ALL
SELECT 'P0001','ETR',1,0.0018 UNION ALL
SELECT 'P0002','ETR',1,0.0015 UNION ALL
SELECT 'P0002','ABT',0,0.0020 UNION ALL
SELECT 'P0003','ART',0,0.0015 UNION ALL
SELECT 'P0003','RYT',0,0.0030 UNION ALL
SELECT 'P0003','RTU',0,0.0012


DECLARE @PNO VARCHAR(20)
SET @PNO='P0003'

SELECT TOP 1 * FROM MATERINFO
WHERE PNO=@PNO
ORDER BY PPL DESC ,PRICE ASC
feixianxxx 2009-08-25
  • 打赏
  • 举报
回复
select 
*
from tb t
where ppl=1
or not exists(select * from tb where t.pno=pno and price<t.price or ppl=1 )

34,575

社区成员

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

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