高分求一个复杂的关于分组统计的SQL语句

Question

本帖最后由 xs52121 于 2009-08-25 09:32:08 编辑

Answer 1

AttTime(datetime)    Emp(nvarchar)      Dept(nvarchar) 
2008-08-02 6:45        zhangsan               A 
2008-08-02 18:20       zhangsan                A 
2008-08-02 11:22       zhangsan                A 
2008-08-02 18:20       zhangsan                A 
2008-08-08 7:15        zhangsan                A 
2008-08-08 19:12       zhangsan                A 
2008-08-01 6:41        ww                      A 
2008-08-01 8:41        ww                      A 
2008-08-01 17:20       ww                      A 
2008-08-06 7:11        ww                      A 
2008-08-06 18:12       ww                      A 
2008-08-04 8:13        lisi                    B 

Answer 2

想要得到数据如下： 
即得到指定Dept(A)下的所有Emp(zhangsan,ww)的AttTime记录，并按每个人分组显示，求出每个人每天的最大时间和大小时间， 
没有记录的日期用空的补全。 AttDate      minAttTime    maxAttTime Emp Dept 
2008-08-02    6:45         18:20      zhangsan  A 
2008-08-03    null          null      zhangsan  A 
2008-08-04    null          null      zhangsan  A 
2008-08-05    null          null      zhangsan  A 
2008-08-06    null          null      zhangsan  A 
2008-08-07    null          null      zhangsan  A 
2008-08-08    7:15          19:12     zhangsan  A 2008-08-01    6:41          17:20      ww       A 
2008-08-02    null          null       ww       A 
2008-08-03    null          null       ww       A 
2008-08-04    null          null       ww       A 
2008-08-05    null          null        ww      A 
2008-08-06    null          null       ww       A修改下排版

Answer 3

想要得到数据如下： 
即得到指定Dept(A)下的所有Emp(zhangsan,ww)的AttTime记录，并按每个人分组显示，求出每个人每天的最大时间和最小时间， 
没有记录的日期用空的补全。 AttDate      minAttTime    maxAttTime Emp Dept 
2008-08-02    6:45        18:20      zhangsan  A 
2008-08-03    null          null      zhangsan  A 
2008-08-04    null          null      zhangsan  A 
2008-08-05    null          null      zhangsan  A 
2008-08-06    null          null      zhangsan  A 
2008-08-07    null          null      zhangsan  A 
2008-08-08    7:15          19:12    zhangsan  A 2008-08-01    6:41          17:20      ww      A 
2008-08-02    null          null      ww      A 
2008-08-03    null          null      ww      A 
2008-08-04    null          null      ww      A 
2008-08-05    null          null        ww      A 
2008-08-06    7:11          18:12      ww      A 修改下排版 

Answer 4

2008-08-04 8:13        lisi                    B 
这条记录呢 

Answer 5

看样子用到
MSTER..SPT_VALUES
LEFT JOIN

Answer 6

我要得到指定的Dept（A），这条记录被过滤掉了啊

