在 SQL Server 中循环访问结果

Question

示例表

id  emplrcd effdt   country visited
------------------------------------
112233  0   10/9/2018   US
112233  1   10/10/2018  IND
112233  1   10/11/2018  BAN
112233  1   10/12/2018  PAK
112233  0   10/13/2018  US
112233  2   10/14/2018  IND
112233  2   10/16/2018  THA
112233  2   10/17/2018  SIN
112233  0   10/18/2018  US
223344  0   10/9/2018   US
223344  1   10/10/2018  IND
223344  1   10/11/2018  BAN
223344  1   10/12/2018  PAK
223344  0   10/13/2018  US
223344  2   10/14/2018  IND

我想从查询中循环访问某些结果集，例如 id 112233 来自美国，在两个美国国家/地区代码之间，国家/地区代码表示他访问的国家/地区。

访问国家/地区count(*)= 美国 --> 3

现在我想迭代我的结果 3 次，得到他出去的所有中间日期。这可以在单个 SQL 查询中完成，还是我需要像游标这样的东西来执行此操作？

上述游标或查询的结果应返回以下行

112233  1   10/10/2018  IND
112233  1   10/11/2018  BAN
112233  1   10/12/2018  PAK
112233  2   10/14/2018  IND
112233  2   10/16/2018  THA
112233  2   10/17/2018  SIN
223344  1   10/10/2018  IND
223344  1   10/11/2018  BAN
223344  1   10/12/2018  PAK

Answer 1

我会使用窗口函数：

select t.*
from (select t.*,
min(case when t.country = 'US' then t.effdt end) over (partition by t.id) as us_min_effdt,
max(case when t.country = 'US' then t.effdt end) over (partition by t.id) as us_max_effdt
from t
) t
where country <> 'US' and
effdt >= us_min_effdt and efft < us_max_effdt;

Answer 2

下面是使用 CTE 设置边界条件的解决方案。

-------------------------
-- TEST SCHEMA
create table #datatable (id varchar(6), emplrcd int, effdt date, [country visited] varchar (3))
insert into #datatable values
('112233',0,'10/9/2018 ','US '),
('112233',1,'10/10/2018','IND'),
('112233',1,'10/11/2018','BAN'),
('112233',1,'10/12/2018','PAK'),
('112233',0,'10/13/2018','US '),
('112233',2,'10/14/2018','IND'),
('112233',2,'10/16/2018','THA'),
('112233',2,'10/17/2018','SIN'),
('112233',0,'10/18/2018','US '),
('223344',0,'10/9/2018 ','US '),
('223344',1,'10/10/2018','IND'),
('223344',1,'10/11/2018','BAN'),
('223344',1,'10/12/2018','PAK'),
('223344',0,'10/13/2018','US '),
('223344',2,'10/14/2018','IND')
-------------------------
-- QUERY
; WITH boundryDate AS 
(
SELECT 
id
,min (effdt) mindate
, max(effdt) maxdate
FROM
#datatable
WHERE
[country visited] = 'US'
GROUP BY
id
)
SELECT
*
FROM
#datatable dt
JOIN boundryDate bd
on dt.id = bd.id
and dt.effdt between bd.mindate and bd.maxdate
WHERE
[country visited] <> 'US'

Answer 3

使用 EXISTS(( 函数返回所有不是 "US" 的行，其中 EXISTS(( 前面是"US" 行，后面是 EXISTS(( 行。