@Did是商家ID没有重复的
@Pid是产品报价ID没有重复的
@auto_tem类型ID “1”代表主推参品但指能有5条主推产品!
下面的存储过程是更新这个@auto_tem字段
现在的问题是我想在更新这个字段的同时查询出@auto_tem字段等于1的条数不能出现5条,如果是大于5条按照时间字段auoto_sj,去最新的5条,并且其它的数据@auto_tem字段更新为null
请大伙帮这看看!
----------------------
CREATE PROCEDURE AutoPrice_Tem
@Did int,
@Pid int,
@auto_tem int
AS
UPDATE AutoPrice SET
[auto_tem] = @auto_tem
WHERE [PriceID] = @Pid and [DealerID] = @Did
GO
@Pid是产品报价ID没有重复的
@auto_tem类型ID “1”代表主推参品但指能有5条主推产品!
下面的存储过程是更新这个@auto_tem字段
现在的问题是我想在更新这个字段的同时查询出@auto_tem字段等于1的条数不能出现5条,如果是大于5条按照时间字段auoto_sj,去最新的5条,并且其它的数据@auto_tem字段更新为null
请大伙帮这看看!
----------------------
CREATE PROCEDURE AutoPrice_Tem
@Did int,
@Pid int,
@auto_tem int
AS
UPDATE AutoPrice SET
[auto_tem] = @auto_tem
WHERE [PriceID] = @Pid and [DealerID] = @Did
GO
解决方案 »
- 请问怎样增加IIS Serve中的站点?
- ★◆关于Response.Buffer的疑问
- 求一段多表联查的SQL语句
- 关于asp.net中datagrid控件内编辑框高度的设置问题,求助
- Frameset在ASP.NET2.0使用的问题,请高手指点。
- 有没有谁熟悉SMIL?
- 如何显示和隐藏多个div
- irtualItemCount在msdn里说是获取或设置在使用自定义分页时 DataGrid 控件中的实际项数,到底什么意思?
- 如何用Javascript 得到asp:radiobuttonlist的selecteditem值
- 请问js标签<object>的问题
- 调式程序出现以下错误:试图运行项目出错;无法在web服务器上启动调试。调试失败,因为没有启用集成windows身份验证
- 断点跟不进去???
select count(auto_tem) from table where auto_tem=1
如果有大于5,然后再查出最大时间那条
select top 1 id from table order by auoto_sj desc 查出来是id
然后楼主就可以更新了..
UPDATE AutoPrice SET [auto_tem] = @auto_tem再根据时间取前5条
UPDATE AutoPrice SET [auto_tem] = 2 WHERE [PriceID] IN (SELECT [PriceID] FROM AutoPrice WHERE [auto_tem] = 1 ORDER BY auoto_sj DESC )UPDATE AutoPrice SET [auto_tem] = NULL WHERE [auto_tem] !=2
UPDATE AutoPrice SET [auto_tem] = @auto_tem WHERE [PriceID] = @Pid and [DealerID] = @Did
再根据时间取前5条
UPDATE AutoPrice SET [auto_tem] = 2 WHERE [PriceID] IN (SELECT [PriceID] FROM AutoPrice WHERE [auto_tem] = 1 ORDER BY auoto_sj DESC )UPDATE AutoPrice SET [auto_tem] = NULL WHERE [auto_tem] !=2
UPDATE AutoPrice SET [auto_tem] = @auto_tem WHERE [PriceID] = @Pid and [DealerID] = @Did
再根据时间取前5条
UPDATE AutoPrice SET [auto_tem] = 2 WHERE [PriceID] IN (SELECT TOP 5 [PriceID] FROM AutoPrice WHERE [auto_tem] = 1 ORDER BY auoto_sj DESC )UPDATE AutoPrice SET [auto_tem] = NULL WHERE [auto_tem] !=2
UPDATE AutoPrice SET [auto_tem] = @auto_tem WHERE [PriceID] = @Pid and [DealerID] = @Did
再根据时间取前5条
UPDATE AutoPrice SET [auto_tem] = 2 WHERE [PriceID] IN (SELECT TOP 5 [PriceID] FROM AutoPrice WHERE [auto_tem] = 1 ORDER BY auoto_sj DESC )UPDATE AutoPrice SET [auto_tem] = NULL WHERE [auto_tem] !=2UPDATE AutoPrice SET [auto_tem] = 1 WHERE [auto_tem] =2
完了,有点麻烦.再想想好点的办法,但此法结果不会错