代码之家 › 专栏 › 技术社区 › John Clarence Castro

SQL,在返回重复值的循环中选择

sql-server-2016 stored-procedures sql-server sql

John Clarence Castro · 技术社区 · 7 年前

我在一个项目上工作了很长时间,但我似乎不知道该怎么做。我有一个循环代码和一个函数,但似乎无法得到我想要看到的输出。

我的代码如下:

WITH CTE AS
(
    SELECT 1 as Day
    UNION ALL
    SELECT Day+1 FROM CTE
    WHERE Day < 15
), Name as (Select * from fn_logs(@Month, @Year,@date_from,@date_to)

)

SELECT CTE.Day, 
CASE WHEN Name.DAtee != CTE.Day THEN Name.Fullname ELSE Name.Fullname END as Fullname,  
CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.AMIN END as AMIN, 
CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.AMOUT END as AMOUT, 
CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.PMIN END as PMIN, 
CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.PMOUT END as PMOUT
FROM CTE, Name

group by CTE.Day,Name.Fullname,Name.AMIN,Name.AMOUT,Name.PMIN,Name.PMOUT

其中 名称日期 获取日期的日期我想要的结果是:

Day  Fullname  AM-IN  AM-OUT  PM-IN  PM-OUT
1    Ara Ast   8:00   12:00   12:03  5:00
2    Ara Ast   7:51   12:22   12:23  5:10
3    Ara Ast      
1    Clara Est 8:01   12:12   12:25  5:07
2    Clara Est 
3    Clara Est 7:41   12:02   12:15  5:00

我想显示单个员工记录的所有日期,当没有记录日期时,它将自动显示没有值。我得到的结果如下:

Day  Fullname  AM-IN  AM-OUT  PM-IN  PM-OUT
1    Ara Ast   8:00   12:00   12:03  5:00
2    Ara Ast   
3    Ara Ast 
1    Ara Ast   
2    Ara Ast   7:51   12:22   12:23  5:10
3    Ara Ast 
1    Ara Ast   
2    Ara Ast   
3    Ara Ast

示例数据

INSERT INTO table1 (Fullname, Date, AMIN, AMOUT, PMIN, PMOUT) VALUES 
('Ara Ast', '2/1/2018','8:00 AM','12:00 PM','1:00 PM','5:00 PM'), 
('Ema Watson', '2/1/2018','8:00 AM','12:00 PM','1:00 PM','5:00 PM'), 
('Ema Watson', '2/2/2018','8:00 AM','12:00 PM','1:00 PM','5:00 PM'), 
('Ara Ast', '2/3/2018','8:00 AM','12:00 PM','1:00 PM','5:00 PM');

这些值似乎重复出现,每当值不等于日期时,它都会打印“无”值。抱歉,我还处于sql server编码的学习阶段。

提前谢谢。

1 回复 | 直到 7 年前

uzi 7 年前

检查此查询。根据你的评论,这就是你需要的

declare @t table (Fullname varchar(100), Date date, AMIN varchar(100), AMOUT varchar(100), PMIN varchar(100), PMOUT varchar(100))

INSERT INTO @t (Fullname, Date, AMIN, AMOUT, PMIN, PMOUT) 
VALUES ('Ara Ast', '20180201','8:00 AM','12:00 PM','1:00 PM','5:00 PM')
    , ('Ema Watson', '20180201','8:00 AM','12:00 PM','1:00 PM','5:00 PM')
    , ('Ema Watson', '20180202','8:00 AM','12:00 PM','1:00 PM','5:00 PM')
    , ('Ara Ast', '20180203','8:00 AM','12:00 PM','1:00 PM','5:00 PM');

;WITH CTE AS
(
    SELECT 1 as Day
    UNION ALL
    SELECT Day+1 FROM CTE
    WHERE Day < 15
)
select 
    * 
from 
    cte c
    cross join (select distinct Fullname from @t) t
    left join @t tt on t.Fullname = tt.Fullname and c.Day = day(tt.Date)
order by 2,1