请教一个查询语句
表结构如下:
name a b yongtu
a1 1.20 5.00 用途1
a2 1.20 6.00 用途1
a3 1.30 6.00 用途2要求返回
name ba yongtu
a2 5.00 用途1
a3 4.62 用途2
其中ba=b/a(保留两位)。如果yongtu中有相同值,则取ba最大的一条记录。
表结构如下:
name a b yongtu
a1 1.20 5.00 用途1
a2 1.20 6.00 用途1
a3 1.30 6.00 用途2要求返回
name ba yongtu
a2 5.00 用途1
a3 4.62 用途2
其中ba=b/a(保留两位)。如果yongtu中有相同值,则取ba最大的一条记录。
select 1 from 表 where yongtu=a.yongtu and b>a.t
)
insert into 表 select 'a1',1.20,5.00,'用途1'
insert into 表 select 'a2',1.20,6.00,'用途1'
insert into 表 select 'a3',1.30,6.00,'用途2'
select name,cast(b/a as decimal(20,2)) as ba,yongtu from 表 a
where not exists( select 1 from 表 where yongtu=a.yongtu and b>a.b )name ba yongtu
a2 5.00 用途1
a3 4.62 用途2
set nocount on;
declare @T table([name] nvarchar(2),[a] decimal(38,2),[b] decimal(38,2),[yongtu] nvarchar(3))
Insert @T
select N'a1',1.20,5.00,N'用途1' union all
select N'a2',1.20,6.00,N'用途1' union all
select N'a3',1.30,6.00,N'用途2'
Select
[name],cast(b/a as decimal(18,2)) as ba,[yongtu]
from
@T a
where
cast(b/a as decimal(18,2)) =(select max(cast(b/a as decimal(18,2))) from @T where [yongtu]=a.[yongtu])
name ba yongtu
---- --------------------------------------- ------
a2 5.00 用途1
a3 4.62 用途2
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(2),a numeric(3,2),b numeric(3,2),yongtu varchar(5))
insert into #T
select 'a1',1.20,5.00,'用途1' union all
select 'a2',1.20,6.00,'用途1' union all
select 'a3',1.30,6.00,'用途2'select a.name,b.ba,a.yongtu from #T as a
inner join (select yongtu,cast(max(b/a)as numeric(18,2)) as ba from #T group by yongtu) as b on a.yongtu=b.yongtu
and cast(a.b/a.a as numeric(18,2))=b.ba/*
a2 5.00 用途1
a3 4.62 用途2
*/
这个不对.你再加一个
select 'a3',0.30,4.00,'用途2'
试试.
cast(b as numeric(18,2))
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(2),a varchar(200),b varchar(200),yongtu varchar(5))
insert into #T
select 'a1',1.20,5.00,'用途1' union all
select 'a2',1.20,6.00,'用途1' union all
select 'a3',1.30,6.00,'用途2'
select a.name,b.ba,a.yongtu from #T as a
inner join (select yongtu,cast(max(cast(b as numeric(18,2))/cast(a as numeric(18,2)))as numeric(18,2)) as ba from #T group by yongtu) as b on a.yongtu=b.yongtu
and cast(cast(a.b as numeric(18,2))/cast(a.a as numeric(18,2)) as numeric(18,2))=b.ba/*
a2 5.00 用途1
a3 4.62 用途2
*/
insert into 表 select 'a1',1.20,5.00,'用途1'
insert into 表 select 'a2',1.20,6.00,'用途1'
insert into 表 select 'a3',1.30,6.00,'用途2'
insert into 表 select 'a3',0.30,4.00,'用途2' select name,cast(cast(b as decimal(20,10))/cast(a as decimal(20,10)) as decimal(20,2)) as ba,yongtu from 表 a
where not exists(
select 1 from 表 where yongtu=a.yongtu and cast(b as decimal(20,10))/cast(a as decimal(20,10))>cast(a.b as decimal(20,10))/cast(a.a as decimal(20,10))
)name ba yongtu
a2 5.00 用途1
a3 4.62 用途2
我是又错了,是b/a 的值最大哦