可以, 如果你用sql 2005的话, 可以写一个聚合的CLR函数
解决方案 »
- sql server 中有什么sql语句可以把表的所有字段显示出来
- 急求SQL语句,在线等!!!!!
- 在ER/Studio里面,如何做到LogicModel和PhysicalModel之间的同步
- 求sql语句:取每组中前三行
- 一个简单的问题,在线等
- 请问 统计各 型号的 销售 比例 的 sql 语句 如何写?????????? 急!!!!
- 请教一个较难的问题
- 新闻表(新闻id,内容) 评论表(新闻id,评论人) 选择 内容,评论数
- USE a51118101 Exec sp_addrolemember db_owner,'a51118101'
- 请教:如何使sql server在每天指定的时间执行一些sql语句操作
- SQL SP4补丁的卸载
- 如何在触发器上加上事务回滚?
(F1 char(10),
F2 char(2))
insert into Test
select '001' F1,'01' F2
union
select '001' F1,'02' F2
union
select '001' F1,'03' F2 用试图表示为
volumn box
1 1-3
(Order_No char(8),
Volumn_Code char(8),
Box_Code char(2))
insert into Consign
select '00000001' Order_No,'00000001' Volumn_Code,'01' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'02' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'03' Box_Code 用视图表示为
Order_No Volumn_Code Box_Code
00000001 00000001 1-3
这里可能要用自定义函数实现
(Order_No char(8),
Volumn_Code char(8),
Box_Code char(2))
insert into Consign
select '00000001' Order_No,'00000001' Volumn_Code,'01' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'02' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'03' Box_Code
UNION
select '00000001' Order_No,'00000001' Volumn_Code,'07' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'08' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'30' Box_Code
GO
--生成已用编号分布字符串的函数
CREATE FUNCTION f_Box_Code(
@Order_No char(8),
@Volumn_Code char(8)
)RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000),@pid int
SELECT @re='',@pid=-1
SELECT @re=CASE
WHEN col2=@pid+1 THEN @re
ELSE @re
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END
+','+CAST(col2 as varchar)
END,
@pid=col2
FROM(
SELECT col2 = CAST(Box_Code as int)
FROM Consign
WHERE Order_No = @Order_No
AND Volumn_Code = @Volumn_Code
AND ISNUMERIC(Box_Code) = 1
) tb
ORDER BY col2
RETURN(STUFF(@re,1,2,'')
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END)
END
GO
-- 视图
CREATE VIEW v_Consign
AS
SELECT
Order_No, Volumn_Code,
Box_Code = dbo.f_Box_Code(Order_No, Volumn_Code)
FROM(
SELECT DISTINCT
Order_No, Volumn_Code
FROM Consign
)A
GO-- 显示视图
SELECT * FROM v_Consign
GO-- 删除测试
DROP TABLE Consign
DROP VIEW v_Consign
DROP FUNCTION dbo.f_Box_Code
-------- ----------- ----------------
00000001 00000001 1-3,7-8,30(1 行受影响)
(
idno int
)
declare @i int
set @i=0
while(@i<100)
begin
insert test select @i
insert test select @i
set @i=@i+1end
create function mysum (@idno int)
returns int
as
begin
declare @sum bigint
set @sum=0
select @sum=@sum+idno from test where idno=@idno
return @sum
endselect idno,dbo.mysum(idno) from test group by idno--这样的聚合函数带有很大的局限性
-------- ----------- ----------------
00000001 00000001 30(1 行受影响)
(Order_No char(8),
Volumn_Code char(8),
Box_Code char(2))
insert into Consign
select '00000001' Order_No,'00000001' Volumn_Code,'01' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'02' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'03' Box_Code
UNION
select '00000001' Order_No,'00000001' Volumn_Code,'07' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'08' Box_Code
union
select '00000001' Order_No,'00000001' Volumn_Code,'30' Box_Code
union
select '00000002' Order_No,'00000002' Volumn_Code,'01' Box_Code
union
select '00000002' Order_No,'00000002' Volumn_Code,'02' Box_Code
union
select '00000002' Order_No,'00000002' Volumn_Code,'06' Box_Code
GOselect id=identity(int,1,1) ,* into #t from consign order by convert(int,box_code)select order_no,volumn_code,min(box_code)+'-'+max(box_code) as box_code from #t group by order_no,volumn_code,convert(int,box_code)-iddrop table consign
drop table #t
/*Result:*/
/*再串下字串就可以了~~*/
order_no volumn_code box_code
-------- ----------- ---------
00000001 00000001 01-03
00000001 00000001 07-08
00000001 00000001 30-30
00000002 00000002 01-02
00000002 00000002 06-06
order by 确实有问题,加了就只处理最大的.而不加也有隐患.你新增一个05试试,此时的05就永远最后一个处理.即结果是"...30,05".邹兄来解释一下啊