select count(distinct left(mo,4)) from tb group by left(mo,4) having count(*)>1应该只有3条吧
select distinct left(No,4) from tb
难道你想着这样 --> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([no] VARCHAR(6)) INSERT [tb] SELECT '2356' UNION ALL SELECT '2234-1' UNION ALL SELECT '2234-2' UNION ALL SELECT '2234-3' UNION ALL SELECT '2135' UNION ALL SELECT '2236' UNION ALL SELECT '2567-1' UNION ALL SELECT '2567-1' --------------开始查询-------------------------- SELECT SUM(cnt) FROM ( SELECT COUNT(*) AS cnt FROM tb GROUP BY LEFT([no], 4) HAVING COUNT(*) > 1 ) AS t
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([no] VARCHAR(6))
INSERT [tb]
SELECT '2356' UNION ALL
SELECT '2234-1' UNION ALL
SELECT '2234-2' UNION ALL
SELECT '2234-3' UNION ALL
SELECT '2135' UNION ALL
SELECT '2236' UNION ALL
SELECT '2567-1' UNION ALL
SELECT '2567-1'
--------------开始查询--------------------------
SELECT SUM(cnt)
FROM (
SELECT COUNT(*) AS cnt
FROM tb
GROUP BY LEFT([no], 4)
HAVING COUNT(*) > 1
) AS t