想了半天都没搞出来，求SQL语句及思路

yuzhizhi 2021-04-05 04:36:15


想要得到如上结果。我的语句：

select a.SaleId, CAST( convert(varchar(10), a.date,112) AS DATE) '单据日期',a.CodeId'渠道号',
b.GoodsNo '货号',c.Color '颜色编号','0' '内长',
case when c.S1<>0 then 'S1'
when c.S2<>0 then 'S2'
when c.S3<>0 then 'S3'
when c.S4<>0 then 'S4'
when c.S5<>0 then 'S5'
when c.S6<>0 then 'S6'
when c.S7<>0 then 'S7'
when c.S8<>0 then 'S8'
when c.S9<>0 then 'S9'
when c.S10<>0 then 'S10'
when c.S11<>0 then 'S11'
when c.S12<>0 then 'S12'
when c.S13<>0 then 'S13'
when c.S14<>0 then 'S14'
end
'尺码', b.Quantity '数量',b.Price*b.Quantity '生意额' from 表A a
right join 表B b on a.SaleID=b.SaleID
right join 表C c on b.SaleIDNum=c.SaleIDNum where a.CheckDate = '2021-04-05' and Posted=1 and Customer_ID ='172101'

我用以上语句，得到如下结果，不是我想要的。


SQL语句如何写得到如下结果：

...全文
1059 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
冰思雨 2021-04-13
  • 打赏
  • 举报
 回复
上代码
SELECT t1.*, t2.*, t3.Color, t3.S1, t3.S3, t3.S4 FROM 表1 t1
   LEFT JOIN 表2 t2 ON t1.SaleId = t2.SaleID
	 LEFT JOIN (
		 SELECT SaleIDNum, Color, S1, NULL as S3, NULL as S4 FROM 表3 WHERE S1 <> 0
		 UNION ALL
		 SELECT SaleIDNum, Color, NULL, S3, NULL FROM 表3 WHERE S3 <> 0
		 UNION ALL
		 SELECT SaleIDNum, Color, NULL, NULL, S4 FROM 表3 WHERE S4 <> 0
	 ) t3 ON t3.SaleIDNum = t2.SaleIDNum
yuzhizhi 2021-04-12
  • 打赏
  • 举报
 回复
To 锟斤拷锟斤拷：是的，你说的我比较赞同。我也按你说的这个方向处理。 非常感谢你的指点！
锟斤拷锟斤拷 2021-04-12
  • 打赏
  • 举报
 回复
相对于普通的行转列，这个主要是先要有一个对于表C的列转行，然后接下来就是按行转列的思路进行处理 关于拼接动态SQL进行行转列可以参考这个问题里#6的方法 https://bbs.csdn.net/topics/398467884 另外 对于本问题中的#16 RINK_1大神的方法中的7行和18行，也可以通过 

SELECT ',['+Name+']' FROM SysColumns WHERE id = Object_Id( '表名' ) AND NAME LIKE 'S%' FOR XML PATH('')
对列名进行动态获取（如果以后会有S13 S14 或者 对列进行调整的情况存在）
yuzhizhi 2021-04-12
  • 打赏
  • 举报
 回复
To 文盲老顾：感谢你的指点与技术方案。向你学习了。 To shoppo0505：感谢你的指点与技术方案。 To 锟斤拷锟斤拷：感谢你的指点与技术方案。 To acen_chen：感谢你的指点。 To 泡泡鱼：感谢你的指点，因为这是某一家ERP已经定好的数据结构框架，最后需求按条形码拆解出来。我现在的方法就是暂时在程序完成逻辑中拆解，总感觉效率不高，所以把这块交给SQL来处理效率可能会更好。 To tnnd_007：感谢你的意见。 To morliz子轩：特别感谢您的思路与技术方案。让我受益非浅。 To RINK_1：特别感谢大神的思路与技术方案，让我更深刻学习到一些方法。
泡泡鱼_ 2021-04-12
  • 打赏
  • 举报
 回复
GoodsNo尾号75510的Quantity不是应该是2吗？？？为什么你想得到结果中，它的的Quantity是1?? 另：这表设计的与你需求的结果相比，有点不合理了。 然后说到你要拼接的这个结果，把它放去业务代码中进行处理，或者直接放去前端。就三个数组的关联解析组装数据而已，任何一个处理都比你这样强行在存储过程中处理的逻辑要清楚。否则你这个后期要是还有什么变更，维护的人会想死的心都有的
morliz子轩 2021-04-11
  • 打赏
  • 举报
 回复
