22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[id] int,
[user_code] int,
[card_id] int,
[user_name] varchar(6),
[record_date] date,
[event_time] time(2),
[WeekName] varchar(5),
[Reason] sql_variant
)
go
insert [test]
select 50102,56,13587426,'王玲玲','2012-5-29','7:20:57','二',null union all
select 50101,56,13587426,'王玲玲','2012-5-29','7:20:52','二',null union all
select 50168,56,13587426,'王玲玲','2012-5-29','17:11:54','二',null union all
select 50169,56,13587426,'王玲玲','2012-5-29','17:11:58','二',null union all
select 50207,56,13587426,'王玲玲','2012-5-30','7:23:31','三',null union all
select 50263,56,13587426,'王玲玲','2012-5-30','17:02:43','三',null union all
select 50310,56,13587426,'王玲玲','2012-5-31','7:19:25','四',null union all
select 50389,56,13587426,'王玲玲','2012-5-31','17:23:24','四',null union all
select 19394,6014,15656386,'王學燕','2011-9-6','17:55:50','二',null union all
select 19421,6014,15656386,'王學燕','2011-9-7','7:51:42','三',null
go
with t
as(
select *,
px=COUNT(1)over(partition by [user_code],[record_date])
from test a
where not exists(select 1 from test b
where a.card_id=b.card_id and a.record_date=b.record_date
and a.event_time>b.event_time)
or not exists(select 1 from test c
where a.card_id=c.card_id and a.record_date=c.record_date
and a.event_time<c.event_time)
)
select id,user_code,card_id,user_name,record_date,event_time,WeekName,[Reason]
from t
union all
select id,user_code,card_id,user_name,record_date,
case when datepart(hh,event_time)=17 then '08:00:00' else '17:30:00' end as event_time,
WeekName,'系统生成'
from t where px=1
order by record_date,event_time
/*
id user_code card_id user_name record_date event_time WeekName Reason
19394 6014 15656386 王學燕 2011-09-06 08:00:00.00 二 系统生成
19394 6014 15656386 王學燕 2011-09-06 17:55:50.00 二 NULL
19421 6014 15656386 王學燕 2011-09-07 07:51:42.00 三 NULL
19421 6014 15656386 王學燕 2011-09-07 17:30:00.00 三 系统生成
50101 56 13587426 王玲玲 2012-05-29 07:20:52.00 二 NULL
50169 56 13587426 王玲玲 2012-05-29 17:11:58.00 二 NULL
50207 56 13587426 王玲玲 2012-05-30 07:23:31.00 三 NULL
50263 56 13587426 王玲玲 2012-05-30 17:02:43.00 三 NULL
50310 56 13587426 王玲玲 2012-05-31 07:19:25.00 四 NULL
50389 56 13587426 王玲玲 2012-05-31 17:23:24.00 四 NULL*/
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
id INT,
user_code INT,
card_id VARCHAR(100),
user_name VARCHAR(100),
record_date VARCHAR(10),
event_time VARCHAR(10),
WeekName VARCHAR(10)
)
GO
INSERT INTO tba
SELECT 50102, 56, '13587426', '王玲玲', '2012-5-29', '7:20:57', '二' UNION
SELECT 50101, 56, '13587426', '王玲玲', '2012-5-29', '7:20:52', '二' UNION
SELECT 50168, 56, '13587426', '王玲玲', '2012-5-29', '17:11:54', '二' UNION
SELECT 50169, 56, '13587426', '王玲玲', '2012-5-29', '17:11:58', '二' UNION
SELECT 50207, 56, '13587426', '王玲玲', '2012-5-30', '7:23:31', '三' UNION
SELECT 50263, 56, '13587426', '王玲玲', '2012-5-30', '17:02:43', '三' UNION
SELECT 50310, 56, '13587426', '王玲玲', '2012-5-31', '7:19:25', '四' UNION
SELECT 50389, 56, '13587426', '王玲玲', '2012-5-31', '17:23:24', '四' UNION
SELECT 19394, 6014, '15656386', '王學燕', '2011-9-6', '17:55:50', '二' UNION
SELECT 19421, 6014, '15656386', '王學燕', '2011-9-7', '7:51:42', '三'
GO
--问题二
WITH t AS
(SELECT user_code, card_id, user_name, record_date, MIN(event_time) AS event_time, WeekName
FROM tba
WHERE DATEPART(HOUR,event_time) <= 12
GROUP BY user_code, card_id, user_name, record_date,WeekName
UNION
SELECT user_code, card_id, user_name, record_date, MAX(event_time) AS event_time, WeekName
FROM tba
WHERE DATEPART(HOUR,event_time) > 12
GROUP BY user_code, card_id, user_name, record_date,WeekName
),
y AS
(
SELECT user_code,WeekName,COUNT(1) AS Num
FROM t
GROUP BY user_code,WeekName
)
SELECT t.user_code, card_id, user_name, record_date,CASE WHEN DATEPART(HOUR,event_time) <= 12 THEN '17:30:00'
ELSE '8:00:00' END AS event_time,t.WeekName ,'系统生成' AS Reason
FROM t INNER JOIN y ON t.user_code = y.user_code AND t.WeekName = y.WeekName AND y.Num = 1
UNION
SELECT t.user_code, card_id, user_name, record_date,event_time,t.WeekName ,'' AS Reason
FROM t
user_code card_id user_name record_date event_time WeekName Reason
56 13587426 王玲玲 2012-5-29 17:11:58 二
56 13587426 王玲玲 2012-5-29 7:20:52 二
56 13587426 王玲玲 2012-5-30 17:02:43 三
56 13587426 王玲玲 2012-5-30 7:23:31 三
56 13587426 王玲玲 2012-5-31 17:23:24 四
56 13587426 王玲玲 2012-5-31 7:19:25 四
6014 15656386 王學燕 2011-9-6 17:55:50 二
6014 15656386 王學燕 2011-9-6 8:00:00 二 系统生成
6014 15656386 王學燕 2011-9-7 17:30:00 三 系统生成
6014 15656386 王學燕 2011-9-7 7:51:42 三
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
id INT,
user_code INT,
card_id VARCHAR(100),
user_name VARCHAR(100),
record_date VARCHAR(10),
event_time VARCHAR(10),
WeekName VARCHAR(10)
)
GO
INSERT INTO tba
SELECT 50102, 56, '13587426', '王玲玲', '2012-5-29', '7:20:57', '二' UNION
SELECT 50101, 56, '13587426', '王玲玲', '2012-5-29', '7:20:52', '二' UNION
SELECT 50168, 56, '13587426', '王玲玲', '2012-5-29', '17:11:54', '二' UNION
SELECT 50169, 56, '13587426', '王玲玲', '2012-5-29', '17:11:58', '二' UNION
SELECT 50207, 56, '13587426', '王玲玲', '2012-5-30', '7:23:31', '三' UNION
SELECT 50263, 56, '13587426', '王玲玲', '2012-5-30', '17:02:43', '三' UNION
SELECT 50310, 56, '13587426', '王玲玲', '2012-5-31', '7:19:25', '四' UNION
SELECT 50389, 56, '13587426', '王玲玲', '2012-5-31', '17:23:24', '四' UNION
SELECT 19394, 6014, '15656386', '王學燕', '2011-9-6', '17:55:50', '二' UNION
SELECT 19421, 6014, '15656386', '王學燕', '2011-9-7', '7:51:42', '三'
GO
--问题一
SELECT user_code, card_id, user_name, record_date, MIN(event_time) AS event_time, WeekName
FROM tba
WHERE DATEPART(HOUR,event_time) <= 12
GROUP BY user_code, card_id, user_name, record_date,WeekName
UNION
SELECT user_code, card_id, user_name, record_date, MAX(event_time) AS event_time, WeekName
FROM tba
WHERE DATEPART(HOUR,event_time) > 12
GROUP BY user_code, card_id, user_name, record_date,WeekName
ORDER BY user_code, card_id, user_name, record_date,event_time DESC,WeekName
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[id] int,
[user_code] int,
[card_id] int,
[user_name] varchar(6),
[record_date] date,
[event_time] time(2),
[WeekName] varchar(5),
[Reason] sql_variant
)
go
insert [test]
select 50102,56,13587426,'王玲玲','2012-5-29','7:20:57','二',null union all
select 50101,56,13587426,'王玲玲','2012-5-29','7:20:52','二',null union all
select 50168,56,13587426,'王玲玲','2012-5-29','17:11:54','二',null union all
select 50169,56,13587426,'王玲玲','2012-5-29','17:11:58','二',null union all
select 50207,56,13587426,'王玲玲','2012-5-30','7:23:31','三',null union all
select 50263,56,13587426,'王玲玲','2012-5-30','17:02:43','三',null union all
select 50310,56,13587426,'王玲玲','2012-5-31','7:19:25','四',null union all
select 50389,56,13587426,'王玲玲','2012-5-31','17:23:24','四',null union all
select 19394,6014,15656386,'王學燕','2011-9-6','17:55:50','二',null union all
select 19421,6014,15656386,'王學燕','2011-9-7','7:51:42','三',null
go
with t
as(
select px=ROW_NUMBER()over(partition by [record_date],[user_name],[user_name],[WeekName]
order by [record_date],[event_time] asc),
* from test
),
m as(
select px=ROW_NUMBER()over(partition by [record_date],[user_name],[user_name],[WeekName]
order by [record_date],[event_time] desc),
* from test
)
select id,user_code,card_id,user_name,record_date,event_time,WeekName,Reason
from t where px=1
union
select id,user_code,card_id,user_name,record_date,event_time,WeekName,Reason
from m where px=1
/*
id user_code card_id user_name record_date event_time WeekName Reason
19394 6014 15656386 王學燕 2011-09-06 17:55:50.00 二 NULL
19421 6014 15656386 王學燕 2011-09-07 07:51:42.00 三 NULL
50101 56 13587426 王玲玲 2012-05-29 07:20:52.00 二 NULL
50169 56 13587426 王玲玲 2012-05-29 17:11:58.00 二 NULL
50207 56 13587426 王玲玲 2012-05-30 07:23:31.00 三 NULL
50263 56 13587426 王玲玲 2012-05-30 17:02:43.00 三 NULL
50310 56 13587426 王玲玲 2012-05-31 07:19:25.00 四 NULL
50389 56 13587426 王玲玲 2012-05-31 17:23:24.00 四 NULL
*/
--问题一的答案
问题二的你是需要查询生成还是自动往表里添加?