 加好友  发短信
等级：四尾狐
帖子：858
积分：6381
威望：0
精华：0
注册：2017/2/13 9:04:00
|
请教select子句的运用  Post By：2018/5/31 14:42:00 [显示全部帖子]
select a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname,MAX(c.PriceA_NoInvo) as PriceA_NoInvo f rom ms_code a left join MS_Fty b on a.Fty=b.Fty left join ms_code_price c on a.code=c.code where C.code='10221045019' group by a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname
(select (select Max(NewPRICE) f rom (values (c.PriceA_NoInvo),(c.PriceB_NoInvo),(c.PriceC_NoInvo),(c.PriceD_NoInvo),(c.PriceE_NoInvo), (c.PriceF_NoInvo),(c.PriceG_NoInvo),(c.PriceS_NoInvo),(c.PriceM_NoInvo),(c.PriceL_NoInvo),(c.PriceXL_NoInvo)) as #temp(NewPRICE)) as PriceA_NoInvo f rom ms_code_price)
将黄色select子句代替橙色部分，放进去之后 select a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname, (select (select Max(NewPRICE) f rom (values (c.PriceA_NoInvo),(c.PriceB_NoInvo),(c.PriceC_NoInvo),(c.PriceD_NoInvo),(c.PriceE_NoInvo), (c.PriceF_NoInvo),(c.PriceG_NoInvo),(c.PriceS_NoInvo),(c.PriceM_NoInvo),(c.PriceL_NoInvo),(c.PriceXL_NoInvo)) as #temp(NewPRICE)) as PriceA_NoInvo f rom ms_code_price) f rom ms_code a left join MS_Fty b on a.Fty=b.Fty left join ms_code_price c on a.code=c.code where C.code='10221045019' group by a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname
提示： 选择列表中的列 'ms_code_price.PriceA_NoInvo' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
改成： select a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname, (select (select Max(NewPRICE) f rom (values (c.PriceA_NoInvo),(c.PriceB_NoInvo),(c.PriceC_NoInvo),(c.PriceD_NoInvo),(c.PriceE_NoInvo), (c.PriceF_NoInvo),(c.PriceG_NoInvo),(c.PriceS_NoInvo),(c.PriceM_NoInvo),(c.PriceL_NoInvo),(c.PriceXL_NoInvo)) as #temp(NewPRICE)) as PriceA_NoInvo f rom ms_code_price) f rom ms_code a left join MS_Fty b on a.Fty=b.Fty left join ms_code_price c on a.code=c.code where C.code='10221045019' group by a.stockunit, a.ColorBegNum, a.minnum, a.fConvert, a.Weight, a.colthl, a.code,A.Kind_Desc,a.Model_Desc,a.WLBYEnd_Desc,a.name,a.useunit,a.Stru,a.PubPrice,b.ftyname, c.PriceA_NoInvo,c.PriceB_NoInvo,c.PriceC_NoInvo,c.PriceD_NoInvo,c.PriceE_NoInvo,c.PriceF_NoInvo,c.PriceG_NoInvo,c.PriceS_NoInvo,c.PriceM_NoInvo,c.PriceL_NoInvo,c.PriceXL_NoInvo 又提示： 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。 警告: 聚合或其他 SET 操作消除了 Null 值。 [此贴子已经被作者于2018/5/31 14:55:15编辑过]
|
|