深受RINK_1大神的启发，核心部分是尺码列名表与动态列名字串拼接生成。源码如下： 

declare @dynameicField nvarchar(100) ='',		--动态字段名
		@sql nvarchar(max) =''
;with cte as (
--尺码表
	select distinct type from #c a
	unpivot (qty for type in([S1],[S2],[S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10],[S11],[S12]))b
)
-- select * from cte
//动态列名拼接
select @dynameicField = stuff(( select (',['+type+ ']') from cte order by cast(replace(type,'s','') as int) for xml path('')),1,1,'')

set @sql = N'with cte as ('
set @sql +='select *'
set @sql +=',qty as quantity'
set @sql +=',row_number() over(partition by saleidnum,color order by getdate()) as rn'
set @sql +=' from #c as a'
set @sql +=' unpivot (qty for type in([S1],[S2],[S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10],[S11],[S12])) b )'

set @sql +='select saleid,codeid,billdate,goodsno,quantity,amount,color'
set @sql +=','+@dynameicField
set @sql += ' from ('
set @sql +='select a.saleid'
set @sql +=',a.codeid'
set @sql +=',a.billdate'
set @sql +=',b.goodsno'
set @sql +=',b.price as amount'
set @sql +=',c.*'
set @sql +=' from #a a'
set @sql +=' join #b b on a.saleid = b.saleid'
set @sql +=' join cte c on b.saleidnum = c.saleidnum'
set @sql +=')a'
set @sql +=' pivot (max(qty) for type in ('+@dynameicField+')) d'

exec sp_executesql @sql
最后生成的动态语句和输出结果：
RINK_1 2021-04-11
  • 打赏
  • 举报
 回复 

DECLARE @SQL VARCHAR(MAX)

WITH CTE
AS
(SELECT DISTINCT TYPE
FROM #C AS A
UNPIVOT (QTY FOR TYPE IN ([S1],[S2],[S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10],[S11],[S12])) B)

SELECT @SQL=STUFF((SELECT (',['+TYPE+']')
FROM CTE 
ORDER BY CAST(REPLACE(TYPE,'S','') AS INT)
FOR XML PATH('')),1,1,'')

SET @SQL='WITH CTE
AS
(SELECT *,QTY AS QUANTITY,ROW_NUMBER() OVER (PARTITION BY SALEIDNUM,COLOR ORDER BY GETDATE()) AS RN
FROM #C AS A
UNPIVOT (QTY FOR TYPE IN ([S1],[S2],[S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10],[S11],[S12])) B)

SELECT *
FROM 
(SELECT A.SALEID,A.CODEID,A.DATE,B.GOODSNO,B.PRICE,C.*
FROM #A AS A
JOIN #B AS B ON A.SALEID=B.SALEID
JOIN CTE AS C ON B.SALEIDNUM=C.SALEIDNUM) AS A
PIVOT (MAX(QTY) FOR TYPE IN ('+@SQL+')) D'

EXEC(@SQL)
yuzhizhi 2021-04-11
  • 打赏
  • 举报
 回复
TO morliz子轩：感谢您的回复，让我更深刻学习到一些方法。您这里的折解数量还是有问题的。
morliz子轩 2021-04-10
  • 打赏
  • 举报
 回复
花了1个小时，搞定： 

/*
if object_id('tempdb..#A') is not null
drop table #A
go
create table #A(
	SaleId varchar(10),
	CodeId int,
	BillDate date
)

insert into #A
select 'CMK0101201',172101,'2020-04-05'

-- select * from #a

if object_id('tempdb..#B') is not null
drop table #B
go
create table #B(
	SaleIDNum varchar(15),
	SaleId varchar(10),
	GoodsNo bigint,
	Quantity int,
	Price decimal(10,2)
)
insert into #B
select 'CMK01012010001','CMK0101201',621536472210,2,325 union all
select 'CMK01012010002','CMK0101201',821236475211,1,75

-- select * from #B

if object_id('tempdb..#C') is not null
drop table #C
go
create table #C(
	id int identity(1,1) not null,
	SaleIDNum varchar(15),
	Color int,
	S1 int,
	S2 int,
	S3 int,
	S4 int,
	S5 int,
	S6 int,
	S7 int,
	S8 int,
	S9 int,
	S10 int,
	S11 int,
	S12 int
)

insert into #C(SaleIDNum,Color,S1,S3,S4)
select 'CMK01012010001',411,1,null,null union all
select 'CMK01012010001',411,null,1,null union all
select 'CMK01012010001',411,null,null,1

-- select * from #C as c
*/

