select TB_USERINFO.userid as 用户ID,username as 用户名,giftprice as 单价
,sum(quantity) as 数量,总价=case
when giftprice>0 then giftprice*sum(quantity)
else 0
end
from tb_vipbuygift join tb_vip on vip_id=vipid join tb_userinfo on tb_userinfo.userid=tb_vip.userid join tb_gift on tb_gift.giftid=TB_VipBuyGift.giftid group by username,giftprice,TB_USERINFO.userid返回了
用户ID 用户名 礼物价格 礼物数量 总价
20 vvvvvv 50 300 15000
20 vvvvvv 500 20 10000
26 pppppp 500 12 6000 结果集,但是不是我想要的,我想在通过一条SQL语句查出不同用户购买所有礼物的总价 就像ID 20用户 购买礼物总价为25000
,sum(quantity) as 数量,总价=case
when giftprice>0 then giftprice*sum(quantity)
else 0
end
from tb_vipbuygift join tb_vip on vip_id=vipid join tb_userinfo on tb_userinfo.userid=tb_vip.userid join tb_gift on tb_gift.giftid=TB_VipBuyGift.giftid group by username,giftprice,TB_USERINFO.userid返回了
用户ID 用户名 礼物价格 礼物数量 总价
20 vvvvvv 50 300 15000
20 vvvvvv 500 20 10000
26 pppppp 500 12 6000 结果集,但是不是我想要的,我想在通过一条SQL语句查出不同用户购买所有礼物的总价 就像ID 20用户 购买礼物总价为25000
when giftprice>0 then giftprice*sum(quantity)
else 0
end
from tb_vipbuygift join tb_vip on vip_id=vipid join tb_userinfo on tb_userinfo.userid=tb_vip.userid join tb_gift on tb_gift.giftid=TB_VipBuyGift.giftid
选择列表中的列 'tb_gift.GiftPrice' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
from ...
group by TB_USERINFO.userid,username