22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT bpl.bus_id,r.route_id,r.route_name,sell.station_id,
s.station_id,s.station_name,d.section_serial,
bpl.vehicle_type_code,
(bpl.base_carriage + bpl.price_item_1 + bpl.price_item_2 + bpl.price_item_3 + bpl.price_item_4 + bpl.price_item_5 + bpl.price_item_6 + bpl.price_item_7 + bpl.price_item_8 + bpl.price_item_9 + bpl.price_item_10+ bpl.price_item_11 + bpl.price_item_12 + bpl.price_item_13 + bpl.price_item_14 + bpl.price_item_15) total_price
FROM route_info r, Route_section_lst d ,station_info s ,station_info v,route_direction_info rd, sell_station_info sell,
Bus_price_lst bpl,(
SELECT distinct a.sell_station_id,a.route_id FROM Route_section_lst a,route_info b,sell_station_info c
WHERE a.route_id=b.route_id AND a.sell_station_id=c.sell_station_id and c.station_id=b.start_station_id) e
WHERE
r.route_id = d.route_id And r.direction_id*=rd.direction_id AND r.start_station_id = v.station_id
AND d.end_station_id = s.station_id And e.route_id = d.route_id
And sell.sell_station_id=d.sell_station_id and bpl.station_id=s.station_id
and bpl.sell_station_id=d.sell_station_id and bpl.price_table_id='003'
and bpl.ticket_type='1' and s.station_id>'0002' ORDER BY bpl.bus_id,d.route_id,sell_station_name,d.section_serial
有表tb, 如下:
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
1 aa,bb
2 aaa,bbb,ccc
即,group by id, 求value 的和(字符串相加)
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *FROM (SELECT DISTINCT Id FROM @t)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
( SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/