--查询体
declare @minId int,
		@maxId int,
		@SaleIDNum varchar(15),
		@Color int,
		@S1 int,
		@S2 int,
		@S3 int,
		@S4 int,
		@S5 int,
		@S6 int,
		@S7 int,
		@S8 int,
		@S9 int,
		@S10 int,
		@S11 int,
		@S12 int
/*
--变量体
declare @variable table(
	SaleIDNum varchar(15),
	Color int,
	S1 int,
	S2 int,
	S3 int,
	S4 int,
	S5 int,
	S6 int,
	S7 int,
	S8 int,
	S9 int,
	S10 int,
	S11 int,
	S12 int
)
*/
select @minId = min(id),@maxId = max(id) from #C
-- select @minId,@maxId
declare @sql nvarchar(max) =''

set @sql =N'select a.SaleId'
set @sql += ',a.CodeId'
set @sql += ',a.BillDate'
set @sql += ',b.GoodsNo'
set @sql += ',b.Quantity'
set @sql += ',b.Price * b.Quantity as Amount'
set @sql += ',c.Color'
set @sql += ',0 as ''内长'''

-- select @sql

	--遍历执行
	while(@minId is not null and @minId <=@maxId)
	begin
		select @SaleIDNum =@SaleIDNum
			,@Color =Color
			,@S1 =S1
			,@S2 =S2
			,@S3 =S3
			,@S4 =S4
			,@S5 =S5
			,@S6 =S7
			,@S7 =S7
			,@S8 =S8
			,@S9 =S9
			,@S10 =S10
			,@S11 =S11
			,@S12 =S12
		from #C where id = @minId
		-- select @SaleIDNum,@Color,@S1
	
		if isnull(datalength(@S1),0) >0
			set @sql += ',c.S1'
			
		if isnull(datalength(@S2),0) >0
			set @sql += ',c.S2'
		
		if isnull(datalength(@S3),0) >0
			set @sql += ',c.S3'
		
		if isnull(datalength(@S4),0) >0
			set @sql += ',c.S4'
		
		if isnull(datalength(@S5),0) >0
			set @sql += ',c.S5'
		
		if isnull(datalength(@S6),0) >0
			set @sql += ',c.S6'
		
		if isnull(datalength(@S7),0) >0
			set @sql += ',c.S7'
		
		if isnull(datalength(@S8),0) >0
			set @sql += ',c.S8'
		
		if isnull(datalength(@S9),0) >0
			set @sql += ',c.S9'
		
		if isnull(datalength(@S10),0) >0
			set @sql += ',c.S10'
		
		if isnull(datalength(@S11),0) >0
			set @sql += ',c.S11'
		
		if isnull(datalength(@S12),0) >0
			set @sql += ',c.S12'
		
		set @minId = @minId +1
	end

set @sql += ' from #A a'
set @sql += ' inner join #B b on a.SaleId = b.SaleID'
set @sql += ' inner join #C c on b.SaleIDNum = c.SaleIDNum'

-- select @sql
exec sp_executesql @sql
go
输出结果：
morliz子轩 2021-04-10
  • 打赏
  • 举报
 回复
是基础数据做的有问题，已经改了，另外多数量拆分行缼也完善进去了。 

/*
if object_id('tempdb..#A') is not null
drop table #A
go
create table #A(
	SaleId varchar(10),
	CodeId int,
	BillDate date
)

insert into #A
select 'CMK0101201',172101,'2020-04-05'

-- select * from #a

if object_id('tempdb..#B') is not null
drop table #B
go
create table #B(
	SaleIDNum varchar(15),
	SaleId varchar(10),
	GoodsNo bigint,
	Quantity int,
	Price decimal(10,2)
)
insert into #B
select 'CMK01012010001','CMK0101201',621536472210,2,325 union all
select 'CMK01012010002','CMK0101201',821236475211,1,75

-- select * from #B

if object_id('tempdb..#C') is not null
drop table #C
go
create table #C(
	id int identity(1,1) not null,
	SaleIDNum varchar(15),
	Color int,
	S1 int,
	S2 int,
	S3 int,
	S4 int,
	S5 int,
	S6 int,
	S7 int,
	S8 int,
	S9 int,
	S10 int,
	S11 int,
	S12 int
)

insert into #C(SaleIDNum,Color,S1,S3,S4)
select 'CMK01012010001',411,1,1,null union all
select 'CMK01012010002',366,null,null,1

-- select * from #C as c
*/

