有表A
编号,供应商A,供应商B,供应商C,供应商D,供应商E
01 1.2 1.2 1.2 1.3 1.6
02 3.6 2 3.6 3.6 2.5
03 2 2.1 2 1.2 1.2想要判断出每一个产品中哪个供应商的价最低,供应商字段是哪个(供应商A,供应商B,供应商C,供应商D,供应商E),当
有重复的时候要求取供应商字母靠前的
结果
编号,最低价,供应商
01 1.2 供应商A
02 3.6 供应商B
03 2 供应商A
求一句代码搞定,能否实现
编号,供应商A,供应商B,供应商C,供应商D,供应商E
01 1.2 1.2 1.2 1.3 1.6
02 3.6 2 3.6 3.6 2.5
03 2 2.1 2 1.2 1.2想要判断出每一个产品中哪个供应商的价最低,供应商字段是哪个(供应商A,供应商B,供应商C,供应商D,供应商E),当
有重复的时候要求取供应商字母靠前的
结果
编号,最低价,供应商
01 1.2 供应商A
02 3.6 供应商B
03 2 供应商A
求一句代码搞定,能否实现
order by(right(A.供应商,1))
一堆CASE WHEN
--这个应该可以吧! 放在 group by 后
order by(right(A.供应商,1))
(
select 编号,供应商A as 价格,'供应商A' as 供应商 from A
union all
select 编号,供应商B as 价格,'供应商B' as 供应商 from A
union all
select 编号,供应商C as 价格,'供应商C' as 供应商 from A
union all
select 编号,供应商D as 价格,'供应商D' as 供应商 from A
union all
select 编号,供应商E as 价格,'供应商E' as 供应商 from A
)
select * from cte1 t
where not exists(select 1 from cte1 where 编号=t.编号 and 价格<t.价格)
if object_id('[tb]') is not null drop table [tb]
create table [tb] (编号 varchar(2),供应商A numeric(2,1),供应商B numeric(2,1),供应商C numeric(2,1),供应商D numeric(2,1),供应商E numeric(2,1))
insert into [tb]
select '01',1.2,1.2,1.2,1.3,1.6 union all
select '02',3.6,2,3.6,3.6,2.5 union all
select '03',2,2.1,2,1.2,1.2with cte as (
select 编号,供应商A as 价格,'A' as 供应商 from tb
union
select 编号,供应商B as 价格,'B' as 供应商 from tb
union
select 编号,供应商C as 价格,'C' as 供应商 from tb
union
select 编号,供应商D as 价格,'D' as 供应商 from tb
union
select 编号,供应商E as 价格,'F' as 供应商 from tb)select 编号,价格 as 最低价,min(供应商) as 供应商 from cte t
where not exists(select 1 from cte where 编号=t.编号 and 价格<t.价格)
group by 编号,价格 order by 编号/*
编号 最低价 供应商
01 1.2 A
02 2.0 B
03 1.2 D
*/
/*
有表A
编号,供应商A,供应商B,供应商C,供应商D,供应商E
01 1.2 1.2 1.2 1.3 1.6
02 3.6 2 3.6 3.6 2.5
03 2 2.1 2 1.2 1.2
结果
编号,最低价,供应商
01 1.2 供应商A
02 2 供应商B
03 1.2 供应商D*/
drop table tb
Go
Create table tb([编号] nvarchar(2),[供应商A] decimal(18,1),[供应商B] decimal(18,1),[供应商C] decimal(18,1),[供应商D] decimal(18,1),[供应商E] decimal(18,1))
Insert tb
select N'01',1.2,1.2,1.2,1.3,1.6 union all
select N'02',3.6,2,3.6,3.6,2.5 union all
select N'03',2,2.1,2,1.2,1.2
Go
select 编号,
min(價格)最低價,
min(供应商)供应商
from(
Select 编号,供应商A 價格,N'供应商A'供应商 from tb
union all
Select 编号,供应商B ,N'供应商B'供应商 from tb
union all
Select 编号,供应商C ,N'供应商C'供应商 from tb
union all
Select 编号,供应商D ,N'供应商D'供应商 from tb
union all
Select 编号,供应商E ,N'供应商E'供应商 from tb
)t
group by 编号
/*
编号 最低價 供应商
---- --------------------------------------- ----
01 1.2 供应商A
02 2.0 供应商A
03 1.2 供应商A(3 row(s) affected)
*/
go
create table [A]([编号] varchar(2),
[供应商A] numeric(2,1),
[供应商B] numeric(2,1),
[供应商C] numeric(2,1),
[供应商D] numeric(2,1),
[供应商E] numeric(2,1)
)
insert [A]
select '01',1.2,1.2,1.2,1.3,1.6 union all
select '02',3.6,2,3.6,3.6,2.5 union all
select '03',2,2.1,2,1.2,1.2
GO;with cte1 as
(
select 编号,供应商A as 价格,'供应商A' as 供应商 from A
union all
select 编号,供应商B as 价格,'供应商B' as 供应商 from A
union all
select 编号,供应商C as 价格,'供应商C' as 供应商 from A
union all
select 编号,供应商D as 价格,'供应商D' as 供应商 from A
union all
select 编号,供应商E as 价格,'供应商E' as 供应商 from A
)
select * from cte1 t
where not exists(select 1 from cte1 where 编号=t.编号 and 价格<t.价格)
/**
编号 价格 供应商
---- --------------------------------------- -------
01 1.2 供应商A
01 1.2 供应商B
02 2.0 供应商B
01 1.2 供应商C
03 1.2 供应商D
03 1.2 供应商E(6 行受影响)
**/
;with cte1 as
(
select 编号,供应商A as 价格,'供应商A' as 供应商 from A
union all
select 编号,供应商B as 价格,'供应商B' as 供应商 from A
union all
select 编号,供应商C as 价格,'供应商C' as 供应商 from A
union all
select 编号,供应商D as 价格,'供应商D' as 供应商 from A
union all
select 编号,供应商E as 价格,'供应商E' as 供应商 from A
)
select * from cte1 t
where not exists(select 1 from cte1 where 编号=t.编号 and (价格<t.价格 OR 价格=t.价格 AND 供应商<T.供应商))
/**
编号 价格 供应商
---- --------------------------------------- -------
01 1.2 供应商A
02 2.0 供应商B
03 1.2 供应商D(3 行受影响)**/
use City;
go
set nocount on
if OBJECT_ID(N'A',N'U') is not null drop table A
go
create table A
(
编号 nvarchar(100),
供应商A float,
供应商B float,
供应商C float,
供应商D float,
供应商E float
)
go--插入测试数据
insert into A
select '01', 1.2, 1.2, 1.2, 1.3, 1.6 union all
select '02', 3.6, 2, 3.6, 3.6, 2.5 union all
select '03', 2, 2.1, 2, 1.2, 1.2
go--关键代码
with cte as
(
select 编号,min(供应商A)as [供应商A],min(供应商B) as [供应商B],min(供应商C) as [供应商C],min(供应商D) as [供应商D],min(供应商E) as [供应商E]
from A group by 编号
)
,cte1 as
(
select *,ROW_NUMBER()over(PARTITION by 编号 order by NEWCOLV,NEWCOL) as RN from cte
unpivot
(
NEWCOLV
for NEWCOL in([供应商A],[供应商B],[供应商C],[供应商D],[供应商E])
)as BB
)
select 编号,NEWCOLV,NEWCOL from cte1 where RN=1
--删除测试数据
drop table A
/*
编号 NEWCOLV NEWCOL
----- ----------- --------------------
01 1.2 供应商A
02 2 供应商B
03 1.2 供应商D
*/