分组查询问题

[shizhusz110]

又来问了，昨天问了一个自己开始没想出来，程序分了几步解决，最后看到支持愧疚。
有如下数据:
Lv,Class,ID

1,1,1
1,1,2
1,1,3
1,1,4
1,1,5
1,1,6
1,1,7
1,1,8
1,1,9

要求按照Lv和Class每5个分组，并且取出每组必须有5个的数据，如果分组后只有1或2或3或4个都不取出.

[shizhusz110]

谢谢大家 为什么版主大人的sql运行在2008通不过

[Tiger_Zhao]

WITH
/* 测试数据
table1(Lv,Class,ID) AS (
    SELECT 1,1,1 UNION ALL
    SELECT 1,1,2 UNION ALL
    SELECT 1,1,3 UNION ALL
    SELECT 1,1,4 UNION ALL
    SELECT 1,1,5 UNION ALL
    SELECT 1,1,6 UNION ALL
    SELECT 1,1,7 UNION ALL
    SELECT 1,1,8 UNION ALL
    SELECT 1,1,9  
), */
a AS ( -- 用 MAX(ID) 求最大的完整分组号
    SELECT Lv,Class,
           (MAX(ID)-1-4)/5 max_group_no -- 关键是：-4
      FROM table1
  GROUP BY Lv,Class
)
SELECT t.Lv,
       t.Class,
       t.ID,
       (t.ID-1)/5 group_no
  FROM table1 t
  JOIN a
    ON t.Lv = a.Lv
   AND t.Class = a.Class
   AND (t.ID-1)/5 <= a.max_group_no

         Lv       Class          ID    group_no
----------- ----------- ----------- -----------
          1           1           1           0
          1           1           2           0
          1           1           3           0
          1           1           4           0
          1           1           5           0

[中国风]

如果分组后只有1或2或3或4个都不取出.

这指ID值么？

;WITH Cte
AS
(
SELECT  Lv
       ,Class
       ,ID
FROM    TabName AS a
WHERE   EXISTS ( SELECT 1
                 FROM   Cte
                 WHERE  Lv = a.Lv
                        AND Class = a.Class
                 HAVING COUNT(1) = 5 )
)
SELECT Lv,Class,ID FROM Cte AS a WHERE NOT EXISTS(SELECT 1 FROM Cte WHERE Lv=a.Lv AND Class=a.Class  HAVING COUNT(DISTINCT ID)=1)

[Yole]

--测试数据
create table #tab 
(
Lv int,Class int,ID int
)
insert into #tab
select 1,1,1 union all
select 1,1,2 union all
select 1,1,3 union all
select 1,1,4 union all
select 1,1,5 union all
select 1,1,6 union all
select 1,1,7 union all
select 1,1,8 union all
select 1,1,9

--查询
select a.*,b.[group] from #tab a 
inner join
( 
select Lv,Class,(ID-1)/5  as [group] from #tab
group by Lv,Class,(ID-1)/5
having COUNT((ID-1)/5)=5
)b on a.Lv=b.Lv and a.Class=b.Class and (a.ID-1)/5=b.[group]

--结果
/*
Lv          Class       ID          group
----------- ----------- ----------- -----------
1           1           1           0
1           1           2           0
1           1           3           0
1           1           4           0
1           1           5           0

(5 行受影响)

*/

[shizhusz110]

Id是自增长Int

[中国风]

引用 3 楼 shizhusz110 的回复:

Id是自增长Int

这样的效果？ 不等于5个记录都不出现（大于5条或小于5条）

SELECT  Lv
       ,Class
       ,ID
FROM    TabName AS a
WHERE   EXISTS ( SELECT 1
                 FROM   Cte
                 WHERE  Lv = a.Lv
                        AND Class = a.Class
                 HAVING COUNT(1) = 5 )