--查询体
declare @minId int,
		@maxId int,
		
		@SaleIDNum varchar(15),
		@Color int,
		@S1 int,
		@S2 int,
		@S3 int,
		@S4 int,
		@S5 int,
		@S6 int,
		@S7 int,
		@S8 int,
		@S9 int,
		@S10 int,
		@S11 int,
		@S12 int

select @minId = min(id),@maxId = max(id) from #C
-- select @minId,@maxId
declare @dynameicField varchar(100) ='',		--动态字段[列名]
		@sql nvarchar(max) =''

	--动态字段生成
	while(@minId is not null and @minId <=@maxId)
	begin
		select @SaleIDNum =@SaleIDNum
			,@Color =Color
			,@S1 =S1
			,@S2 =S2
			,@S3 =S3
			,@S4 =S4
			,@S5 =S5
			,@S6 =S7
			,@S7 =S7
			,@S8 =S8
			,@S9 =S9
			,@S10 =S10
			,@S11 =S11
			,@S12 =S12
		from #C where id = @minId
		-- select @SaleIDNum,@Color,@S1
	
		if isnull(datalength(@S1),0) >0
			set @dynameicField = ',S1'
			
		if isnull(datalength(@S2),0) >0
			set @dynameicField += ',S2'
		
		if isnull(datalength(@S3),0) >0
			set @dynameicField += ',S3'
		
		if isnull(datalength(@S4),0) >0
			set @dynameicField += ',S4'
		
		if isnull(datalength(@S5),0) >0
			set @dynameicField += ',S5'
		
		if isnull(datalength(@S6),0) >0
			set @dynameicField += ',S6'
		
		if isnull(datalength(@S7),0) >0
			set @dynameicField += ',S7'
		
		if isnull(datalength(@S8),0) >0
			set @dynameicField += ',S8'
		
		if isnull(datalength(@S9),0) >0
			set @dynameicField += ',S9'
		
		if isnull(datalength(@S10),0) >0
			set @dynameicField += ',S10'
		
		if isnull(datalength(@S11),0) >0
			set @dynameicField += ',S11'
		
		if isnull(datalength(@S12),0) >0
			set @dynameicField += ',S12'
		
		set @minId = @minId +1
	end
	
set @sql =N'select SaleId,CodeId,BillDate,GoodsNo,1 as Quantity,Amount,Color,[内长]'
set @sql += @dynameicField + ' from ('

set @sql += 'select a.SaleId'
set @sql += ',a.CodeId'
set @sql += ',a.BillDate'
set @sql += ',b.GoodsNo'
set @sql += ',b.Quantity'
set @sql += ',b.Price as Amount'
set @sql += ',c.Color'
set @sql += ',0 as ''内长'''
------------------------------------------------------------------------------------------------------------------------
-- select @dynameicField
set @sql += @dynameicField

set @sql += ' from #A a'
set @sql += ' inner join #B b on a.SaleId = b.SaleID'
set @sql += ' inner join #C c on b.SaleIDNum = c.SaleIDNum'

set @sql += ')a,master..spt_values s where a.Quantity>=s.number and s.type=''P'' and s.number >0'

-- select @sql
exec sp_executesql @sql

go
yuzhizhi 2021-04-10
  • 打赏
  • 举报
 回复
TO morliz子轩：谢谢你的最佳方法， 唯一不足取值为表C中的S字段中的数量，而不是表B中的数量。
yuzhizhi 2021-04-10
  • 打赏
  • 举报
 回复
To morliz子轩：你的思路让我看到最佳的方法，但唯一错误是取的数量是不对的。应该取的是表C中的S1，S2，S3，中的数量。
acen_chen 2021-04-09
  • 打赏
  • 举报
 回复
具体列的显示可以放前台控制，不必纠结sql
锟斤拷锟斤拷 2021-04-09
  • 打赏
  • 举报
 回复
你要是觉得1#的 in (S1,S2,S3,S4)要把列名一个个写出来不灵活的话，拼接动态SQL可以吗？
锟斤拷锟斤拷 2021-04-09
  • 打赏
  • 举报
 回复
