表结构如下:
ss_ksfl表
fldm flmc
1 传感器
2 电脑基础
3 一方三通 ss_flmc表
fldm ctsm szbl
1 10 20
2 5 10
3 1 10yw_ks表
rhbh wtdm rhde df kssj
acer 1 A 2.00 2006-03-07 14:32:23.000
tfyzj 1 B 5.00 2006-03-07 16:23:32.000
tfyzj 2 B 10.00 2006-03-07 16:23:32.000
tfyzj 3 B 20.00 2006-03-07 16:23:32.000
tfyzj 4 B 10.00 2006-03-07 16:23:32.000ss_user表
id name pwd xq ssk xb
acer andylau aaa 1 1 1
tfyzj qwq 1 2 0ss_Mine表
id name
1 一矿
2 二矿ss_xq 表
id name
1 大同县
2 新荣区ss_ktlr 表
wtdm wtmc zqde fldm
1 传感器校验是否成功? B 1
2 传感器校验是否成功? B 1
3 传感器校验是否成功? B 2
4 传感器校验是否成功? B 3
说明:
ss_user.id=yw_ks.rhbh ss_user.xq=ss_xq.id ss_user.ssk=ss_mine.id要达到这样的效果:rhbh name xb xqname mkname flmc、szbl、df
acer andylau 1 大同县 一矿 传感器、20、2.00
tfyzj qwq 0 大同县 二矿 传感器、20、15.00、电脑基础、10、20.00
说明:
(flmc、szbl、df)是作为一个字段
传感器、20、15.00 这是一组数据电脑基础、10、20.00 这是一组数据
-----------------------------------------------------------------------------------
我写了个函数:
Create function F_UnionStr(@fldm varchar(10),@df varchar(10))
returns varchar(50)
as
begin
declare @return as varchar(50)
set @return=''
select @return=@return+'、'+cast(flmc as varchar) +'、'+cast(szbl as varchar)+'、'+cast(@df as varchar) from ss_ksfl,ss_flmx where ss_ksfl.fldm =ss_flmx.fldm and ss_ksfl.fldm =@fldm
return (stuff(@return,1,1,''))
end下面是查询语句:
select rhbh,b.name,b.xb, d.name, c.name ,考题类型=dbo.F_UnionStr(f.fldm,sum(df))
from yw_ks as a ,SS_USER as b ,SS_Mine as c , SS_XQ as d ,YW_KTLR as e , SS_KSFL as f , SS_FLMX as g
where a.rhbh = b.id and b.ssk = c.id and b.xq = d.id and a.wtdm = e.wtdm and f.fldm = e.fldm and g.fldm = f.fldm
group by rhbh, b.name,b.xb, d.name, c.name,f.fldm可查询出的效果是:
rhbh name xb xqname mkname flmc、szbl、df
acer andylau 1 大同县 一矿 传感器、20、2.00
tfyzj qwq 0 大同县 二矿 传感器、20、15.00
tfyzj qwq 0 大同县 二矿 电脑基础、10、20.00
tfyzj qwq 0 大同县 二矿 一方三通、10、10.00
tfyzj这个用户怎么合并到一起?!
ss_ksfl表
fldm flmc
1 传感器
2 电脑基础
3 一方三通 ss_flmc表
fldm ctsm szbl
1 10 20
2 5 10
3 1 10yw_ks表
rhbh wtdm rhde df kssj
acer 1 A 2.00 2006-03-07 14:32:23.000
tfyzj 1 B 5.00 2006-03-07 16:23:32.000
tfyzj 2 B 10.00 2006-03-07 16:23:32.000
tfyzj 3 B 20.00 2006-03-07 16:23:32.000
tfyzj 4 B 10.00 2006-03-07 16:23:32.000ss_user表
id name pwd xq ssk xb
acer andylau aaa 1 1 1
tfyzj qwq 1 2 0ss_Mine表
id name
1 一矿
2 二矿ss_xq 表
id name
1 大同县
2 新荣区ss_ktlr 表
wtdm wtmc zqde fldm
1 传感器校验是否成功? B 1
2 传感器校验是否成功? B 1
3 传感器校验是否成功? B 2
4 传感器校验是否成功? B 3
说明:
ss_user.id=yw_ks.rhbh ss_user.xq=ss_xq.id ss_user.ssk=ss_mine.id要达到这样的效果:rhbh name xb xqname mkname flmc、szbl、df
acer andylau 1 大同县 一矿 传感器、20、2.00
tfyzj qwq 0 大同县 二矿 传感器、20、15.00、电脑基础、10、20.00
说明:
(flmc、szbl、df)是作为一个字段
传感器、20、15.00 这是一组数据电脑基础、10、20.00 这是一组数据
-----------------------------------------------------------------------------------
我写了个函数:
Create function F_UnionStr(@fldm varchar(10),@df varchar(10))
returns varchar(50)
as
begin
declare @return as varchar(50)
set @return=''
select @return=@return+'、'+cast(flmc as varchar) +'、'+cast(szbl as varchar)+'、'+cast(@df as varchar) from ss_ksfl,ss_flmx where ss_ksfl.fldm =ss_flmx.fldm and ss_ksfl.fldm =@fldm
return (stuff(@return,1,1,''))
end下面是查询语句:
select rhbh,b.name,b.xb, d.name, c.name ,考题类型=dbo.F_UnionStr(f.fldm,sum(df))
from yw_ks as a ,SS_USER as b ,SS_Mine as c , SS_XQ as d ,YW_KTLR as e , SS_KSFL as f , SS_FLMX as g
where a.rhbh = b.id and b.ssk = c.id and b.xq = d.id and a.wtdm = e.wtdm and f.fldm = e.fldm and g.fldm = f.fldm
group by rhbh, b.name,b.xb, d.name, c.name,f.fldm可查询出的效果是:
rhbh name xb xqname mkname flmc、szbl、df
acer andylau 1 大同县 一矿 传感器、20、2.00
tfyzj qwq 0 大同县 二矿 传感器、20、15.00
tfyzj qwq 0 大同县 二矿 电脑基础、10、20.00
tfyzj qwq 0 大同县 二矿 一方三通、10、10.00
tfyzj这个用户怎么合并到一起?!
select distinct rhbh,name,xb,xqname,mkname,f_otherfunction( rhbh) as flmc、szbl、df
from (
select rhbh,b.name,b.xb, d.name, c.name ,考题类型=dbo.F_UnionStr(f.fldm,sum(df))
from yw_ks as a ,SS_USER as b ,SS_Mine as c , SS_XQ as d ,YW_KTLR as e , SS_KSFL as f , SS_FLMX as g
where a.rhbh = b.id and b.ssk = c.id and b.xq = d.id and a.wtdm = e.wtdm and f.fldm = e.fldm and g.fldm = f.fldm
group by rhbh, b.name,b.xb, d.name, c.name,f.fldm
) a
-----不存在你说的问题,因为在Select 用了distinct