SQL group by and wmsys.wm_concat 的使用和乱码问题
来源:互联网 发布:java反射应用场合 编辑:程序博客网 时间:2024/05/22 02:19
今天做那个自定义报表做了二三天,写了三天的SQL,还是有问题。
问题是:做现在要以wmps_traceID 为主唯一查询,不要出现重复,然后要把价格累加。 还有一个 ws.userdefined5 字段里面有描述,要把 ws.userdefined5这个字段 不同的内容用 , 放一起显示出来,还不能重复。
业务逻辑就是 以箱子id为主,一个箱子里面 有很多个产品,然后每个产品的价格和数量不一样。要把一个箱子里面的所以的产品价格*数量 总价格显示出来,然后还要把每个产品的描述放一起用 , 分开。
最后解决的办法是:
group by 把需要合并的字段去点 就不会出现重复的了。
wm_concat(逗号分隔,不重复的话wm_concat(distinct ...))
wm_concat 乱码问题 加个to_char()就可以啦。
example: replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
select
TO_CHAR(SYSDATE,'yyyymmdd') as fld_TM_manifest_id,
1 as fld_TM_cartons ,
nvl(ps.wmps_grossweight,0)as fld_TM_cbm ,
nvl(ps.wmps_cubic,0)as fld_TM_weight ,
'AIR'as fld_TM_container_type,
'' as fld_TM_container_no,
'AUD' as fld_TM_seal_no,
''as fld_TM_vessel_name,
TO_CHAR(SYSDATE+1,'DD/MM/YYYY') as fld_TM_edt,
ps.wmps_traceID as fld_TMD_connote_no,
p.userdefined2 as fld_TMD_order_no,
1 as fld_TMD_cartons,
nvl(ps.wmps_cubic, 0) as fld_TMD_cbm,
nvl(ps.wmps_grossweight, 0) as fld_TMD_weight,
to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
TO_CHAR(SYSDATE+3,'dd/mm/yyyy') as fld_TO_import_date,
o.wmor_soreference2 as fld_TO_order_no,
1 as fld_TO_carrier,
o.wmor_consigneename as fld_TO_surname,
NVL(o.wmor_c_address1, ' ') as fld_TO_address_1,
NVL(o.wmor_c_address2, ' ') as fld_TO_address_2,
o.wmor_c_country as fld_TO_country_code,
o.wmor_c_city as fld_TO_suburb,
NVL(o.wmor_c_province, ' ') as fld_TO_state,
o.wmor_c_zip as fld_TO_postcode,
NVL(o.wmor_c_tel1, ' ') as fld_TO_telephone,
'AeParcelPac' as fld_TO_delivery_instr,
NVL(o.wmor_c_address3, ' ') as fld_TO_address_3,
NVL(ps.wmps_grossweight, 0) as fld_TO_weight,
1 as fld_TOL_line_no,
'' as fld_TOL_product_no,
'' as fld_TOL_item_no,
1 as fld_TOL_quantity,
TO_CHAR(SYSDATE+5,'DD/MM/YY') as fld_TOL_promised_date,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_price,
sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_total_amount,
replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,
'Jeanswest' as mame,
'11th Floor, Unit C, 2 Tins Centre'as addr1,
'Hung Cheung Road 3' as addr2,
'Tuen Mun' as city,
'Hong Kong' as state,
'NA' as postcode,
'HK'as country
from wm_out_packing_summary ps,wm_out_packing p,wm_out_order o,wm_out_order_sku os,cd_wh_sku ws
where
ps.wmps_traceid=p.wmop_traceid
and p.wmop_orderno=o.wmor_order_no
and p.wmop_sku_code=os.wmos_sku_code
and os.wmos_order_no=o.wmor_order_no
and ws.cdsk_sku_code=os.wmos_sku_code
group by
ps.wmps_traceID,o.wmor_soreference2,o.wmor_consigneename,o.wmor_c_country,o.wmor_c_city,o.wmor_c_zip,ps.wmps_grossweight,
ps.wmps_cubic,ps.wmps_cubic,ps.wmps_grossweight,o.wmor_c_address1,o.wmor_c_address2,o.wmor_c_province,o.wmor_c_tel1,o.wmor_c_address3,ps.wmps_grossweight,p.userdefined2;
写了二三天SQL,木有解决问题呀。 头疼,最后跑到CSDN论坛里面去问高手很快就解决了。 感激不尽,心底暗下决心 ,要好好学习,多逛逛论坛,尽自己能力帮别人解决问题。
- SQL group by and wmsys.wm_concat 的使用和乱码问题
- WMSYS.WM_CONCAT 过长问题的解决
- wmsys.wm_concat结果长度限制的问题
- wmsys.wm_concat结果长度限制的问题
- wmsys.wm_concat 长度限制的问题
- Oracle函数wmsys.wm_concat的使用
- Oracle函数wmsys.wm_concat的使用
- oracle wmsys.wm_concat(column)函数的使用
- oracle wmsys.wm_concat 函数的使用
- Oracle函数wmsys.wm_concat的使用
- sql: group by 和 limit的使用
- wmsys.wm_concat的用法
- SQL函数:WMSYS.WM_CONCAT行转列
- listagg within group 与 WMSYS.wm_concat
- sql,group by的使用
- SQL Group by的使用
- 关于oracle中的wmsys.wm_concat中的使用问题
- 关于oracle中的wmsys.wm_concat中的使用问题
- php可以加在HTML源代码的多个部位
- ACM1003
- vbs--- 字符串函数
- Oracle分页查询存储过程(适用于单表查询)
- C++之练习题16
- SQL group by and wmsys.wm_concat 的使用和乱码问题
- 解决android离线API打开速度缓慢的问题
- 获取时间戳,以及时间戳转换为时间
- Ubuntu下python安装mysqldb
- 生泰宝
- 【IT就业平台咨询】通信行业职业技能鉴定高级管理人员培训班在哈开班
- 调查:最佳的开源云项目是哪些?
- C语言中 extern 关键字的用法
- 基于Java NIO的即时聊天服务器模型