引用 3 楼 yuzhizhi 的回复:
To 文盲老顾：感谢你的回复，感觉想是拼出来的，不是那么灵活。 如果数据变化大，那就更不好处理了。 To shoppo0505：如何个思路。请教！
#2的意思应该就是#1的14~20行，对你明细表C中的S1、S2....S12进行列转行，然后再进行处理
文盲老顾 2021-04-09
  • 打赏
  • 举报
 回复
引用 3 楼 yuzhizhi 的回复:
To 文盲老顾：感谢你的回复，感觉想是拼出来的，不是那么灵活。 如果数据变化大，那就更不好处理了。 To shoppo0505：如何个思路。请教！
字段是没办法的，除非你用动态拼接，如果字段没有变化，这样就足够了 另外，由于所有为null的列都不显示，你的语句本身就应该是动态拼接产生，做成一个完整的存储过程最方便 我这个指令仅仅是提供一个简单的获取需要输出的数据的方法
tnnd_007 2021-04-09
  • 打赏
  • 举报
 回复
题外话，表C设计的不怎么合理啊，表2的数量也是多余的。如果还是设计阶段，直接改了。
yuzhizhi 2021-04-08
  • 打赏
  • 举报
 回复
To 文盲老顾：感谢你的回复，感觉想是拼出来的，不是那么灵活。 如果数据变化大，那就更不好处理了。 To shoppo0505：如何个思路。请教！
shoppo0505 2021-04-06
  • 打赏
  • 举报
 回复
如果你需要将s*的数据分开成多行，那么你的数据库表格的数据就应该分成多行
文盲老顾 2021-04-06
  • 打赏
  • 举报
 回复 
with ta as (
	select 'CMK0101201' as SaleId,172101 as CodeId,CONVERT(date,'2020-4-5') as [date]
),tb as (
	select 'CMK01012010001' as SaleIDNum,'CMK0101201' as SaleID,'621536472210' as GoodsNo,2 as Quantity,325 as Price
	union all
	select 'CMK01012010002','CMK0101201','821236475211',1,75
),tc as (
	select 'CMK01012010001' as SaleIDNum,411 as Color,'1' as S1,'' as S2,'1' as S3,'' as S4
	union all
	select 'CMK01012010002',366,'','','','1'
)
select a.*,GoodsNo,Quantity,Price,Color,S1,S2,S3,S4 
from (
	select * 
	from (
		select *
			,ROW_NUMBER() over(order by getdate()) as rid -- 为了避免列转行，同样产品合并为一行，追加一个不重复列
		from tc
		unpivot(val for col in (S1,S2,S3,S4)) p
		where val<>''
	) a
	pivot(
		max(val) for col in (S1,S2,S3,S4)
	) p
) c
left join tb b on c.SaleIDNum=b.SaleIDNum
left join ta a on b.SaleID=a.SaleId
Mysql查询很慢卡在sending data的原因及解决思路讲解
因为编写了一个Python程序，密集的操作了一个Mysql库，之前数据量不大时，没发现很慢，后来越来越慢，以为只是数据量大了的原因，但是后来慢到不能忍受了，查了半天，索引能用的都用上了，执行一次还是要3到4秒，不...
如何在shell脚本传递SQL语句
有时候运维需要写Linux的shell脚本,最近有个需求，需要对几个固定的SQL语句进行处理，一开始的思路是使用字符串转数组，但是搞了老半天始终传递不过去一句完整的SQL语句； 最后发现在Linux的shell中字符串转数组使用...
7天带你玩转Mysql数据库之SQL语句
在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。 3. 稍微复杂⼀点的SQL就不知道该如何写了。 4. 看不懂同⾏写的SQL是为什么。 5. 不知道索引的原理。 6. 不知道怎么...
php 解析sql语句,php实现解析xml并生成sql语句的方法
本文实例讲述了php实现解析xml并生成sql语句的方法。分享给大家供大家参考，具体如下：php解析xml有很多种办法，文档中有，搜索一下就一大把的。今天遇到一个需求：将某个xml中的节点属性提取出来，然后更新数据库某...
php 解析sql语句,php实现解析xml并生成sql语句步骤详解
这次给大家带来php实现解析xml并生成sql语句步骤详解，php实现解析xml并生成sql语句的注意事项有哪些，下面就是实战案例，一起来看一下。php解析xml有很多种办法，文档中有，搜索一下就一大把的。今天遇到一个需求：...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章