--测试CREATE TABLE tb3 (PartNO CHAR(3),Partname CHAR(10)) INSERT INTO tb3 SELECT '001','A' UNION ALL SELECT '001','B' UNION ALL SELECT '001','C' UNION ALL SELECT '002','D' UNION ALL SELECT '002','E' DECLARE @sql VARCHAR(3000), @count1 INT, @count2 INT, @id CHAR(10), @name VARCHAR(10), @partname VARCHAR(3000) SET @count1=1 SET @count2=1 SET @sql='' SET @name='' SET @partname='' DECLARE get_PartNO CURSOR FOR SELECT PartNO FROM tb3 GROUP BY PartNO OPEN get_PartNOWHILE @count1<=(SELECT COUNT(DISTINCT PartNO) FROM tb3) BEGIN FETCH get_PartNO INTO @id
DECLARE get_name CURSOR FOR SELECT Partname FROM tb3 WHERE PartNO=@id OPEN get_name
SET @partname='' SET @count2=1 WHILE @count2<=(SELECT COUNT(Partname) FROM tb3 WHERE PartNO=@id) BEGIN FETCH get_name INTO @name SELECT @partname=@partname+@name+'+' SELECT @count2=@count2+1 END DEALLOCATE get_name
SELECT @partname=SUBSTRING(@partname,1,len(@partname)-1) SELECT @sql=@sql+' UNION ' SELECT @sql=@sql+'SELECT PartNO,'+'('''+@partname+''') AS Partname FROM tb3 WHERE PartNO='+@id SELECT @count1=@count1+1 ENDSELECT @sql=SUBSTRING(@sql,8,len(@sql)-7) DEALLOCATE get_PartNOEXEC(@sql)--结果PartNO Partname 001 A +B +C 002 D +E
select distinct PartNO, sum(Partname ) from TableA group by PartNO
剛剛去拔河了,2:1 贏了!真怪 。 第一局我上了,輸了。以下兩局我沒有上,贏了! sunboy19(sunboy19)謝謝老兄! A,B,C是有順序的 PartNO Partname order 001 A 1 001 B 3 001 C 2 002 M 2 002 N 1 實現 PartNO FullPartname 001 A+C+B 002 N+M orer 為int 按其進行排序!
俺也想帮你,可是俺也不会,只有帮你ding了
With Data.QUERY1 do begin close ; sql.Clear ; sql.Add('select count(*) 张数,sum(cast(timelen as numeric(10))) 秒数,tel 字头 from hd'+Edit1.Text+' group by tel order by 1') ; open ; end ;这是我的原代码,一定能帮你的。 select PartNO,sum(cast(Partname as numeric(10))) Partname from TableA Group by PartNO
不好意思,这几天在外面出差,刚回来,现在就把例子发给你,你看一下,不懂的地方再联系 create table #PartA(PartNo varchar(10), PartName char(10)) insert into #PartA select PartNo, '' from #Part group by PartNo order by PartNoselect IDENTITY(int,1,1) BID, PartNo, PartName, OrderID into #PartB from #Part order by PartNo, OrderIDdeclare @nID int, @sPartNo varchar(10), @sPartName varchar(10) select Top 1 @sPartName = PartName, @sPartNo = PartNo from #PartB select @nID = 1 while exists(select BID from #PartB) begin delete #PartB where BID = @nID update #PartA set PartName = PartName + @sPartName where PartNo = @sPartNo
select Top 1 @sPartName = PartName, @sPartNo = PartNo from #PartB select @nID = @nID + 1 end select * from #PartA order by PartNodrop table #PartA drop table #PartB
Function AddPartname(@partNo varchar(12)) returns varchar as declare @str varchar(3000) begin set @str='' select @str=@str+'+'+partname from TableA where PartNo=@partNo set @str=substring(@str,2,len(@str) return @str endselect distinct PartNo,AddPartname(Partname) from TableA
测试OK:use tempdb go Create Function F_str(@partno varchar(3000)) returns varchar(3000) as begin declare @Str varchar(3000) set @Str='' select @Str=@Str+'+'+Partname From tablea where PartNo=@partno return(substring(@Str,2,len(@Str))) end go------------------------------ select distinct partno,Partname=dbo.F_Str(partno) from tablea ----------------------- 001 A+B+C 002 M+N
select PartNO,sum(Partname) from tablea group by partno
select PartNO,Partname = sum( Partname ) from TableA group by PartNO 应该没错吧.
select PartNO,Partname = sum(Partname ) from TableA group by PartNO 要求PartName必须是能加的数据库类型!
测试OK:use tempdb go Create Function F_str(@partno varchar(3000)) returns varchar(3000) as begin declare @Str varchar(3000) set @Str='' select @Str=@Str+'+'+Partname From tablea where PartNo=@partno return(substring(@Str,2,len(@Str))) end go------------------------------ select distinct partno,Partname=dbo.F_Str(partno) from tablea ----------------------- 001 A+B+C 002 M+N
chinaandys(天煞孤星&&蛋炒饭) 謝謝老兄 不過----- guanli PartNO Partname 2000 001 A 2000 001 B 2001 001 C 2002 002 M 2002 002 N 實現 : guanli PartNO Partname 2000 001 A+B 2001 001 C 2002 002 M+N可否實現 ?謝謝了! 一會結帖,分數雖然少,但見大家熱情,和小生一點心意!謝謝大家了!
楼主大佬注意看我答案(SQL SERVER常用建立函数) Create Function F_str(@guanli varchar(3000)) returns varchar(3000) as begin declare @Str varchar(3000) set @Str='' select @Str=@Str+'+'+Partname From tablea where guanli=@guanli return(substring(@Str,2,len(@Str))) end go select distinct guanli,partno, guanli=dbo.F_Str(guanli) from tablea
自定义局和函数--------测试表 create table Test (F1 varchar(10), F2 varchar(10)) --插入数据 insert into Test select 'jack' F1,'book1' F2 union select 'jack' F1,'book2' F2 union select 'jack' F1,'book3' F2 union select 'Mary' F1,'book4' F2 union select 'Mary' F1,'book5' F2 union select 'Mike' F1,'book1' F2 union select 'Mike' F1,'book5' F2 union select 'Mike' F1,'book7' F2 union select 'Mike' F1,'book9' F2 --一条动态SQL语句go --合并函数 CREATE FUNCTION MergeCharField(@Group varchar(255)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+'+'+rtrim(F2) FROM Test WHERE F1=@GroupRETURN(substring(@r,2,8000)) END GO --调用 select F1 [name],dbo.MergeCharField(F1) [book] from test group by F1 --删除测试环境 drop table test drop FUNCTION MergeCharFieldjack book1+book2+book3 Mary book4+book5 Mike book1+book5+book7+book9
jinjazz(近身剪(N-P攻略)) 有兩個字段控制
guanli PartNO Partname 2000 001 A 2000 001 B 2001 001 C 2002 002 M 2002 002 N guanli PartNO Partname 2000 001 A+B 2001 001 C 2002 002 M+N
--测试表 create table Test (guanli varchar(10), PartNO varchar(10), Partname varchar(10)) go --插入数据 insert into Test select '2000' guanli,'001' PartNO,'A' Partname union select '2000' guanli,'001' PartNO,'B' Partname union select '2001' guanli,'001' PartNO,'C' Partname union select '2002' guanli,'002' PartNO,'M' Partname union select '2002' guanli,'002' PartNO,'N' Partnamego--合并函数 CREATE FUNCTION MergeCharField(@Group1 varchar(255),@Group2 varchar(255)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+'+'+rtrim(Partname) FROM Test WHERE guanli=@Group1 and PartNO=@Group2 RETURN(substring(@r,2,8000)) END GO--调用 select guanli, PartNO,dbo.MergeCharField(guanli,PartNO) as Partname from test group by guanli, PartNO--删除测试环境 drop table test drop FUNCTION MergeCharField
先给个语句吧(不一定对啊,好长时间没玩了)
SELECT SUM(Partname) FROM TableA GROUPBY(PartNO)
from TableA
group by PartNO
如果是numeric参照楼上的就可以了。
如果是varchar之类的麻烦些
INSERT INTO tb3
SELECT '001','A'
UNION ALL SELECT '001','B'
UNION ALL SELECT '001','C'
UNION ALL SELECT '002','D'
UNION ALL SELECT '002','E'
DECLARE @sql VARCHAR(3000),
@count1 INT,
@count2 INT,
@id CHAR(10),
@name VARCHAR(10),
@partname VARCHAR(3000)
SET @count1=1
SET @count2=1
SET @sql=''
SET @name=''
SET @partname=''
DECLARE get_PartNO CURSOR
FOR SELECT PartNO FROM tb3 GROUP BY PartNO
OPEN get_PartNOWHILE @count1<=(SELECT COUNT(DISTINCT PartNO) FROM tb3)
BEGIN
FETCH get_PartNO INTO @id
DECLARE get_name CURSOR
FOR SELECT Partname FROM tb3 WHERE PartNO=@id
OPEN get_name
SET @partname=''
SET @count2=1
WHILE @count2<=(SELECT COUNT(Partname) FROM tb3 WHERE PartNO=@id)
BEGIN
FETCH get_name INTO @name
SELECT @partname=@partname+@name+'+'
SELECT @count2=@count2+1
END
DEALLOCATE get_name
SELECT @partname=SUBSTRING(@partname,1,len(@partname)-1) SELECT @sql=@sql+' UNION '
SELECT @sql=@sql+'SELECT PartNO,'+'('''+@partname+''') AS Partname FROM tb3 WHERE PartNO='+@id
SELECT @count1=@count1+1
ENDSELECT @sql=SUBSTRING(@sql,8,len(@sql)-7)
DEALLOCATE get_PartNOEXEC(@sql)--结果PartNO Partname
001 A +B +C
002 D +E
001 A +B +C
A,B,C的顺序有没有要求?没要求简单
group by PartNO
PartNO Partname order
001 A 1
001 B 3
001 C 2
002 M 2
002 N 1 實現 PartNO FullPartname
001 A+C+B
002 N+M orer 為int 按其進行排序!
begin
close ;
sql.Clear ;
sql.Add('select count(*) 张数,sum(cast(timelen as numeric(10))) 秒数,tel 字头 from hd'+Edit1.Text+' group by tel order by 1') ;
open ;
end ;这是我的原代码,一定能帮你的。
select PartNO,sum(cast(Partname as numeric(10))) Partname
from TableA
Group by PartNO
按照加工順序order 實現
partno newpartname
001 MM+MC (舉例,如果001有兩個工程,按其order排序)
002 MM+MC+FG
不好意思了,這次我說明白了! 也就是說,partname 字段是char(10) ,如果是int,我自己解決算了!
create table #PartA(PartNo varchar(10), PartName char(10))
insert into #PartA
select PartNo, ''
from #Part
group by PartNo
order by PartNoselect IDENTITY(int,1,1) BID, PartNo, PartName, OrderID into #PartB
from #Part
order by PartNo, OrderIDdeclare @nID int, @sPartNo varchar(10), @sPartName varchar(10)
select Top 1 @sPartName = PartName, @sPartNo = PartNo from #PartB
select @nID = 1
while exists(select BID from #PartB)
begin
delete #PartB where BID = @nID
update #PartA set PartName = PartName + @sPartName
where PartNo = @sPartNo
select Top 1 @sPartName = PartName, @sPartNo = PartNo from #PartB
select @nID = @nID + 1
end
select * from #PartA order by PartNodrop table #PartA
drop table #PartB
游标没什么问题啊, 如果用的太多是会耗资源,2万条记录,和临时表差不多的!奇异的方法就对了,还关注什么啊关注!
returns varchar
as
declare @str varchar(3000)
begin
set @str=''
select @str=@str+'+'+partname from TableA where PartNo=@partNo
set @str=substring(@str,2,len(@str)
return @str
endselect distinct PartNo,AddPartname(Partname) from TableA
go
Create Function F_str(@partno varchar(3000))
returns varchar(3000)
as
begin
declare @Str varchar(3000)
set @Str=''
select @Str=@Str+'+'+Partname From tablea where PartNo=@partno
return(substring(@Str,2,len(@Str)))
end
go------------------------------
select distinct partno,Partname=dbo.F_Str(partno) from tablea
-----------------------
001 A+B+C
002 M+N
应该没错吧.
from TableA
group by PartNO
要求PartName必须是能加的数据库类型!
go
Create Function F_str(@partno varchar(3000))
returns varchar(3000)
as
begin
declare @Str varchar(3000)
set @Str=''
select @Str=@Str+'+'+Partname From tablea where PartNo=@partno
return(substring(@Str,2,len(@Str)))
end
go------------------------------
select distinct partno,Partname=dbo.F_Str(partno) from tablea
-----------------------
001 A+B+C
002 M+N
2000 001 A
2000 001 B
2001 001 C
2002 002 M
2002 002 N
實現 :
guanli PartNO Partname
2000 001 A+B
2001 001 C
2002 002 M+N可否實現 ?謝謝了! 一會結帖,分數雖然少,但見大家熱情,和小生一點心意!謝謝大家了!
我的例子会出错吗?应该没问题吧,我这边测试过了
Create Function F_str(@guanli varchar(3000))
returns varchar(3000)
as
begin
declare @Str varchar(3000)
set @Str=''
select @Str=@Str+'+'+Partname From tablea where guanli=@guanli
return(substring(@Str,2,len(@Str)))
end
go
select distinct guanli,partno, guanli=dbo.F_Str(guanli) from tablea
2001 001 C
2002 002 M+N
譬如應該 guanli PartNO Partname
2000 001 A+B
2001 001 C
2002 002 M+N guanli PartNO Partname
2000 001 A+B+(不該加的)
2001 001 C+(不該加的)
2002 002 M+N+(不該加的)我沒有查到原因 ,紀錄結果前幾乎都為正確,後面正確的就少了
create table Test
(F1 varchar(10),
F2 varchar(10))
--插入数据
insert into Test
select 'jack' F1,'book1' F2
union
select 'jack' F1,'book2' F2
union
select 'jack' F1,'book3' F2
union
select 'Mary' F1,'book4' F2
union
select 'Mary' F1,'book5' F2
union
select 'Mike' F1,'book1' F2
union
select 'Mike' F1,'book5' F2
union
select 'Mike' F1,'book7' F2
union
select 'Mike' F1,'book9' F2
--一条动态SQL语句go
--合并函数
CREATE FUNCTION MergeCharField(@Group varchar(255))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+'+'+rtrim(F2) FROM Test WHERE F1=@GroupRETURN(substring(@r,2,8000))
END
GO
--调用
select F1 [name],dbo.MergeCharField(F1) [book] from test group by F1
--删除测试环境
drop table test
drop FUNCTION MergeCharFieldjack book1+book2+book3
Mary book4+book5
Mike book1+book5+book7+book9
guanli PartNO Partname
2000 001 A
2000 001 B
2001 001 C
2002 002 M
2002 002 N guanli PartNO Partname
2000 001 A+B
2001 001 C
2002 002 M+N
create table Test
(guanli varchar(10),
PartNO varchar(10),
Partname varchar(10))
go
--插入数据
insert into Test
select '2000' guanli,'001' PartNO,'A' Partname
union
select '2000' guanli,'001' PartNO,'B' Partname
union
select '2001' guanli,'001' PartNO,'C' Partname
union
select '2002' guanli,'002' PartNO,'M' Partname
union
select '2002' guanli,'002' PartNO,'N' Partnamego--合并函数
CREATE FUNCTION MergeCharField(@Group1 varchar(255),@Group2 varchar(255))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+'+'+rtrim(Partname) FROM Test WHERE guanli=@Group1 and PartNO=@Group2
RETURN(substring(@r,2,8000))
END
GO--调用
select guanli, PartNO,dbo.MergeCharField(guanli,PartNO) as Partname from test group by guanli, PartNO--删除测试环境
drop table test
drop FUNCTION MergeCharField
2001 001 C
2002 002 M+N
小弟 在线等 也可以加我QQ313739427 我在线等!!!!!!谢谢!!!
我是新手,对索引这些还不清楚,你能举个例子吗?
lk