34,838
社区成员




--建立测试环境
set nocount on
create table DailyReportTemp02(px varchar(20),CreateDate varchar(20),GaName varchar(20),线别 varchar(20),代码 varchar(20),qty int,机型 varchar(20),Place varchar(20))
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','C4','20','CL-323','1'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','C3','10','CL-323','2'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','C7','5','CL-323','3'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','C5','5','CL-323','3'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','C6','3','CL-323','4'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','NULL','1','CL-323','5'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','33','1','CL-323','5'
insert into DailyReportTemp02 select '1','2008-02-18','半成品测试','B线','44','1','CL-323','5'
insert into DailyReportTemp02 select '1','2008-02-18','投入','B线','NULL','1','CL-323','1'
insert into DailyReportTemp02 select '1','2008-02-19','半成品测试','B线','C4','19','CL-323','1'
insert into DailyReportTemp02 select '1','2008-02-19','半成品测试','B线','C3','11','CL-323','2'
insert into DailyReportTemp02 select '1','2008-02-19','半成品测试','B线','44','2','CL-323','3'
insert into DailyReportTemp02 select '1','2008-02-19','半成品测试','B线','NULL','1','CL-323','4'
insert into DailyReportTemp02 select '1','2008-02-19','成品测试','B线','C8','60','CL-323','1'
insert into DailyReportTemp02 select '1','2008-02-19','成品测试','B线','C4','15','CL-323','2'
insert into DailyReportTemp02 select '1','2008-02-19','成品测试','B线','C3','10','CL-323','3'
insert into DailyReportTemp02 select '1','2008-02-19','成品测试','B线','NULL','3','CL-323','4'
go
--测试
SELECT *, Place =
(SELECT COUNT(*)
FROM DailyReportTemp02
WHERE qty -checksum(代码)/10000000000 > =t.qty -checksum(t.代码)/10000000000 AND CreateDate = t .CreateDate AND
GaName = t .GaName)
FROM DailyReportTemp02 t
--删除测试环境
drop table DailyReportTemp02
set nocount off
/*
1 2008-02-18 半成品测试 B线 C4 20 CL-323 1 1
1 2008-02-18 半成品测试 B线 C3 10 CL-323 2 2
1 2008-02-18 半成品测试 B线 C7 5 CL-323 3 3
1 2008-02-18 半成品测试 B线 C5 5 CL-323 3 4
1 2008-02-18 半成品测试 B线 C6 3 CL-323 4 5
1 2008-02-18 半成品测试 B线 NULL 1 CL-323 5 6
1 2008-02-18 半成品测试 B线 33 1 CL-323 5 7
1 2008-02-18 半成品测试 B线 44 1 CL-323 5 8
1 2008-02-18 投入 B线 NULL 1 CL-323 1 1
1 2008-02-19 半成品测试 B线 C4 19 CL-323 1 1
1 2008-02-19 半成品测试 B线 C3 11 CL-323 2 2
1 2008-02-19 半成品测试 B线 44 2 CL-323 3 3
1 2008-02-19 半成品测试 B线 NULL 1 CL-323 4 4
1 2008-02-19 成品测试 B线 C8 60 CL-323 1 1
1 2008-02-19 成品测试 B线 C4 15 CL-323 2 2
1 2008-02-19 成品测试 B线 C3 10 CL-323 3 3
1 2008-02-19 成品测试 B线 NULL 3 CL-323 4 4
*/