34,575
社区成员
发帖
与我相关
我的任务
分享
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
---------------------------*/
--借小麦的数据
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 行受影响)
*/
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)
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 行)
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 行)
**/
-- =========================================
-- -----------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
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)
-------------------------------------
-- 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 行受影响)
*/
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
select
*
from tb t
where ppl=1
or not exists(select * from tb where t.pno=pno and price<t.price or ppl=1 )