Answer 7

 
--> 生成测试数据: @AttRecords
DECLARE @AttRecords TABLE (AttTime DATETIME,Emp VARCHAR(8),Dept VARCHAR(1))
INSERT INTO @AttRecords
SELECT '2008-08-02 6:45','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-02 11:22','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-08 7:15','zhangsan','A' UNION ALL
SELECT '2008-08-08 19:12','zhangsan','A' UNION ALL
SELECT '2008-08-01 6:41','ww','A' UNION ALL
SELECT '2008-08-01 8:41','ww','A' UNION ALL
SELECT '2008-08-01 17:20','ww','A' UNION ALL
SELECT '2008-08-06 7:11','ww','A' UNION ALL
SELECT '2008-08-06 18:12','ww','A' UNION ALL
SELECT '2008-08-04 8:13','lisi','B'--SQL查询如下:SELECT Dept,Emp,MIN(AttTime) AS MinAttTime,MAX(AttTime) AS MaxAttTime INTO #tmp
FROM @AttRecords
WHERE Dept = 'A' GROUP BY Dept, Emp,CONVERT(VARCHAR(8),AttTime,112);DECLARE @minTime DATETIME,@maxTime DATETIME;
SELECT @minTime = MIN(AttTime),@maxTime = MAX(AttTime) FROM @AttRecords
WHERE Dept = 'A';DECLARE @diffDay INT;
SET @diffDay = DATEDIFF(day,@minTime,@maxTime) + 1;SET ROWCOUNT @diffDay;SELECT ID=IDENTITY(INT,0,1) INTO #number FROM syscolumns;SET ROWCOUNT 0;SELECT CONVERT(VARCHAR(10),DATEADD(day,A.ID,@minTime),120) AS AttTime,
    CONVERT(VARCHAR(10),MinAttTime,108) AS MinAttTime,
    CONVERT(VARCHAR(10),MaxAttTime,108) AS MaxAttTime,
    A.Emp,A.Dept
