谁给出个招吧

[想飞的狼]

有个表

CREATE TABLE [dbo].[ysb] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[产品名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[规格] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[产地] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[批发价] [float] NOT NULL ,
[零售价] [float] NOT NULL ,
[销售价3] [float] NOT NULL ,
[进货单位] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[最低限量] [int] NOT NULL ,
[数量] [numeric](18, 0) NOT NULL ,
[单价] [float] NOT NULL ,
[金额] [numeric(18,2)] NOT NULL
) ON [PRIMARY]
GO


进货单位都是以T开头的字符,现在想知道每一个T开头的客户销售多少金额
select 进货单位,sum(金额) from ysb group by 进货单位

T1 10
T2 30.00
TC 32.20
T10 23.00
问题出现了,那么我想当单价<销售价3时无论'进货单位'是什么都按T1算,>=销售价3时正常,表该怎么写呢?

[dobear_0922]

--try
select   进货单位=case when [单价]<[销售价3] then 'T1' else [进货单位] end, sum(金额)   
from   ysb   
group   by   进货单位 

[bqb]

select
.
.
.

case when [单价]<[销售价3] then
(select sum(金额) from ysb where [进货单位]='T1') else [单价]
then end as [单价]

from ysb

[wzy_love_sly]

select 进货单位,sum(金额) from (
select case when [单价]<销售价 then 't1' else 进货单位 end as '进货单位2',* from ysb
) tp group by 进货单位2

[kk19840210]

select 进货单位,金额=sum(金额) from
(
select 进货单位,金额=case when [单价]>=[销售价3] then 金额 else (select top 1 金额 from ysb where 进货单位='T1') end   from   ysb
) a   group   by   a.进货单位

[dobear_0922]

--try
select [进货单位]=进货单位, [金额]=sum(金额)
from
(
	select   进货单位=case when [单价]<[销售价3] then 'T1' else [进货单位] end, 金额   
	from   ysb
) T 
group   by   进货单位 

[pt1314917]

select   case when 单价 <销售价3 then 'T1' else 进货单位 end [进货单位],
sum(金额)   from   ysb   group   by   case when 单价 <销售价3 then 'T1' else 进货单位 end

[kk19840210]

或者也有可能

select   进货单位=case when [单价]<[销售价3] then 'T1' else [进货单位] end, 金额=sum(金额)   
from   ysb   
group   by   进货单位 

[bqb]

理解错了！

[$扫地僧$]

select 进货单位,sum(金额) as 金额
from
(
    select case when 单价<销售价3 then 'T1' else 进货单位 end as 进货单位,
          金额
     from ysb
) T
group by 进货单位