时间戳

首页 > 解决方案 > 如何在 T-SQL 中操作字符串？

sql - 如何在 T-SQL 中操作字符串？

问题描述

业务编号 开放日 开放时间 close_day 关闭时间
1 星期五 08:00 星期五 12:00
1 星期五 13:00 星期五 17:00
1 周一 08:00 周一 17:00
2 周六 08:00 周六 16:00
2 星期日 08:00 星期日 16:00
3 周一 08:00 周一 16:00

我的任务是为企业创建和格式化营业时间，我需要将这些按天分组到一个字符串中。然而，它正在重复这一天。如果某个特定值在一个字符串中出现两次，是否可以搜索它？我已经做到了这一点：

create table open_times (business_id int, open_day varchar(10), open_time varchar(10), close_day varchar(10), close_time varchar(10) )
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'FRIDAY', '08:00', 'FRIDAY', '12:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'FRIDAY', '13:00', 'FRIDAY', '17:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'MONDAY', '08:00', 'MONDAY', '17:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (2, 'SATURDAY', '08:00', 'SATURDAY', '16:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (2, 'SUNDAY', '08:00', 'SUNDAY', '16:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (3, 'MONDAY', '08:00', 'MONDAY', '16:00')

drop table open_times

我感谢您的帮助。

    select 
    business_id
    ,left(name_values, LEN(name_values)-1) as opening_hours
from 
    (
    select 
            results.business_id
            ,STUFF((
                    select
                     ( case when open_day = 'FRIDAY' then 'Fr' when open_day = 'MONDAY' then 'Mo' when open_day = 'TUESDAY' then 'Tu' when open_day = 'WEDNESDAY' then 'We' When open_day = 'THURSDAY' then 'Th' when open_day = 'SATURDAY' then 'Sa' else 'Su' end )
                        + ' ' + open_time + '-' + close_time + '; '
                    from open_times
                    where business_id = results.business_id 
                    for xml path(''),type).value('(./text())[1]','VARCHAR(MAX)'),1,0, '')  as name_values
            from open_times results
            group by business_id
    )  innerquery




Current Output for Business 1: 'Fr 08:00-12:00; Fr 13:00-17:00; Mo 08:00-17:00'   
Desired Output For Business 1: 'Fr 08:00-12:00, 13:00-17:00; Mo 08:00-17:00'

标签： sqlsql-servertsqlsql-server-2016

解决方案

你可以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<>小提琴

推荐阅读