34,593
社区成员
发帖
与我相关
我的任务
分享
select *
from tb t
where '客户1' = (select top 1 cust from tb where name = t.name order by jiage)
select * from tb
where [name] in (select [name] from tb
where cust= (select top 1 cust from tb order by jiage))
select * from tb where name in
(select name from tb a
where cust='客户1'
and not exists
(select 1 from tb b where a.name=b.name and a.color=b.color and b.jiage<a.jiage))
/*
name color cust jiage
----- ----- ----- -----------------------------------------------------
产品A 黄色 客户1 0.20000000000000001
产品A 黄色 客户2 0.29999999999999999
产品B 黄色 客户1 0.10000000000000001
产品B 黄色 客户2 0.20000000000000001
(所影响的行数为 4 行)
*/
create table tb([name] varchar(5),color varchar(5),cust varchar(5), jiage float)
insert into tb
select '产品A','黄色','客户1',0.2 union
select '产品A','黄色','客户2',0.3 union
select '产品B','黄色','客户1',0.1 union
select '产品B','黄色','客户2',0.2 union
select '产品C','黄色','客户3',0.2
select * from tb
where [name]=(select top 1 [name] from tb a where cust='客户1'
and exists(select 1 from tb where jiage>a.jiage) order by jiage)
==>
名称 颜色 客户 价格
产品B 黄色 客户1 0.1
产品B 黄色 客户2 0.2
想要的结果是:
名称 颜色 客户 价格
产品A 黄色 客户1 0.2
产品A 黄色 客户2 0.3
产品B 黄色 客户1 0.1
产品B 黄色 客户2 0.2
select * from tb
where 名称=(select top 1 名称 from tb a where cust='客户一'
and exists(select 1 from tb where jiage>a.jiage) order by jiage)
select *
from tb a
where name in
(select top 1 name from tb order by jiage)
and exists
(select 1 from tb b where a.name=b.name and b.jiage>any
(select jiage from tb c where c.name<>a.name))
select *
from tb
where name = (select top 1 name from tb where cust = '客户一' order by jiage desc)
要的结果是
产品A 黄色 客户1 0.1
产品A 黄色 客户2 0.3
drop table tb
create table tb(name varchar(5),color varchar(5),cust varchar(5), jiage float)
insert into tb
select '产品A','黄色','客户1',0.1 union
select '产品A','黄色','客户2',0.3 union
select '产品B','黄色','客户2',0.2 union
select '产品B','黄色','客户2',0.2 union
select '产品C','黄色','客户3',0.2
select *
from tb
where name in
(select top 1 name from tb order by jiage)
/*
name color cust jiage
----- ----- ----- --------------
产品A 黄色 客户1 0.1
产品A 黄色 客户2 0.3
(所影响的行数为 2 行)
*/