商品表ID就是订单字表的protudId吗? 再加个起始时间参数qishi public int DingzhiTCount(DateTime now, int id, DateTime qishi) { string sql = "update dt_dingzhi set shangjia = 0 where id = " + id +"and ...这里面怎么写?....; return shangjia ; }
给商品表加个“已卖出数量”,貌似简洁些。 那又怎么写sql?原表里只有浏览字段,上架字段
string sql = "update dt_dingzhi set shangjia = 0 where id = " + id ; 这样不就完了,还要写什么?
string sql = "update dt_dingzhi set shangjia = 0 where id = " + id + "and sum in (select sum from OrderDetail where oderId=(select oderId from oder where orderType=1 and creadDate <" + qishi + "creadDate>"+now+")))"; 这样写行嘛?
UPDATE dt_dingzhi SET shangjia=0 FROM dt_dingzhi INNER JOIN ( SELECT OrderDetail.protudId,COUNT(OrderDetail.[SUM]) AS n FROM OrderDetail INNER JOIN [order] on OrderDetail.oderid=[order].Oderid WHERE [order].creadDate >@Date AND [order].orderType=5 GROUP BY OrderDetail.protudId HAVING COUNT(OrderDetail.[SUM])>=10 ) LSA ON dt_dingzhi.ID= LSA.protudId
string sql="update dt_dingzhi set shangjia=0 where (select count(*) from OrderDetail join order on order.orderId=oderId where OrderDetail.protudId="+id+" and OrderDetail.detailType=1 and order.orderType=5)>10";
join 一下 update dt_dingzhi set shangjia = 0 from dt_dingzhi join (select count(0) as num,id from OrderDetail group by id) B on dingzhi.id=B.id where dingzhi.id = " + id +" and b.num>10
其实啊,在 string sql = "update dt_dingzhi set shangjia = 0 where id = " + id 句之前,你应该先判断这个id的数量是否大于10,如果大于进行更新,小于直接报个无法更新的提示
update dt_dingzhi set shangjia = 0 where id = " + id + "and (select count(*) from order a left join OrderDetail b on a.orderid= b.orderid where id = " + id + "and orderType=1 and creadDate <" + qishi + "creadDate>"+now+" ) >10
select * from [order] o join OrderDetail d on o.oderId=d.oderId where o.orderType='5' and d.protudId='id' --细表肯定有商品ID吧,这里应该是【protudId】,得到某商品所有已付款订单 update dt_dingzhi set shangjia = 0 where id in( select max(protudId) from (select * from [order] o join OrderDetail d on o.oderId=d.oderId where o.orderType='5' and d.protudId='id') a where sum([a.sum]) >=10 )大概写法,具体自已调试,根据错误提示去修改
update dt_dingzhi set shangjia=0 where product_id in (select a.product_id as product_id from orderDetail a join oder b on a.order_id=b.order and b.order_type='S' group by a.product_id having sum(a.sum)>10) 当子查询语句中的product id数量超过1000这个sql就不适用了 抱怨一句:你这个问题描述实在是不好,你这个数据表设计的实在是不好...没有良好的数据结构就没有良好的程序...
关键字 'order' 附近有语法错误。
写购物车源代码的人用关键词order做表名,cs文件又打包到dll文件里了 现在测试“update dt_dingzhi set shangjia=0 where (select count(*) from OrderDetail INNER JOIN orders on orders.orderId=OrderDetail.oderId where OrderDetail.protudId=177 and and orders.orderType=5)>10”是成功的 表名orders 还原成order或者dbo.order都报错,但是order表名由于上述原因改不了,怎么办列?
这是个一元秒杀的程序啊~~~~~~~~~感谢各位~敬请解决表名order和关键词冲突的问题
使用Order为表名时,加上方括号. 如:SELECT * FROM [Order]
protected void Page_Load(object sender, EventArgs e) { DingzhiManager manager = new DingzhiManager(); DateTime now = DateTime.Now; int idt=121; if (manager.DingzhiTCount(idt) == 0) { Label1.Text = "<img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c3' alt='秒杀结束' />"; } else { Label1.Text = "<a href='../feedback/ms.aspx?id=121' target='_blank'><img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c2' alt='一元秒杀倒计时 4天' style='cursor: pointer;' /></a>"; } } public int DingzhiTCount(int id) { string sql = "update dt_dingzhi set shangjia=0 where (select count(*) from [OrderDetail] INNER JOIN [order] on [order].orderId=[OrderDetail].oderId where [OrderDetail].protudId="+ id+ "and [order].orderType=5)>10"; return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql); } 报错啊~~~~~~~~http://www.ballwest.com/miaosha.aspx 转换 nvarchar 值 '010070800097' 时溢出了整数列。超过了其中最大的整数值。 语句已终止。
你应该确认一下OrderDetail.protudId的类型,是int吗,如果不是int的话:应该string sql = "update dt_dingzhi set shangjia=0 where (select count(*) from [OrderDetail] INNER JOIN [order] on [order].orderId=[OrderDetail].oderId where [OrderDetail].protudId='"+ id+ "' and [order].orderType=5)>10";
晕了。 改成下面这样也不能置shangjia上架字段为0 public int DingzhiTCount(int id) { string sql = "update dt_dingzhi set shangjia=0 where (select count(sum) from [OrderDetail] where [OrderDetail].protudId=" + id + ") > 10"; return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql); } public int DingzhiT(int id) { string sql = "select shangjia from dt_dingzhi where id = " + id; return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql); }
public int DingzhiTCount(int id) { string sql = "update dt_dingzhi set shangjia=0 where (select sum(sum) from [OrderDetail] where [OrderDetail].protudId=" + id + ") > 10 and id=" + id; return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql); } public int DingzhiT(int id) { string sql = "select shangjia from dt_dingzhi where id = " + id; return Convert.ToInt32(SqlHelper.ExecuteScalar(DBConn.ConnectionString, CommandType.Text, sql)); }解决了,感恩~~~~~~~~~~~~~~~~
再加个起始时间参数qishi
public int DingzhiTCount(DateTime now, int id, DateTime qishi)
{
string sql = "update dt_dingzhi set shangjia = 0 where id = " + id +"and ...这里面怎么写?....;
return shangjia ;
}
那又怎么写sql?原表里只有浏览字段,上架字段
这样不就完了,还要写什么?
这样写行嘛?
(
SELECT OrderDetail.protudId,COUNT(OrderDetail.[SUM]) AS n
FROM OrderDetail INNER JOIN [order] on OrderDetail.oderid=[order].Oderid
WHERE [order].creadDate >@Date AND [order].orderType=5
GROUP BY OrderDetail.protudId
HAVING COUNT(OrderDetail.[SUM])>=10
) LSA ON dt_dingzhi.ID= LSA.protudId
update dt_dingzhi set shangjia = 0
from dt_dingzhi join (select count(0) as num,id from OrderDetail group by id) B on dingzhi.id=B.id
where dingzhi.id = " + id +" and b.num>10
string sql = "update dt_dingzhi set shangjia = 0 where id = " + id
句之前,你应该先判断这个id的数量是否大于10,如果大于进行更新,小于直接报个无法更新的提示
(select count(*) from order a left join OrderDetail b on a.orderid= b.orderid
where id = " + id + "and orderType=1 and creadDate <" + qishi + "creadDate>"+now+" )
>10
where o.orderType='5' and d.protudId='id'
--细表肯定有商品ID吧,这里应该是【protudId】,得到某商品所有已付款订单
update dt_dingzhi set shangjia = 0 where id in(
select max(protudId) from (select * from [order] o join OrderDetail d on o.oderId=d.oderId
where o.orderType='5' and d.protudId='id') a where sum([a.sum]) >=10
)大概写法,具体自已调试,根据错误提示去修改
谢谢了
我想返回shangjia怎么写啊,原来的是return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
set shangjia=0
where product_id in
(select a.product_id as product_id
from orderDetail a join oder b
on
a.order_id=b.order and b.order_type='S'
group by a.product_id
having sum(a.sum)>10)
当子查询语句中的product id数量超过1000这个sql就不适用了
抱怨一句:你这个问题描述实在是不好,你这个数据表设计的实在是不好...没有良好的数据结构就没有良好的程序...
现在测试“update dt_dingzhi set shangjia=0 where (select count(*) from OrderDetail INNER JOIN orders on orders.orderId=OrderDetail.oderId where OrderDetail.protudId=177 and and orders.orderType=5)>10”是成功的
表名orders 还原成order或者dbo.order都报错,但是order表名由于上述原因改不了,怎么办列?
如:SELECT * FROM [Order]
{
DingzhiManager manager = new DingzhiManager();
DateTime now = DateTime.Now;
int idt=121;
if (manager.DingzhiTCount(idt) == 0)
{
Label1.Text = "<img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c3' alt='秒杀结束' />";
}
else
{
Label1.Text = "<a href='../feedback/ms.aspx?id=121' target='_blank'><img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c2' alt='一元秒杀倒计时 4天' style='cursor: pointer;' /></a>";
}
} public int DingzhiTCount(int id)
{
string sql = "update dt_dingzhi set shangjia=0 where (select count(*) from [OrderDetail] INNER JOIN [order] on [order].orderId=[OrderDetail].oderId where [OrderDetail].protudId="+ id+ "and [order].orderType=5)>10";
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}
报错啊~~~~~~~~http://www.ballwest.com/miaosha.aspx
转换 nvarchar 值 '010070800097' 时溢出了整数列。超过了其中最大的整数值。
语句已终止。
这两个数据类型一样吗?
没有成功 public int DingzhiTCount(int id)
{
string sql = "update dt_dingzhi set shangjia=0 where (select count(sum) from [OrderDetail] INNER JOIN [order] on [order].id=[OrderDetail].oderId where [OrderDetail].protudId=" + id + "and [order].orderType=4) > 10";
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}
public int DingzhiT(int id)
{
string sql = "select shangjia from dt_dingzhi where id = " + id;
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}miaosha.aspx.cs里代码
public partial class miaosha : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DingzhiManager manager = new DingzhiManager();
int idt=178;
manager.DingzhiTCount(idt);
if (manager.DingzhiT(idt) == 1)
{
Label1.Text = "<img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c2' alt='秒杀结束' />";
}
else
{
Label1.Text = "<a href='../feedback/ms.aspx?id="+idt+"' target='_blank'><img src='../UploadFiles/miaosha/2010.6.7_0_r2_c2.jpg' width='456' height='178' id='n201067_0_r2_c3' alt='一元秒杀倒计时 4天' style='cursor: pointer;' /></a>";
} }
}
order是订单表里面没有商品信息只有订单信息
OrderDetail是订单子表有商品信息包括商品数量
“OrderDetail表里的oderId字段对应[order]表里orderid字段,但是[order]表里orderid字段是nvarchar(50)数据类型由时间+id组成的12位数字,末2位和OrderDetail表里的oderId字段相同”
可以忽略算了我改了下数据,可以对应了。
就是int shja = manager.DingzhiTCount(idt);
不成功置shangjia上架字段为0.。
改成下面这样也不能置shangjia上架字段为0
public int DingzhiTCount(int id)
{
string sql = "update dt_dingzhi set shangjia=0 where (select count(sum) from [OrderDetail] where [OrderDetail].protudId=" + id + ") > 10";
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}
public int DingzhiT(int id)
{
string sql = "select shangjia from dt_dingzhi where id = " + id;
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}
1,OrderDetail.oderId 类型是 nvarchar
2,order.orderid的后两位,保证在order表中的所有行中都是唯一值,你的order表不会超过100行
3,dt_dingzhi表中id类型是int,与OrderDetail.protudId类型相同,行为上是关联字段以上三条,如果第二条不能确认的话,特别是最后两位不重复,那么你的方案没有处理办法,只能重新设置数据库。
{
string sql = "update dt_dingzhi set shangjia=0 where (select sum(sum) from [OrderDetail] where [OrderDetail].protudId=" + id + ") > 10 and id=" + id;
return SqlHelper.ExecuteNonQuery(DBConn.ConnectionString, CommandType.Text, sql);
}
public int DingzhiT(int id)
{
string sql = "select shangjia from dt_dingzhi where id = " + id;
return Convert.ToInt32(SqlHelper.ExecuteScalar(DBConn.ConnectionString, CommandType.Text, sql));
}解决了,感恩~~~~~~~~~~~~~~~~