假如有表格a列数有,查找出相同名称的基础上,UOM存在不同的记录,如下示例数据
name UOM
A 1
A 1
B 2
B 2
B 3
C 1
C 1
D 2
D 3
D 4查找的结果是
B
D
如果能显示不同的UOM的数量更好如
B 2
D 3
name UOM
A 1
A 1
B 2
B 2
B 3
C 1
C 1
D 2
D 3
D 4查找的结果是
B
D
如果能显示不同的UOM的数量更好如
B 2
D 3
(select distinct * from a) a group by name having count(UOM)>1
-------------
不是全出來了?
(name Varchar(10),
UOM Int)
Insert A Select 'A', 1
Union All Select 'A', 1
Union All Select 'B', 2
Union All Select 'B', 2
Union All Select 'B', 3
Union All Select 'C', 1
Union All Select 'C', 1
Union All Select 'D', 2
Union All Select 'D', 3
Union All Select 'D', 4
GO
Select name, Count(Distinct UOM) As Count From A Group By name Having Count(Distinct UOM) > 1
GO
Drop Table A
--Result
/*
name Count
B 2
D 3
*/
B 2
D 3 ----------------
2,3 應是UOM的值,不是統計個數吧
declare @t table(name varchar(10),UOM int)
insert @t select 'A',1
union All select 'A',1
union All select 'B',2
union All select 'B',2
union All select 'B',3
union All select 'C',1
union All select 'C',1
union All select 'D',2
union All select 'D',3
union All select 'D',4
select name,count(distinct UOM) 个数
from @t group by name having count(distinct UOM)>1
--测试结果
--------------------------------------------------
name 个数
B 2
D 3
---------------------------------------------------
学着写了一个。