Question

怎么写一条sql语句，如果用户名username相同和商品编号goodsid相同，则把该商品的数量nums加一？





Answer 1

语句：
update table
set nums = 2
from
(
select goodsid,username from table
group by username
having count(goodsid) = 2
) a
where table.goodsid = a.goodsid
and table.username = a.username

追问

是多表查询吗，查一张表怎么做，我只查询cart表怎么做

追答

那就把table改成cart
update cart
set nums = 2
from
(
select goodsid,username from cart
group by username
having count(goodsid) = 2
) a
where table.goodsid = a.goodsid
and table.username = a.username

更新之前，先执行
select goodsid,username from cart
group by username
having count(goodsid) = 2
看看是否相同的都查询出来了

追问

这是什么错，select goodsid,username from cart group by goodsid,username having count(goodsid) = 2;可以执行，第二条语句不能执行，报错

消息 156，级别 15，状态 1，第 1 行

关键字 'select' 附近有语法错误。

消息 102，级别 15，状态 1，第 1 行

')' 附近有语法错误。

追答

语句改一下：
update cart
set nums = 2
from
(
select goodsid,username from cart
group by username,goodsid
having count(*) = 2
) a
where table.goodsid = a.goodsid
and table.username = a.username

先执行
select goodsid,username from cart
group by username,goodsid
having count(*) = 2
看看是否是你要的条件相同的。

追问

update cart set nums = nums+1 from cart(select goodsid,username from cart group by username having count(goodsid) = 2) where cart.goodsid = cart.goodsid and cart.username = cart.username;我这样写不行

追答

update cart
set nums = nums+1
from
(select goodsid,username from cart group by username having count(goodsid) = 2) ---这里要加表别名
where cart.goodsid = cart.goodsid --- 条件这样判断有错的。会表自己关联自己判断的
and cart.username = cart.username ---
写的语法有错吧。

追问

哦哦，谢谢！非常满意！你那么耐心啊