日期:2014-05-18  浏览次数:20561 次

统计记录数
编号如下:
No
2356
2234-1
2234-2
2234-3
2135
2236
2567-1
2567-1

如何统计出前四位No一样的共5条记录?


------解决方案--------------------
select distinct(left(no,4)) from tb
------解决方案--------------------
SQL code
select count(distinct  left(mo,4)) from tb group by left(mo,4) having count(*)>1

------解决方案--------------------
select distinct left(No,4) from tb


------解决方案--------------------
难道你想着这样

SQL code

--> 测试数据:[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