如何操作T-SQL中的字符串

Question

业务id1周五8:00>周五12:001周五13:00五17:001星期一08:00>星期一2星期六08:00>星期六2星期日08:00>星期日3星期一08:00>星期一

Answer 1

您可以在此处使用STRING_AGG。您只需要进行两个级别的分组，每天一次，然后对整个business_id进行一次

SELECT
ot.business_id,
Times = STRING_AGG(CONCAT(
UPPER(LEFT(ot.open_day, 1)),
LOWER(SUBSTRING(ot.open_day, 2, 1)),
' ',
ot.Times)
, '; ')
FROM (
SELECT
ot.business_id,
ot.open_day,
Times = STRING_AGG(CONCAT(
ot.open_time,
'-',
ot.close_time),
', ')
FROM open_times ot
GROUP BY ot.business_id, ot.open_day
) ot
GROUP BY ot.business_id;

db<gt；小提琴

Answer 2

您可以通过将窗口函数合并到逻辑中，使用一个聚合级别来实现这一点：

select ot.business_id,
stuff((select (case when seqnum = 1 then '; ' else ', ' end) +
(case when seqnum = 1 and ot2.open_day = 'FRIDAY' then 'Fr '
when seqnum = 1 and ot2.open_day = 'MONDAY' then 'Mo '
when seqnum = 1 and ot2.open_day = 'TUESDAY' then 'Tu '
when seqnum = 1 and ot2.open_day = 'WEDNESDAY' then 'We '
when seqnum = 1 and ot2.open_day = 'THURSDAY' then 'Th '
when seqnum = 1 and ot2.open_day = 'SATURDAY' then 'Sa '
when seqnum = 1 then 'Su '
else ''
end ) +
ot2.open_time + '-' + ot2.close_time 
from (select ot2.*,
row_number() over (partition by ot2.open_day order by ot2.open_time) as seqnum
from open_times ot2
where ot2.business_id = ot.business_id 
) ot2
order by ot2.open_day, ot2.open_time
for xml path(''),type
).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, ''
)  as name_values
from open_times ot
group by ot.business_id;

这里有一个db<gt；不停摆弄

您的查询版本实际上不正确，因为XML子查询中没有order by。结果可以是任何顺序。

此外，您不仅希望删除重复的名称，还希望将分隔符从;更改为,。

上面查询中的想法是每天枚举时间。第一次的分隔符是;，后续的分隔符则是,。缩写仅用于第一个。我还在所有专栏参考资料中添加了资格，这是一种强烈建议的做法。

请注意，您可以将case逻辑简化为：

left(ot2.open_day, 1) + lower(substring(ot2.open_day, 2, 1))

Answer 3

对于旧版本，请使用FOR XML PATH聚合技巧。假设每天不超过2个开放间隔

with t as (
select business_id, left(open_day,2) +  ' ' + min(open_time + '-' + close_time) +
case when min(open_time) = max(open_time) then '' 
else ', ' + max(open_time + '-' + close_time) end ots
from open_times
group by business_id, open_day
)
select business_id, stuff( 
(select '; ' + ots
from t t2
where t2.business_id = t1.business_id
order by left(ots,2)
for xml path(''))
, 1, 2, '') opening_hours
from t t1
group by business_id