select usr,
(case when prd<>'' then
prd+'/'+aa end) aa
from 表名
group by usr我的意思是想把同一usr名下的prd都连起来,但是在prd+aa中的aa本来是不存在的,所以会出错说没有aa这一个字段名,大家帮我想想办法,怎么才能用最少的语句实现这种效果,
(case when prd<>'' then
prd+'/'+aa end) aa
from 表名
group by usr我的意思是想把同一usr名下的prd都连起来,但是在prd+aa中的aa本来是不存在的,所以会出错说没有aa这一个字段名,大家帮我想想办法,怎么才能用最少的语句实现这种效果,
大家有什么方法
var str : string;
with adoquery1 do
begin
close;
sql.clear;
sql.add('select a.person_id,c.person_name,c.age,b.station into #d from a,b,c where a.person_id=c.person_id and a.station_id=b.station_id where @@rowcount>0');
execsql;
end;
//////////////////////////////////////
with adoquery1 do
begin
close;
sql.clear;
sql.add('select people_id,people_name,age from c');
open; //得到people_id,people_name,age
if recordcount <= 0 then exit;
while not eof do
begin
adoquery2.close;
adoquery2.sql.clear;
adoquery2.sql.add(select b.station as station from a,b,c where a.person_id=c.person_id and a.station_id=b.station_id and a.people_id=:id');
parameters.parabyname().value := fieldbyname('people_id').asinteger;
open; ///根据people_id得到相应的station
if recordcount <> 0 then
begin
while NOT adoquery2.eof do
begin
str := adoquery2.FieldValues['station'];
adoquery2.next;
end;
end;
adoquery2.close;
adoquery2.sql.clear;
adoquery2.sql.add(insert into #d values(:id,:name,:age,:station));
adoquery2.parameters.parabyname('id').value := FieldValues['peoper_id'];
adoquery2.parameters.parabyname('name').value := FieldValues['peoper_name'];
adoquery2.parameters.parabyname('age').value := FieldValues['age'];
adoquery2.parameters.parabyname('station').value := str;
execsql;/////至此添加第一个people_id的所有的信息到临时表d中
next; ///循环第二个people_id
end;
close;
sql.clear;
sql.add('select * from #d');
open;///////从临时表d中获得数据
adoquery2.close;
adoquery2.sql.clear;
adoquery2.sql.add('drop table #d');
adoquery2.execsql;/////删除临时表
end;
from 表名
group by usr
pick the FAQ in MS SQL SERVER zone of CSDN.
我找到的资料是2000不支持自定义的聚合函数,阁下是否确定2000支持自定义的聚合函数?
unfortunately, can not use use defined function in SQL Server 2000.In my opinion, u'd better develop a Stored procedure to implement it.2 pdw2009(捡垃圾去上网)
sorry, something wrong with my Chinese IME.
去分析器试试。CREATE FUNCTION Fun_Str(@usr char(10)) --组合prd的自定义函数
returns varchar(4000)
AS
BEGIN declare @r varchar(8000)
set @r=''
select @r=@r+','+Rtrim(prd) from t1
where usr=@usr
return(stuff(@r,1,1,''))END
goCreate Table t1(usr char(100),prd char(100)) --测试表Insert Into t1
select 'AAA','111'
union all select 'AAA','222'
union all select 'AAA','333'
union all select 'BBB','111'
union all select 'BBB','222'
union all select 'CCC','111'select usr,dbo.Fun_Str(usr) from t1 Group by usr --查询DROP FUNCTION Fun_Str --删除函数
DROP TABLE t1 --删除测试表
就是如你那个函数,如果那个表名tl能够在函数的输入中写就是我所要的自定义函数了
(case when prd<>'' then
"prd+'/'+aa" end) aa
from 表名
group by usr