这个SQL怎么弄!!!!

zlyanyan 2005-03-28 07:53:15
select cast(amountsum as varchar(10))+'×'+boxsize+boxtype as BoxInfo,conid
from (SELECT SUM(Amount) AS amountsum, BoxSize, BoxType, ConID
FROM T_PlanBox
GROUP BY ConID, BoxSize, BoxType) mm

查出:

boxinfo conid
23×20'GP 12312321
12×20'OT 12312321

想要看到的信息是:
boxallinfo conid
23×20'GP/12×20'OT 12312321

该怎么实现亚!
...全文
141 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
jinjazz 2005-03-29
  • 打赏
  • 举报
回复
--建立测试环境
Create Table 表(boxinfo varchar(10),conid varchar(10))
--插入数据
insert into 表
select '23×20''GP','12312321' union
select '12×20''OT','12312321'
--测试语句
go
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(40)
AS
BEGIN
DECLARE @r varchar(40)
SET @r=''
SELECT @r=@r+','+boxinfo FROM 表 WHERE conid=@vchA
RETURN(substring(@r,2,8000))
END
GO

select dbo.FunMergeCharField(conid) as boxinfo,conid from 表 group by conid
--删除测试环境
Drop Table 表
drop function FunMergeCharField

/*
boxinfo conid
---------------------------------------- ----------
12×20'OT,23×20'GP 12312321
*/
pchaos 2005-03-29
  • 打赏
  • 举报
回复
不固定怎么查?可以有好多组合
zlyanyan 2005-03-29
  • 打赏
  • 举报
回复
我是想如果在我查出的那些记录的基础上即
boxinfo conid
23×20'GP 12312321
12×20'OT 12312321
再转化:而且GP OT 都不是固定的
boxallinfo conid
23×20'GP/12×20'OT 12312321
该怎么办,如果用在程序中写,该怎么实现。遍历马?
smiler007 2005-03-28
  • 打赏
  • 举报
回复
SELECT cast(amountsum_gp AS varchar(10))
+ '×' + boxsize + 'GP/' + cast(amountsum_ot AS varchar(10))
+ '×' + boxsize + 'OT' AS BoxInfo, conid
FROM (SELECT SUM(CASE BoxType WHEN 'GP' THEN Amount ELSE 0 END)
AS amountsum_gp,
SUM(CASE BoxType WHEN 'OT' THEN Amount ELSE 0 END)
AS amountsum_ot, BoxSize, BoxType, ConID
FROM T_PlanBox
GROUP BY ConID, BoxSize) mm
GROUP BY CONID
flamboyant 2005-03-28
  • 打赏
  • 举报
回复
SELECT cast(amountsum_gp AS varchar(10))
+ '×' + boxsize + 'GP/' + cast(amountsum_ot AS varchar(10))
+ '×' + boxsize + 'OT' AS BoxInfo, conid
FROM (SELECT SUM(CASE BoxType WHEN 'GP' THEN Amount ELSE 0 END)
AS amountsum_gp,
SUM(CASE BoxType WHEN 'OT' THEN Amount ELSE 0 END)
AS amountsum_ot, BoxSize, BoxType, ConID
FROM T_PlanBox
GROUP BY ConID, BoxSize) mm

2,495

社区成员

发帖
与我相关
我的任务
社区描述
Delphi 数据库相关
社区管理员
  • 数据库相关社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