请教大家一个问题,下面这样一个表:
material assy# Build Date wipdemand
11-1731-01$1 73-10098-18 2011-08-24 432.000
11-1731-01$1 73-10098-18 2011-08-20 432.000
11-1731-01$1 73-12886-04 2011-08-26 400.000
11-1731-01$1 73-12886-04 2011-08-17 400.000
11-2286-01$1 73-10098-18 2011-08-20 378.000
11-2286-01$1 73-10098-18 2011-08-24 378.000
11-2286-01$1 73-10899-16C 2011-08-18 350.000
11-2286-01$1 73-10899-17 2011-08-28 70.000一个条件,在相同material,相同assy#前提下,该条件下的第一行不变,其它行更新wipdemand为0.sql 如何写?
material assy# Build Date wipdemand
11-1731-01$1 73-10098-18 2011-08-24 432.000
11-1731-01$1 73-10098-18 2011-08-20 432.000
11-1731-01$1 73-12886-04 2011-08-26 400.000
11-1731-01$1 73-12886-04 2011-08-17 400.000
11-2286-01$1 73-10098-18 2011-08-20 378.000
11-2286-01$1 73-10098-18 2011-08-24 378.000
11-2286-01$1 73-10899-16C 2011-08-18 350.000
11-2286-01$1 73-10899-17 2011-08-28 70.000一个条件,在相同material,相同assy#前提下,该条件下的第一行不变,其它行更新wipdemand为0.sql 如何写?
如果这样,只要更新 material, assy# 相同, 并且Created Date > min(Created Date)就可以了。
Created Date > min(Created Date) 这个条件不行啊,我这个表是SELECT 出来的。CreateDate都是同一个时间生成的。
假设表名称为Table1 ,db为datacontact 的一个实例var query=(from u in db.table
group u by u.material into g
select new
{
g.key,
Count=(from n in g
group n by n.iassy# into m
select m).take(1)
}foreach(var v in query)
{
console.WriteLine(v.key)
console.WriteLine(v.g.material+" "+v.g.assy# Build+" "+v.g.Build_Date+" "+v.g.wipdemand
)
}
set wipdemand=0
from tb a
where exists(select 1
from tb b
where b.material=a.material
and b.assy#=a.assy#
and b.wipdemand=a.wipdemand
and b.BuildDate>a.BuildDate)
tb是表名,默认第一条是日期最小的一条,且日期不会重复。
set wipdemand = 0
from tb t
where id not in (select min(id) from tb group by material,assy#)
and b.BuildDate>a.BuildDate)这里应该是and b.BuildDate<a.BuildDate才对,刚才搞错了
tb
set
wipdemand=0
from
tb t
where
BuildDate=(select max(b.BuildDate) from tb where material=t.material and b.assy#=a.assy# and b.wipdemand=a.wipdemand )
BuildDate=(select max(b.BuildDate) from tb where material=t.material and b.assy#=a.assy# and b.wipdemand=a.wipdemand )