表Test
有四个字段,code是主键
code(单号),colorCode(色号),wlCode(物料号),nums(数量)有数据如下(部分):
code,colorCode,wlCode,nums
0011 A11 11 10
0011 A11 12 21
0011 A11 13 32
0012 A11 12 18
0012 A11 13 12
0013 A11 11 78
0013 A11 12 5
0013 A11 13 5
0013 A11 14 35
0014 A12 11 15
0014 A12 12 56
0015 A13 11 5
0015 A13 12 35
0015 A13 13 8
0016 A13 11 19
0016 A13 12 25
0016 A13 11 10
....
A11色号对应0011,0012,0013三张单号
A12色号对应0014一张单号
A13色号对应0015,0016两张单号问题:我只要找出每种色号第一张单的所有物料的数量结果:code colorCode nums
0011 A11 33
0014 A12 71
0015 A13 48
...
有四个字段,code是主键
code(单号),colorCode(色号),wlCode(物料号),nums(数量)有数据如下(部分):
code,colorCode,wlCode,nums
0011 A11 11 10
0011 A11 12 21
0011 A11 13 32
0012 A11 12 18
0012 A11 13 12
0013 A11 11 78
0013 A11 12 5
0013 A11 13 5
0013 A11 14 35
0014 A12 11 15
0014 A12 12 56
0015 A13 11 5
0015 A13 12 35
0015 A13 13 8
0016 A13 11 19
0016 A13 12 25
0016 A13 11 10
....
A11色号对应0011,0012,0013三张单号
A12色号对应0014一张单号
A13色号对应0015,0016两张单号问题:我只要找出每种色号第一张单的所有物料的数量结果:code colorCode nums
0011 A11 33
0014 A12 71
0015 A13 48
...
问:主键怎么这么多重复的?
alter table test add id int identity
select * from test a where a.id=(select top 1 id from test where a.colorCode=colorCode order by id desc)
有四个字段
code(单号),colorCode(色号),wlCode(物料号),nums(数量)有数据如下(部分):
code,colorCode,wlCode,nums
0011 A11 11 10
0011 A11 12 21
0011 A11 13 32
0012 A11 12 18
0012 A11 13 12
0013 A11 11 78
0013 A11 12 5
0013 A11 13 5
0013 A11 14 35
0014 A12 11 15
0014 A12 12 56
0015 A13 11 5
0015 A13 12 35
0015 A13 13 8
0016 A13 11 19
0016 A13 12 25
0016 A13 11 10
....
A11色号对应0011,0012,0013三张单号
A12色号对应0014一张单号
A13色号对应0015,0016两张单号问题:我只要找出每种色号第一张单的所有物料的数量结果:code colorCode nums
0011 A11 33
0014 A12 71
0015 A13 48
...
from test a
where code=(select top 1 code from test where colorCode=a.colorCode order by code)
group by code,colorCode--你的描述有问题
--1、code是主键,但是数据表明code可以重复,不可能是主键
--2、结果
0011 A11 33
好像应该是
0011 A11 63
DECLARE @tb TABLE([code] varchar(10), [colorCode] varchar(10), [wlCode] int, [nums] int)
INSERT INTO @tb
SELECT '0011', 'A11', 11, 10
UNION ALL SELECT '0011', 'A11', 12, 21
UNION ALL SELECT '0011', 'A11', 13, 32
UNION ALL SELECT '0012', 'A11', 12, 18
UNION ALL SELECT '0012', 'A11', 13, 12
UNION ALL SELECT '0013', 'A11', 11, 78
UNION ALL SELECT '0013', 'A11', 12, 5
UNION ALL SELECT '0013', 'A11', 13, 5
UNION ALL SELECT '0013', 'A11', 14, 35
UNION ALL SELECT '0014', 'A12', 11, 15
UNION ALL SELECT '0014', 'A12', 12, 56
UNION ALL SELECT '0015', 'A13', 11, 5
UNION ALL SELECT '0015', 'A13', 12, 35
UNION ALL SELECT '0015', 'A13', 13, 8
UNION ALL SELECT '0016', 'A13', 11, 19
UNION ALL SELECT '0016', 'A13', 12, 25
UNION ALL SELECT '0016', 'A13', 11, 10
SELECT code, colorCode, SUM(NUMS) AS NUMS FROM @tb A
WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE colorCode = A.colorCode AND code < A.code)
GROUP BY code, colorCode--结果
code colorCode NUMS
---------- ---------- -----------
0011 A11 63
0014 A12 71
0015 A13 48(所影响的行数为 3 行)
重申,code不是主键
重申,code不是主键