qty2列里150这个数据是怎么来的?
解决方案 »
- 解发器统计贴子数目
- 求SQL语句!谢谢
- 求SQL语句两条
- 除了用第三方工具,还有没有其他办法查看SQL日志?
- 安装sqlserver时设置的最大客户端连接数,安装好后在什么地方可以查看和修改?
- 如何用sql语句删除主键约束,并且在修改了列属性之后再把主键约束加上(放分100,解决问题立即相送,决不食言)
- sql server 2000
- 把数字分组 例如:把0到9 这些数分成456,721,647 这几类
- sql2005中in和not in 联用的问题
- 用windows登陆SQLServer 显示 用户 'sa' 登录失败
- 求一个存储过程的写法!
- 没有为文件组 'INDEX' 指派文件。只有添加文件后,才能在此文件组上填充表、索引和 text、ntext 和 image 列。
原表:
ID week item qty qty2
1 1 2 50
2 2 2 50
3 3 2 50
4 5 1 100
5 6 1 100
6 7 1 100
7 8 1 100想要的结果:ID week item qty qty2
1 1 2 50
2 2 2 50
3 3 2 50
8 4 50
4 5 1 100 50
5 6 1 100 150
6 7 1 100 100
7 8 1 100 100
update 原表
set qty2=a.qty
from 原表,
(select week+3,qty
from 原表
where item=2) a
where 原表.week=a.week and 原表.item=2
CREATE TABLE [dbo].[原表](
[ID] [int] NULL,
[week] [int] NULL,
[item] [int] NULL,
[qty] [int] NULL,
[qty2] [int] NULL
) ON [PRIMARY]goINSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (1,1,2,50);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (2,2,2,50);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (3,3,2,50);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (4,5,1,100);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (5,6,1,100);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (6,7,1,100);
INSERT INTO dbo.原表 (ID,week,item,qty)
VALUES (7,8,1,100);现在需要
qty 2 的数据更新
Item 为2的往下顺延3个week
Item 为1的往下顺延1个week
想要的结果:ID week item qty qty2
1 1 2 50
2 2 2 50
3 3 2 50
8 4 50
4 5 1 100 50
5 6 1 100 150
6 7 1 100 100
7 8 1 100 100
CREATE TABLE [dbo].[a](
[ID] [int] NULL,
[week] [int] NULL,
[item] [int] NULL,
[qty] [int] NULL,
[qty2] [int] NULL
) ON [PRIMARY]goINSERT INTO dbo.a (ID,week,item,qty)
VALUES (1,1,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (2,2,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (3,3,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (4,5,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (5,6,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (6,7,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (7,8,1,100);
GO
INSERT INTO a (week)
SELECT week
FROM (SELECT week + 3 AS week FROM a WHERE (item = 2)
UNION
SELECT week + 1 AS week FROM a WHERE (item = 1)) a
WHERE (week NOT IN (SELECT week FROM a))
GO
SELECT b.ID, b.week, b.item, b.qty, a.qty2
FROM (SELECT week, SUM(qty2) AS qty2
FROM (SELECT week + 3 AS week, qty AS qty2
FROM a
WHERE (item = 2)
UNION ALL
SELECT week + 1 AS week, qty AS qty2
FROM a
WHERE (item = 1)) a
GROUP BY week) a RIGHT OUTER JOIN
a b ON a.week = b.week
DROP TABLE A
qty数据换一下好像就不对了。
create table week4(ID int,week int,item int,qty int,qty2 int)
insert into week4 values(1,1,2,50,null)
insert into week4 values(1,2,2,50,null)
insert into week4 values(1,3,2,50,null)
insert into week4 values(1,5,1,100,null)
insert into week4 values(1,6,1,100,null)
insert into week4 values(1,7,1,100,null)
insert into week4 values(1,8,1,100,null)select *from week4
*/
update week4
set qty2=w.qty2
from(
select (case when item=2 then week+3 else week+1 end) week,sum(qty) qty2
from week4
group by (case when item=2 then week+3 else week+1 end)
)w
where w.week=week4.weekinsert into week4 select 1,a.week,null,null,a.qty2--如果ID是自动编号,那么"1"可以去掉
from( select (case when item=2 then week+3 else week+1 end) week,sum(qty) qty2
from week4
group by (case when item=2 then week+3 else week+1 end)
)a
left join week4 w on a.week=w.week
where w.id is null