34,590
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE TABLE TB(COL1 DATETIME,COL2 INT,COL3 VARCHAR(10))
INSERT INTO TB
SELECT '2010-01-02', 100 ,'a' UNION ALL
SELECT '2010-01-03', 100 ,'a' UNION ALL
SELECT '2010-02-02', 100 ,'a' UNION ALL
SELECT '2010-03-02', 100 ,'a' UNION ALL
SELECT '2010-03-02', 100 ,'b'
GO
CREATE FUNCTION FUN_MU(@COL3 VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+CAST(MONTH(COL1) AS VARCHAR(2))+'月'
FROM TB
WHERE COL3=@COL3
GROUP BY MONTH(COL1)
RETURN @STR
END
GO
SELECT dbo.FUN_MU(COL3),SUM(COL2),COL3
FROM TB
GROUP BY COL3
/*
1月,2月,3月 400 a
3月 100 b
*/
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (日期 datetime,金额 int,经手人 varchar(1))
insert into [tb]
select '2010-01-02',100,'a' union all
select '2010-01-03',100,'a' union all
select '2010-02-02',100,'a' union all
select '2010-03-02',100,'a' union all
select '2010-03-02',100,'b'
go
--创建函数
create function get_str(@man varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(50)
select @sql=isnull(@sql+'、','')+月份 from
(select distinct 月份=datename(mm,日期) from tb where 经手人=@man)a
return @sql
end
go
select 日期=dbo.get_str(经手人),金额=sum(金额),经手人 from [tb] group by 经手人
--结果:
日期 金额 经手人
-------------------------------------------------- ----------- ----
01、02、03 400 a
03 100 b