FROM (SELECT * FROM #number
    CROSS JOIN (SELECT DISTINCT Emp,Dept FROM #tmp) AS T) AS A
  LEFT JOIN #tmp AS B
ON DATEDIFF(day,DATEADD(day,A.ID,@minTime),B.MinAttTime) = 0
    AND A.Emp = B.Emp
ORDER BY A.Emp,A.IDDROP TABLE #number,#tmp;/*
AttTime    MinAttTime MaxAttTime Emp      Dept
---------- ---------- ---------- -------- ----
2008-08-01 06:41:00   17:20:00   ww       A
2008-08-02 NULL       NULL       ww       A
2008-08-03 NULL       NULL       ww       A
2008-08-04 NULL       NULL       ww       A
2008-08-05 NULL       NULL       ww       A
2008-08-06 07:11:00   18:12:00   ww       A
2008-08-07 NULL       NULL       ww       A
2008-08-08 NULL       NULL       ww       A
2008-08-01 NULL       NULL       zhangsan A
2008-08-02 06:45:00   18:20:00   zhangsan A
2008-08-03 NULL       NULL       zhangsan A
2008-08-04 NULL       NULL       zhangsan A
2008-08-05 NULL       NULL       zhangsan A
2008-08-06 NULL       NULL       zhangsan A
2008-08-07 NULL       NULL       zhangsan A
2008-08-08 07:15:00   19:12:00   zhangsan A(16 行受影响)
*/

Answer 8

关于MSTER..SPT_VALUES这个系统表里的东西，我一直不太明白，楼下那位可以解释下，这些常用的地方啊。

Answer 9

left join 是必须的
最小的怎么求啊？

Answer 10

DECLARE @TB TABLE([AttTime] DATETIME, [Emp] VARCHAR(8), [Dept] VARCHAR(1))
INSERT @TB 
SELECT '2008-08-02 6:45', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-02 18:20', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-02 11:22', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-02 18:20', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-08 7:15', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-08 19:12', 'zhangsan', 'A' UNION ALL 
SELECT '2008-08-01 6:41', 'ww', 'A' UNION ALL 
SELECT '2008-08-01 8:41', 'ww', 'A' UNION ALL 
SELECT '2008-08-01 17:20', 'ww', 'A' UNION ALL 
SELECT '2008-08-06 7:11', 'ww', 'A' UNION ALL 
SELECT '2008-08-06 18:12', 'ww', 'A' UNION ALL 
SELECT '2008-08-04 8:13', 'lisi', 'B'SELECT C.Dept,C.Emp,CONVERT(VARCHAR(10),C.AttTime,120),
MIN(CONVERT(VARCHAR(10),D.AttTime,108)) AS MINTIME,MAX(CONVERT(VARCHAR(10),D.AttTime,108)) AS MAXTIME
FROM (
SELECT *,DATEADD(DAY,NUMBER,MINTIME) AS AttTime
FROM (
SELECT Dept,Emp,MIN(AttTime) AS MINTIME,MAX(AttTime) AS MAXTIME
FROM @TB 
GROUP BY Dept,Emp) AS A,
(SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P') AS B
WHERE DATEDIFF(DAY,MINTIME,MAXTIME)>=NUMBER) C
LEFT JOIN @TB AS D
ON C.Dept=D.Dept AND C.Emp=D.Emp  AND DATEDIFF(DAY,C.AttTime,D.AttTime)=0
GROUP BY  C.Dept,C.Emp,CONVERT(VARCHAR(10),C.AttTime,120)/*
Dept Emp                 MINTIME    MAXTIME    
---- -------- ---------- ---------- ---------- 
A    ww       2008-08-01 06:41:00   17:20:00
A    ww       2008-08-02 NULL       NULL
A    ww       2008-08-03 NULL       NULL
A    ww       2008-08-04 NULL       NULL
A    ww       2008-08-05 NULL       NULL
A    ww       2008-08-06 07:11:00   18:12:00
A    zhangsan 2008-08-02 06:45:00   18:20:00
A    zhangsan 2008-08-03 NULL       NULL
A    zhangsan 2008-08-04 NULL       NULL
A    zhangsan 2008-08-05 NULL       NULL
A    zhangsan 2008-08-06 NULL       NULL
A    zhangsan 2008-08-07 NULL       NULL
A    zhangsan 2008-08-08 07:15:00   19:12:00
B    lisi     2008-08-04 08:13:00   08:13:00(14 row(s) affected)
*/

Answer 11

问下，这个要做成视图，就把你的直接放到create view的select里就可以了啊

Answer 12

佩服梁哥 
把梁哥的  
    CONVERT(VARCHAR(10),MinAttTime,108) AS MinAttTime,
    CONVERT(VARCHAR(10),MaxAttTime,108) AS MaxAttTime,  
改为
CONVERT(VARCHAR(5),MinAttTime,108) AS MinAttTime,
CONVERT(VARCHAR(5),MaxAttTime,108) AS MaxAttTime,就满足要求了

Answer 13

--> 生成测试数据: @AttRecords
DECLARE @AttRecords TABLE (AttTime DATETIME,Emp VARCHAR(8),Dept VARCHAR(1))
INSERT INTO @AttRecords
SELECT '2008-08-02 6:45','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-02 11:22','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-08 7:15','zhangsan','A' UNION ALL
SELECT '2008-08-08 19:12','zhangsan','A' UNION ALL
SELECT '2008-08-01 6:41','ww','A' UNION ALL
SELECT '2008-08-01 8:41','ww','A' UNION ALL
SELECT '2008-08-01 17:20','ww','A' UNION ALL
SELECT '2008-08-06 7:11','ww','A' UNION ALL
SELECT '2008-08-06 18:12','ww','A' UNION ALL
SELECT '2008-08-04 8:13','lisi','B'--SQL查询如下:SELECT Dept,Emp,MIN(AttTime) AS MinAttTime,MAX(AttTime) AS MaxAttTime INTO #tmp
FROM @AttRecords
WHERE Dept = 'A' GROUP BY Dept, Emp,CONVERT(VARCHAR(8),AttTime,112);DECLARE @minTime DATETIME,@maxTime DATETIME;
SELECT @minTime = MIN(AttTime),@maxTime = MAX(AttTime) FROM @AttRecords
WHERE Dept = 'A';DECLARE @diffDay INT;
SET @diffDay = DATEDIFF(day,@minTime,@maxTime) + 1;SET ROWCOUNT @diffDay;SELECT ID=IDENTITY(INT,0,1) INTO #number FROM syscolumns;SET ROWCOUNT 0;SELECT CONVERT(VARCHAR(10),DATEADD(day,A.ID,@minTime),120) AS AttTime,
    CONVERT(VARCHAR(5),MinAttTime,108) AS MinAttTime,
    CONVERT(VARCHAR(5),MaxAttTime,108) AS MaxAttTime,
    A.Emp,A.Dept
FROM (SELECT * FROM #number
    CROSS JOIN (SELECT DISTINCT Emp,Dept FROM #tmp) AS T) AS A
  LEFT JOIN #tmp AS B
ON DATEDIFF(day,DATEADD(day,A.ID,@minTime),B.MinAttTime) = 0
    AND A.Emp = B.Emp
ORDER BY A.Emp,A.IDDROP TABLE #number,#tmp;
/*AttTime    MinAttTime MaxAttTime Emp      Dept
---------- ---------- ---------- -------- ----
2008-08-01 06:41      17:20      ww       A
2008-08-02 NULL       NULL       ww       A
2008-08-03 NULL       NULL       ww       A
2008-08-04 NULL       NULL       ww       A
2008-08-05 NULL       NULL       ww       A
2008-08-06 07:11      18:12      ww       A
2008-08-07 NULL       NULL       ww       A
2008-08-08 NULL       NULL       ww       A
2008-08-01 NULL       NULL       zhangsan A
2008-08-02 06:45      18:20      zhangsan A
2008-08-03 NULL       NULL       zhangsan A
2008-08-04 NULL       NULL       zhangsan A
2008-08-05 NULL       NULL       zhangsan A
2008-08-06 NULL       NULL       zhangsan A
2008-08-07 NULL       NULL       zhangsan A
2008-08-08 07:15      19:12      zhangsan A(16 行受影响)*/

Answer 14

--上面的少了个条件
SELECT C.Dept,C.Emp,CONVERT(VARCHAR(10),C.AttTime,120),
MIN(CONVERT(VARCHAR(10),D.AttTime,108)) AS MINTIME,MAX(CONVERT(VARCHAR(10),D.AttTime,108)) AS MAXTIME
FROM (
SELECT *,DATEADD(DAY,NUMBER,MINTIME) AS AttTime
FROM (
SELECT Dept,Emp,MIN(AttTime) AS MINTIME,MAX(AttTime) AS MAXTIME
FROM @TB 
WHERE Dept='A'
GROUP BY Dept,Emp) AS A,
(SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P') AS B
WHERE DATEDIFF(DAY,MINTIME,MAXTIME)>=NUMBER) C
LEFT JOIN @TB AS D
ON C.Dept=D.Dept AND C.Emp=D.Emp  AND DATEDIFF(DAY,C.AttTime,D.AttTime)=0
GROUP BY  C.Dept,C.Emp,CONVERT(VARCHAR(10),C.AttTime,120)
ORDER BY  C.Dept,C.Emp,CONVERT(VARCHAR(10),C.AttTime,120)

Answer 15

看来核心的就是MASTER..SPT_VALUES  和 LEFT JOIN ，其他的都是些辅助的逻辑运算。

Answer 16

后面不知道怎么把空缺的日期补上了，郁闷if object_id('AttRecords')is not null drop table AttRecords
go 
create table AttRecords (AttTime datetime,Emp nvarchar(20),Dept nvarchar(20) )
insert AttRecords select
'2008-08-02 6:45' ,  'zhangsan', 'A' union all select
'2008-08-02 18:20', 'zhangsan',   'A' union all select 
'2008-08-02 11:22', 'zhangsan',   'A' union all select
'2008-08-02 18:20' ,'zhangsan',   'A' union all select 
'2008-08-08 7:15'   ,'zhangsan',   'A' union all select 
'2008-08-08 19:12' ,'zhangsan',     'A' union all select 
'2008-08-01 6:41'   , 'ww', 'A' union all select 
'2008-08-01 8:41'   ,'ww', 'A' union all select 
'2008-08-01 17:20' ,'ww',         'A' union all select 
'2008-08-06 7:11' ,'ww',         'A' union all select 
'2008-08-06 18:12', 'ww',         'A' union all select 
'2008-08-04 8:13' ,'lisi',         'B'
select convert(varchar(12),t.AttTime,112) as AttDate,
minAttTime=
(select min(convert(varchar(12),AttTime,108)) 
from AttRecords 
where Dept='A' and Emp=t.Emp and convert(varchar(12),AttTime,112)=convert(varchar(12),t.AttTime,112)
),
maxAttTime=
(select max(convert(varchar(12),AttTime,108)) 
from AttRecords 
where Dept='A' and Emp=t.Emp and convert(varchar(12),AttTime,112)=convert(varchar(12),t.AttTime,112)
),
Emp,Dept
from   AttRecords t
where Dept='A'
group by convert(varchar(12),t.AttTime,112),Emp,Dept
order by Emp,AttDateAttDate      minAttTime   maxAttTime   Emp                  Dept
------------ ------------ ------------ -------------------- --------------------
20080801     06:41:00     17:20:00     ww                   A
20080806     07:11:00     18:12:00     ww                   A
20080802     06:45:00     18:20:00     zhangsan             A
20080808     07:15:00     19:12:00     zhangsan             A(4 行受影响)

Answer 17

create table AttRecords (AttTime  datetime,    Emp nvarchar(50) ,     Dept nvarchar(50) )
insert into AttRecordsselect '2008-08-02 6:45 ','zhangsan','A'  union all
select '2008-08-02 18:20','zhangsan','A'  union all 
select '2008-08-02 11:22','zhangsan','A'  union all 
select '2008-08-02 18:20','zhangsan','A'  union all 
select '2008-08-08 7:15 ','zhangsan','A'  union all 
select '2008-08-08 19:12','zhangsan','A'  union all 
select '2008-08-01 6:41 ', 'ww'   ,'A'   union all
select '2008-08-01 8:41 ', 'ww'   ,'A'   union all
select '2008-08-01 17:20', 'ww'   ,'A'   union all
select '2008-08-06 7:11 ', 'ww'   ,'A'   union all
select '2008-08-06 18:12', 'ww'   ,'A'   union all
select '2008-08-04 8:13 ', 'lisi'   ,'B' select distinct  convert(char(10), AttTime,20),
minTime = (select convert(char(8),min(AttTime),108) from AttRecords  where Dept=A.Dept and Emp= A.Emp and 
convert(char(10), AttTime,20) = convert(char(10), A.AttTime,20)),
maxTime = (select convert(char(8),max(AttTime),108) from AttRecords  where Dept=A.Dept and Emp= A.Emp and 
convert(char(10), AttTime,20) = convert(char(10), A.AttTime,20)),
Emp,Dept
from AttRecords  A where  Dept = 'A' and  Emp in ('zhangsan','ww')
order by emp desc,convert(char(10), AttTime,20) asc

Answer 18

AttDate      minAttTime   maxAttTime   Emp                  Dept
------------ ------------ ------------ -------------------- --------------------
20080801     06:41:00     17:20:00     ww                   A
20080806     07:11:00     18:12:00     ww                   A
20080802     06:45:00     18:20:00     zhangsan             A
20080808     07:15:00     19:12:00     zhangsan             A(4 行受影响)

Answer 19

 
--> 生成测试数据: @AttRecords
DECLARE @AttRecords TABLE (AttTime DATETIME,Emp VARCHAR(8),Dept VARCHAR(1))
INSERT INTO @AttRecords
SELECT '2008-08-02 6:45','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-02 11:22','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-08 7:15','zhangsan','A' UNION ALL
SELECT '2008-08-08 19:12','zhangsan','A' UNION ALL
SELECT '2008-08-01 6:41','ww','A' UNION ALL
SELECT '2008-08-01 8:41','ww','A' UNION ALL
SELECT '2008-08-01 17:20','ww','A' UNION ALL
SELECT '2008-08-06 7:11','ww','A' UNION ALL
SELECT '2008-08-06 18:12','ww','A' UNION ALL
SELECT '2008-08-04 8:13','lisi','B'--SQL查询如下:
SELECT TOP 50 ID=IDENTITY(INT,0,1) INTO #number FROM syscolumns;SELECT 
    A.Emp,A.Dept,
    CONVERT(VARCHAR(10),A.AttTime,120) AS AttTime,
    CONVERT(VARCHAR(10),MIN(B.AttTime),108) AS MinAttTime,
    CONVERT(VARCHAR(10),MAX(B.AttTime),108) AS MaxAttTime
FROM (SELECT A.ID,B.Dept,B.Emp,DATEADD(day,A.ID,B.MinAttTime) AS AttTime
      FROM #number AS A
        CROSS JOIN (SELECT Dept,Emp,MIN(AttTime) AS MinAttTime,MAX(AttTime) AS MaxATtTime
                FROM @AttRecords WHERE Dept = 'A' GROUP BY Dept,Emp ) AS B
    WHERE DATEADD(day,A.ID,B.MinAttTime) <= B.MaxAttTime) AS A
LEFT JOIN @AttRecords AS B
ON DATEDIFF(day,B.AttTime,A.AttTime) = 0 AND A.Emp = B.Emp AND A.Dept = B.Dept
GROUP BY A.Dept,A.Emp,CONVERT(VARCHAR(10),A.AttTime,120)
ORDER BY A.Dept,A.Emp;
DROP TABLE #number;

Answer 20

我要这个select的结果做成视图，要怎么弄？

Answer 21

create view viewname as
  --sql statement....

Answer 22

这样会抱错的
Msg 1033, Level 15, State 1, Procedure view4, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Answer 23

--> 生成测试数据: @AttRecords
CREATE TABLE AttRecords(AttTime DATETIME,Emp VARCHAR(8),Dept VARCHAR(1))
INSERT INTO AttRecords
SELECT '2008-08-02 6:45','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-02 11:22','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-08 7:15','zhangsan','A' UNION ALL
SELECT '2008-08-08 19:12','zhangsan','A' UNION ALL
SELECT '2008-08-01 6:41','ww','A' UNION ALL
SELECT '2008-08-01 8:41','ww','A' UNION ALL
SELECT '2008-08-01 17:20','ww','A' UNION ALL
SELECT '2008-08-06 7:11','ww','A' UNION ALL
SELECT '2008-08-06 18:12','ww','A' UNION ALL
SELECT '2008-08-04 8:13','lisi','B'--SQL查询如下:--在实际中.应该在数据库中建立一个序号表来代替master.dbo.spt_values
GO
CREATE VIEW dbo.v_Test 
AS
SELECT 
    A.Emp,A.Dept,
    CONVERT(VARCHAR(10),A.AttTime,120) AS AttTime,
    CONVERT(VARCHAR(10),MIN(B.AttTime),108) AS MinAttTime,
    CONVERT(VARCHAR(10),MAX(B.AttTime),108) AS MaxAttTime
FROM (SELECT A.ID,B.Dept,B.Emp,DATEADD(day,A.ID,B.MinAttTime) AS AttTime
      FROM (SELECT number AS ID FROM master.dbo.spt_values WHERE type = 'p') AS A
        CROSS JOIN (SELECT Dept,Emp,MIN(AttTime) AS MinAttTime,MAX(AttTime) AS MaxATtTime
                FROM AttRecords WHERE Dept = 'A' GROUP BY Dept,Emp ) AS B
    WHERE DATEADD(day,A.ID,B.MinAttTime) <= B.MaxAttTime) AS A
LEFT JOIN AttRecords AS B
ON DATEDIFF(day,B.AttTime,A.AttTime) = 0 AND A.Emp = B.Emp AND A.Dept = B.Dept
GROUP BY A.Dept,A.Emp,CONVERT(VARCHAR(10),A.AttTime,120);
GOSELECT * FROM dbo.v_Test ORDER BY Dept,Emp,AttTime;GO
DROP VIEW dbo.v_Test;
DROP TABLE AttRecords;

Answer 24

--在实际中.应该在数据库中建立一个序号表来代替master.dbo.spt_values
这句话什么意思啊？