sql 一个字段是字符串逗号分隔，其他的事单独字符，拼接

最新推荐文章于 2024-05-02 01:04:40 发布
最新推荐文章于 2024-05-02 01:04:40 发布
阅读量488 收藏 7
点赞数 7
文章标签： sql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_24953751/article/details/134827502
版权 

    SELECT sss.*,co.id conid,(sss.invoicedAmount1-ifnull(b.entryAmount,0)) invoiceBalance  from (
    select id id,contract_number contractNumber,customer_name customerName,invoice_date invoiceDate1,entry_amount1 invoicedAmount1,
	
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',1),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'
    union all
    select  id id,contract_number contractNumber,customer_name customerName,invoice_date2 invoiceDate1,entry_amount2 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',2),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3' and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>0
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date3 invoiceDate1,entry_amount3 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',3),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>1
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date4 invoiceDate1,entry_amount4 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    (select substring_index(substring_index(invoice_tax_number,',',4),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',4),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',4),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',4),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',4),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>2
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date5 invoiceDate1,entry_amount5 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    (select substring_index(substring_index(invoice_tax_number,',',5),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',5),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',5),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',5),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>3
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date6 invoiceDate1,entry_amount6 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    (select substring_index(substring_index(invoice_tax_number,',',6),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',6),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',6),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>4
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date7 invoiceDate1,entry_amount7 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    (select substring_index(substring_index(invoice_tax_number,',',7),',',-1))
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',7),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>5
    union all
    select id id,contract_number contractNumber,customer_name customerName,invoice_date8 invoiceDate1,entry_amount8 invoicedAmount1,
    case when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=0 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=1 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=2 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=3 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=4 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=5 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=6 THEN
    null
    when (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))=7 THEN
    (select substring_index(substring_index(invoice_tax_number,',',8),',',-1))
    END as invoiceTaxNumber
    from invoice_info WHERE invoice_status = '3'  and (LENGTH(invoice_tax_number) - LENGTH( REPLACE (invoice_tax_number, ',', '')))>6
    ) sss
    LEFT JOIN contract_info co ON sss.contractNumber = co.contract_number
    left join
    (
    SELECT invoice_tax_number,sum(return_amount) entryAmount from money_back_info WHERE   invoice_tax_number is not null and return_type=2 GROUP BY  invoice_tax_number
    ) b on sss.invoiceTaxNumber=b.invoice_tax_number

    WHERE (sss.invoicedAmount1-ifnull(b.entryAmount,0))>0


    <if test="commonParamsVm.returnCompany!= null and commonParamsVm.returnCompany != ''  ">
      and sss.customerName!= #{commonParamsVm.returnCompany}
    </if>
    <if test="commonParamsVm.dimParamValue!= null and commonParamsVm.dimParamValue != ''  ">

      AND (sss.customerName like CONCAT(CONCAT('%', #{commonParamsVm.dimParamValue}), '%')
        or sss.contractNumber like CONCAT(CONCAT('%', #{commonParamsVm.dimParamValue}), '%')
      or sss.invoiceTaxNumber like CONCAT(CONCAT('%', #{commonParamsVm.dimParamValue}), '%')
      )
    </if>
    ORDER BY
    sss.id DESC

无心1314
关注
  • 7
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sql server拼接字符串和拼接一列的值
09-03
sql server拼接字符串查询语句。 普通拼接字符串和拼接某一列的所有值。
使用sql语句实现字符串根据逗号分隔并减一重新赋值
weixin_52308594的博客
12-04 2955
使用sql语句实现字符串根据逗号分隔并减一重新赋值
sql以某个字段的逗号分开成多条数据
a506602491的专栏
11-12 2521
SELECT NEWID(),a.RYBH,NULL,NULL,SUBSTRING(a.ZCZY, b.number, CHARINDEX(',', a.ZCZY+',', b.number)-b.number) AS Specialty,a.ZCZY FROM DataCenter.dbo.XXZX_YPTQY_T_QY_ZYJSRYZCXX a JOIN MASTER.dbo.spt_va...
Leetcode:（剑指 Offer 58 - II.）左旋转字符串-------python
yuansiming0920的博客
09-13 190
1 题目描述 字符串的左旋转操作是把字符串前面的若干个字符转移到字符串的尾部。请定义一个函数实现字符串左旋转操作的功能。比如，输入字符串"abcdefg"和数字2，该函数将返回左旋转两位得到的结果"cdefgab"。 示例 1： 输入: s = “abcdefg”, k = 2 输出: “cdefgab” 示例 2： 输入: s = “lrloseumgh”, k = 6 输出: “umghlrlose” 限制： 1 <= k < s.length <= 10000 2 解题思路 就是字符
sql with as 用法_SQL查询语句-简单查询
weixin_39923110的博客
12-09 355
一、SQL基本查询语句1、从表中查询2列select 姓名,性别 from student;2、查询出全部列select * from student;3、为列赋予别名select 姓名 as s_name,性别 as '人类性别' from student;4、删除重复数据①删除某列重复数据select distinct 姓名 from student;②删除两列的重复数据select dist...
SQL SERVER 把逗号隔开的字符串拆分成行
wys0127的博客
02-09 5003
SQL SERVER 把逗号隔开的字符串拆分成行，有效
如何用sql处理拆分字段内带逗号拼接及用逗号合并同一字段内的值
weixin_43949154的博客
08-19 8174
数据库如何处理拆分字段内带逗号拼接及用逗号合并同一字段内的值同一字段内存在用逗号拼接的 同一字段内存在用逗号拼接的
浅析MySQL中concat以及group_concat的使用
热门推荐
海阔天空sky的博客
08-01 12万+
说明： 本文中使用的例子均在下面的数据库表tt2中执行： 一、concat()函数 1、功能：将多个字符串连接成一个字符串。 2、语法：concat(str1, str2,...) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。 3、举例： 例1:select concat (id, name, score) as info from tt
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
12-14
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
SQL SERVER 把逗号隔开的字符串拆分成行，有效哦！
u012949335的专栏
07-17 1万+
例如：比如 产品字段protest有逗号组合，现在把这些分割开来。 变成如下： 方法如下： create table dbo.test_name( id int null ,name varchar(10) null ,protest varchar(500) null ); insert into dbo.test_name values ('1','张三','电脑,电视机,扫地机'); insert into dbo.test_name values ('2','李四',.
SQL server中字符串逗号分隔函数分享
01-21
继SQl -Function创建函数数据库输出的结果用逗号隔开，在开发中也有许多以参数的形式传入带逗号字条串参数（数据大时不建议这样做） 例：查找姓名为“张三,李二” 的数据此时在数据库里就要对此参数做处理如图： ...
SQL Server把某个字段的数据用一条语句转换成字符串
09-10
主要介绍了SQL Server把某个字段的数据用一条语句转换成字符串,本文讲解使用一条语句把字段中的数据查询出来并以指定分割符组成字符串,需要的朋友可以参考下
MyBatis 动态拼接Sql字符串的问题
09-01
MyBatis的动态SQL，解决了SQL字符串拼接的痛苦。下文分步骤给大家详细介绍了MyBatis 动态拼接Sql字符串的问题，非常不错，感兴趣的朋友一起看下吧
SQL Server的基本操作示例
weixin_58566539的博客
04-29 507
SQL Server的基本操作示例
常用SQL命令
最新发布
wenxiaoba的博客
05-02 932
常用SQL语句使用说明和示例
优化SQL的方法
weixin_44139651的博客
04-26 1480
使用join实现，但不适合join太多表，阿里巴巴开发者手册的规定，join表的数量不应该超过3个，join表数量太多时，会导致mysql在选错索引。一般SQL优化第一考虑的是索引优化，可使用explain命令，查看MySQL的执行计划，确认SQL是否有走索引。相对连接查询，子查询使用in关键字实现，具有结构化，相对简单，但是需要创建和删除临时表，增加资源消耗。索引可提升SQL效率，但索引需要额外的存储空间，而且还会有一定的性能消耗。left join，两个表的交集，以及左表剩余的数据，左表为驱动表。
【SQL Server】入门教程-基础篇(二)
m0_74229735的博客
04-29 842
本章要讲的就是Sql Server的高级语言，还有后续。
SQL LPAD函数使用
sxk1101的博客
04-29 423
Oracle SQL 中的LPAD函数是一个用于格式化字符串的函数，它会在给定字符串的左侧填充指定的字符，直到字符串达到指定的长度。这个函数在数据库查询中非常有用，尤其是在需要对输出进行格式化时，比如在报表中。
sql面试题
qq_24099547的博客
04-28 678
表student中记录学生的成绩信息, 要求只保留学生的最新一条成绩记录。
sql查询字段是由字符串拼接字符串的数据怎么in查询
06-13
如果要在 SQL 中对由字符串拼接字符串的数据进行 IN 查询，可以使用字符串分割函数将其拆分为多个值，然后再进行 IN 查询。 例如，假设有一个字段名为 `concat_str`，值为 `a,b,c`，需要进行 IN 查询，可以使用以下 SQL 语句： ```sql SELECT * FROM table_name WHERE FIND_IN_SET('a', concat_str) > 0 OR FIND_IN_SET('b', concat_str) > 0 OR FIND_IN_SET('c', concat_str) > 0; ``` 其中，`FIND_IN_SET` 函数可以查找某个值是否在逗号分隔的字符串列表中，如果存在，返回其在列表中的位置，否则返回 0。这样，就可以对每个需要查询的值进行判断，将结果进行 OR 运算，最终得到 IN 查询的结果。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值