在SQL Viia递归查询中组合行
在SQL Viia递归查询中组合行
提问于 2016-09-19 22:14:51
我有下面的表格。
Animal Vaccine_Date Vaccine
Cat 2/1/2016 y
Cat 2/1/2016 z
Dog 2/1/2016 z
Dog 1/1/2016 x
Dog 2/1/2016 y我希望得到如下所示的结果。
Animal Vaccine_Date Vaccine
Dog 1/1/2016 x
Dog 2/1/2016 y,z
Cat 2/1/2016 y,z我有下面的代码，它是通过我在"Combine(concatenate) rows based on dates via SQL“上的另一个帖子提供的。
WITH RECURSIVE recCTE AS
(
SELECT
animal,
vaccine_date,
CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list
cast (1 as int) as depth --used to determine the largest/last group_concate (the full group) in the final select
FROM TableOne
GROUP BY 1,2
UNION ALL
SELECT
recCTE.animal,
recCTE.vaccine_date,
trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
recCTE.depth + cast(1 as int) as depth
FROM recCTE
INNER JOIN TableOne ON
recCTE.animal = TableOne.animal AND
recCTE.vaccine_date = TableOne.vaccine_date and
TableOne.vaccine > recCTE.vaccine
WHERE recCTE.depth < 5
)
--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1但这会导致以下结果。
Animal Vaccine_Date vaccine depth
Cat 2/1/2016 y,z,z,z,z 5
Dog 1/1/2016 x 1
Dog 2/1/2016 y,z,z,z,z 5"z“一直在重复。这是因为代码说的是任何大于最低疫苗的内容。为了说明这一点，代码被更改为以下代码。
WITH RECURSIVE recCTE AS
(
SELECT
animal,
vaccine_date,
CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list
cast (1 as int) as depth, --used to determine the largest/last group_concate (the full group) in the final select
vaccine as vaccine_check
FROM TableOne
GROUP BY 1,2,5
UNION ALL
SELECT
recCTE.animal,
recCTE.vaccine_date,
trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
recCTE.depth + cast(1 as int) as depth,
TableOne.vaccine as vaccine_check
FROM recCTE
INNER JOIN TableOne ON
recCTE.animal = TableOne.animal AND
recCTE.vaccine_date = TableOne.vaccine_date and
TableOne.vaccine > recCTE.vaccine and
vaccine_check <> recCTE.vaccine_check
WHERE recCTE.depth < 5
)
--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1然而，这导致了以下结果。
Animal Vaccine_Date vaccine depth vaccine_check
Cat 2/1/2016 y 1 y
Dog 1/1/2016 x 1 x
Dog 2/1/2016 y 1 y代码中缺少什么以获得以下所需的结果。
Animal Vaccine_Date Vaccine
Dog 1/1/2016 x
Dog 2/1/2016 y,z
Cat 2/1/2016 y,zStack Overflow用户
发布于 2016-09-19 22:27:06
嗯。我手头没有Teradata，但这是该项目的一个主要缺点(在我看来)。我认为这对你来说是可行的，但它可能需要一些调整：
with tt as (
select t.*,
row_number() over (partition by animal, vaccine_date order by animal) as seqnum
count(*) over (partition by animal, vaccine_date) as cnt
),
recursive cte as (
select animal, vaccine_date, vaccine as vaccines, seqnum, cnt
from tt
where seqnum = 1
union all
select cte.animal, cte.dte, cte.vaccines || ',' || t.vaccine, tt.seqnum, tt.cnt
from cte join
tt
on tt.animal = cte.animal and
tt.vaccine_date = cte.vaccine_date and
tt.seqnum = cte.seqnum + 1
)
select cte.*
from cte
where seqnum = cnt;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39575359
复制相关文章
相似问题
腾讯云开发者
