SQL2005，根据一列多行的值分类转列并剔除重复值

jhonlonehou 2016-02-14 01:15:49
小菜求教：
有表结构如下，
OrderNo OrderRow StockBill# FeeName FeePrice QTY StockQTY
3564 1 4654 运费 253 87000 134990
3564 1 4654 加工费 6.8 87000 134990
3564 1 4654 装卸费 36 87000 134990
3564 2 4654 运费 253 15000 8280
3564 2 4654 加工费 6.8 15000 8280
3564 2 4654 装卸费 36 15000 8280
3579 1 4658 运费 272 40000 67780
3575 1 4659 运费 603 200000 199300
3575 1 4659 佣金 200 200000 199300
3575 1 4663 运费 603 200000 199300
3575 1 4663 运费 603 200000 199300
3575 1 4663 运费 603 200000 199300
3579 1 4664 运费 272 40000 67780
3575 1 4665 运费 603 200000 199300
3572 1 4666 运费 122 90000 96950
3566 1 4667 NULL NULL 20000 20085
3581 1 4671 运费 54 200000 199740
3581 1 4672 佣金 154 200000 199740
3580 1 4673 运费 225.85 80000 79700
3587 1 4674 运费 243.93 60000 67360
---------------------------------------------------
欲得到如下非重复行结果：
OrderNo OrderRow QTY StockQTY 佣金 运费 其它费
3564 1 87000 134990 0 253 42.8
3564 2 15000 8280 0 253 42.8
3566 1 20000 20085 0 0 0
3572 1 90000 96950 0 122 0
3575 1 200000 199300 200 3015 0
3579 1 40000 67780 0 544 0
3580 1 80000 79700 0 225.85 0
3581 1 200000 199740 154 54 0
3587 1 60000 67360 0 243.93 0

也就是说Feename列若值为“佣金”，或"运费",转为列值，若不是佣金或运费，其值合计入“其它费”，并转列。
本菜鸟搞出来总有点毛病，求大神指教，拜谢
...全文
138 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2016-02-15
  • 打赏
  • 举报
 回复
引用 3 楼 jhonlonehou 的回复:
[quote=引用 1 楼 ch21st 的回复:] 

WITH a(OrderNo,OrderRow,StockBill#,FeeName,FeePrice,QTY,StockQTY) AS (
select 3564,1,4654,N'运费',253,87000,134990 union
select 3564,1,4654,N'加工费',6.8,87000,134990 union
select 3564,1,4654,N'装卸费',36,87000,134990 union
select 3564,2,4654,N'运费',253,15000,8280 union
select 3564,2,4654,N'加工费',6.8,15000,8280 union
select 3564,2,4654,N'装卸费',36,15000,8280 union
select 3579,1,4658,N'运费',272,40000,67780 union
select 3575,1,4659,N'运费',603,200000,199300 union
select 3575,1,4659,N'佣金',200,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3579,1,4664,N'运费',272,40000,67780 union
select 3575,1,4665,N'运费',603,200000,199300 union
select 3572,1,4666,N'运费',122,90000,96950 union
select 3566,1,4667,NULL,NULL,20000,20085 union
select 3581,1,4671,N'运费',54,200000,199740 union
select 3581,1,4672,N'佣金',154,200000,199740 union
select 3580,1,4673,N'运费',225.85,80000,79700 union
select 3587,1,4674,N'运费',243.93,60000,67360
)
SELECT OrderNo,OrderRow,QTY,StockQTY
       ,SUM(CASE WHEN FeeName=N'佣金' then FeePrice ELSE 0 end )  as 佣金
       ,SUM(CASE WHEN FeeName=N'运费' then FeePrice ELSE 0 end )  as 运费
       ,SUM(CASE WHEN FeeName NOT IN (N'运费',N'佣金') then FeePrice ELSE 0 end )  as 其它费
FROM a
GROUP BY OrderNo,OrderRow,QTY,StockQTY
------------------------------- 感谢回复，不过这不是我想要的结果，不好意思上面列结果示例的时候列错了， “佣金、运费”列是不做SUM的，仅“其它费”列做总计。 这跟我的做法基本一致，数据量大的情况下，查出来结果有问题。 正确的想要的结果应该是这样： ------------------------------------ OrderNo OrderRow QTY StockQTY 佣金 运费 其它费 3564 1 87000 134990 0 253 42.8 3564 2 15000 8280 0 253 42.8 3566 1 20000 20085 0 0 0 3572 1 90000 96950 0 122 0 3575 1 200000 199300 200 603 0 3579 1 40000 67780 0 272 0 3580 1 80000 79700 0 225.85 0 3581 1 200000 199740 154 54 0 3587 1 60000 67360 0 243.93 0 [/quote] 我有点糊涂了，我贴出的那个SQL返回的是返回的就是9行数据啊？ 如果佣金和运费不合计,在一个组中出现多个佣金或者运费怎么处理了呢？如果出现多行，那么其他金额要放在哪一行上？ 原SQL查询结果： /* OrderNo OrderRow QTY StockQTY 佣金 运费 其它费 3564 1 87000 134990 0.00 253.00 42.80 3564 2 15000 8280 0.00 253.00 42.80 3566 1 20000 20085 0.00 0.00 0.00 3572 1 90000 96950 0.00 122.00 0.00 3575 1 200000 199300 200.00 1809.00 0.00 3579 1 40000 67780 0.00 544.00 0.00 3580 1 80000 79700 0.00 225.85 0.00 3581 1 200000 199740 154.00 54.00 0.00 3587 1 60000 67360 0.00 243.93 0.00 */
jhonlonehou 2016-02-14
  • 打赏
  • 举报
 回复
引用 2 楼 octwind 的回复:
if object_id('tb1') is not null drop table tb1 go create table tb1(OrderNo int, OrderRow int, StockBill# int, FeeName varchar(100), FeePrice float,QTY int,stockQTY int) insert into tb1 select 3564,1,4654,'运费',253,87000,134990 union all select 3564,1,4654,'加工费',6.8,87000,134990 union all select 3564,1,4654,'装卸费',36,87000,134990 union all select 3564,2,4654,'运费',253,15000,8280 union all select 3564,2,4654,'加工费',6.8,15000,8280 union all select 3564,2,4654,'装卸费',36,15000,8280 union all select 3579,1,4658, '运费',272,40000,67780 union all select 3575,1,4659,'运费',603,200000,199300 union all select 3575,1,4659,'佣金',200,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3579,1,4664,'运费',272,40000,67780 union all select 3575,1,4665,'运费',603,200000,199300 union all select 3572,1,4666,'运费',122,90000,96950 union all select 3566,1,4667,NULL,NULL,20000,20085 union all select 3581,1,4671,'运费',54,200000,199740 union all select 3581,1,4672,'佣金',154,200000,199740 union all select 3580,1,4673,'运费',225.85,80000,79700 union all select 3587,1,4674,'运费',243.93,60000,67360 select orderno,orderrow,max(qty) qty,max(stockqty) stockqty, sum(case FeeName when '佣金' then feeprice else 0 end) '佣金', sum(case FeeName when '运费' then feeprice else 0 end) '运费',sum(case when FeeName <> '运费' and FeeName <> '佣金' then feeprice else 0 end) '其它费' from tb1 group by orderno,orderrow order by orderno
------------------------------------- 可能本菜鸟没表达清楚，借用您上面的代码： 
WITH a(OrderNo,OrderRow,StockBill#,FeeName,FeePrice,QTY,StockQTY) AS (
select 3564,1,4654,N'运费',253,87000,134990 union
select 3564,1,4654,N'加工费',6.8,87000,134990 union
select 3564,1,4654,N'装卸费',36,87000,134990 union
select 3564,2,4654,N'运费',253,15000,8280 union
select 3564,2,4654,N'加工费',6.8,15000,8280 union
select 3564,2,4654,N'装卸费',36,15000,8280 union
select 3579,1,4658,N'运费',272,40000,67780 union
select 3575,1,4659,N'运费',603,200000,199300 union
select 3575,1,4659,N'佣金',200,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3579,1,4664,N'运费',272,40000,67780 union
select 3575,1,4665,N'运费',603,200000,199300 union
select 3572,1,4666,N'运费',122,90000,96950 union
select 3566,1,4667,NULL,NULL,20000,20085 union
select 3581,1,4671,N'运费',54,200000,199740 union
select 3581,1,4672,N'佣金',154,200000,199740 union
select 3580,1,4673,N'运费',225.85,80000,79700 union
select 3587,1,4674,N'运费',243.93,60000,67360
)
SELECT OrderNo,OrderRow,QTY,StockQTY
       ,(CASE WHEN FeeName=N'佣金' then FeePrice ELSE 0 end )  as 佣金
       ,(CASE WHEN FeeName=N'运费' then FeePrice ELSE 0 end )  as 运费
       ,SUM(CASE WHEN FeeName NOT IN (N'运费',N'佣金') then FeePrice ELSE 0 end )  as 其它费
FROM a
GROUP BY OrderNo,OrderRow,QTY,StockQTY,Feename,FeePrice
这样出来的结果是： OrderNo OrderRow QTY StockQTY 佣金 运费 其它费 3564 1 87000 134990 0 0 6.8 3564 1 87000 134990 0 253 0 3564 1 87000 134990 0 0 36 3564 2 15000 8280 0 0 6.8 3564 2 15000 8280 0 253 0 3564 2 15000 8280 0 0 36 3566 1 20000 20085 0 0 0 3572 1 90000 96950 0 122 0 3575 1 200000 199300 200 0 0 3575 1 200000 199300 0 603 0 3579 1 40000 67780 0 272 0 3580 1 80000 79700 0 225.85 0 3581 1 200000 199740 154 0 0 3581 1 200000 199740 0 54 0 3587 1 60000 67360 0 243.93 0 而我想要的结果是将相同OrderNo+OrderRow的结果合并 如： OrderNo OrderRow QTY StockQTY 佣金 运费 其它费 3564 1 87000 134990 0 253 42.8 3564 2 15000 8280 0 253 42.8 3572 1 90000 96950 0 122 0
octwind 2016-02-14
  • 打赏
  • 举报
 回复
sum改成max试试
jhonlonehou 2016-02-14
  • 打赏
  • 举报
 回复
引用 1 楼 ch21st 的回复:

WITH a(OrderNo,OrderRow,StockBill#,FeeName,FeePrice,QTY,StockQTY) AS (
select 3564,1,4654,N'运费',253,87000,134990 union
select 3564,1,4654,N'加工费',6.8,87000,134990 union
select 3564,1,4654,N'装卸费',36,87000,134990 union
select 3564,2,4654,N'运费',253,15000,8280 union
select 3564,2,4654,N'加工费',6.8,15000,8280 union
select 3564,2,4654,N'装卸费',36,15000,8280 union
select 3579,1,4658,N'运费',272,40000,67780 union
select 3575,1,4659,N'运费',603,200000,199300 union
select 3575,1,4659,N'佣金',200,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3579,1,4664,N'运费',272,40000,67780 union
select 3575,1,4665,N'运费',603,200000,199300 union
select 3572,1,4666,N'运费',122,90000,96950 union
select 3566,1,4667,NULL,NULL,20000,20085 union
select 3581,1,4671,N'运费',54,200000,199740 union
select 3581,1,4672,N'佣金',154,200000,199740 union
select 3580,1,4673,N'运费',225.85,80000,79700 union
select 3587,1,4674,N'运费',243.93,60000,67360
)
SELECT OrderNo,OrderRow,QTY,StockQTY
       ,SUM(CASE WHEN FeeName=N'佣金' then FeePrice ELSE 0 end )  as 佣金
       ,SUM(CASE WHEN FeeName=N'运费' then FeePrice ELSE 0 end )  as 运费
       ,SUM(CASE WHEN FeeName NOT IN (N'运费',N'佣金') then FeePrice ELSE 0 end )  as 其它费
FROM a
GROUP BY OrderNo,OrderRow,QTY,StockQTY
------------------------------- 感谢回复，不过这不是我想要的结果，不好意思上面列结果示例的时候列错了， “佣金、运费”列是不做SUM的，仅“其它费”列做总计。 这跟我的做法基本一致，数据量大的情况下，查出来结果有问题。 正确的想要的结果应该是这样： ------------------------------------ OrderNo OrderRow QTY StockQTY 佣金 运费 其它费 3564 1 87000 134990 0 253 42.8 3564 2 15000 8280 0 253 42.8 3566 1 20000 20085 0 0 0 3572 1 90000 96950 0 122 0 3575 1 200000 199300 200 603 0 3579 1 40000 67780 0 272 0 3580 1 80000 79700 0 225.85 0 3581 1 200000 199740 154 54 0 3587 1 60000 67360 0 243.93 0
octwind 2016-02-14
  • 打赏
  • 举报
 回复
if object_id('tb1') is not null drop table tb1 go create table tb1(OrderNo int, OrderRow int, StockBill# int, FeeName varchar(100), FeePrice float,QTY int,stockQTY int) insert into tb1 select 3564,1,4654,'运费',253,87000,134990 union all select 3564,1,4654,'加工费',6.8,87000,134990 union all select 3564,1,4654,'装卸费',36,87000,134990 union all select 3564,2,4654,'运费',253,15000,8280 union all select 3564,2,4654,'加工费',6.8,15000,8280 union all select 3564,2,4654,'装卸费',36,15000,8280 union all select 3579,1,4658, '运费',272,40000,67780 union all select 3575,1,4659,'运费',603,200000,199300 union all select 3575,1,4659,'佣金',200,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3575,1,4663,'运费',603,200000,199300 union all select 3579,1,4664,'运费',272,40000,67780 union all select 3575,1,4665,'运费',603,200000,199300 union all select 3572,1,4666,'运费',122,90000,96950 union all select 3566,1,4667,NULL,NULL,20000,20085 union all select 3581,1,4671,'运费',54,200000,199740 union all select 3581,1,4672,'佣金',154,200000,199740 union all select 3580,1,4673,'运费',225.85,80000,79700 union all select 3587,1,4674,'运费',243.93,60000,67360 select orderno,orderrow,max(qty) qty,max(stockqty) stockqty, sum(case FeeName when '佣金' then feeprice else 0 end) '佣金', sum(case FeeName when '运费' then feeprice else 0 end) '运费',sum(case when FeeName <> '运费' and FeeName <> '佣金' then feeprice else 0 end) '其它费' from tb1 group by orderno,orderrow order by orderno
道素 2016-02-14
  • 打赏
  • 举报
 回复 

WITH a(OrderNo,OrderRow,StockBill#,FeeName,FeePrice,QTY,StockQTY) AS (
select 3564,1,4654,N'运费',253,87000,134990 union
select 3564,1,4654,N'加工费',6.8,87000,134990 union
select 3564,1,4654,N'装卸费',36,87000,134990 union
select 3564,2,4654,N'运费',253,15000,8280 union
select 3564,2,4654,N'加工费',6.8,15000,8280 union
select 3564,2,4654,N'装卸费',36,15000,8280 union
select 3579,1,4658,N'运费',272,40000,67780 union
select 3575,1,4659,N'运费',603,200000,199300 union
select 3575,1,4659,N'佣金',200,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3575,1,4663,N'运费',603,200000,199300 union
select 3579,1,4664,N'运费',272,40000,67780 union
select 3575,1,4665,N'运费',603,200000,199300 union
select 3572,1,4666,N'运费',122,90000,96950 union
select 3566,1,4667,NULL,NULL,20000,20085 union
select 3581,1,4671,N'运费',54,200000,199740 union
select 3581,1,4672,N'佣金',154,200000,199740 union
select 3580,1,4673,N'运费',225.85,80000,79700 union
select 3587,1,4674,N'运费',243.93,60000,67360
)
SELECT OrderNo,OrderRow,QTY,StockQTY
       ,SUM(CASE WHEN FeeName=N'佣金' then FeePrice ELSE 0 end )  as 佣金
       ,SUM(CASE WHEN FeeName=N'运费' then FeePrice ELSE 0 end )  as 运费
       ,SUM(CASE WHEN FeeName NOT IN (N'运费',N'佣金') then FeePrice ELSE 0 end )  as 其它费
FROM a
GROUP BY OrderNo,OrderRow,QTY,StockQTY
SQL查询去除重复数据（多行结果取第一行）
在平时的开发中，经常需要在多行数据里面剔除重复数据，如分组之前 的数据：而想要的结果：通常使用的方法如下：SQL： SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn, test1.* ...
【SQL实现合并/拆分数据】单行变多行&多行变单行
多行表名：table_1 sport name 篮球 LiLei 篮球 HanMeiMei 篮球 XiaoMing 足球 Jane 足球 ZhangSan 足球 LiLei 单行表名：table_2 sport names 篮球 ...
SQL“键列信息不足或不正确，更新影响到多行
SQL“键列信息不足或不正确，更新影响到多行 用企业管理器打开表，想删除表中的某一行时，会出现“键列信息不足或不正确，更新影响到多行。”  出现“键列信息不足...”的错误，是因为在该表中存在重复的记录，...
SQL“键列信息不足或不正确，更新影响到多行。”
用企业管理器打开表，想删除表中的某一行时，会出现“键列信息不足或不正确，更新影响到多行。”  出现“键列信息不足...”的错误的确如楼上所说，是因为在该表中存在重复的记录，因此在企业管理器中试图删除时会...
去除列中有重复的行，保留一行
1.UserID 并非主键，UserID有重复值 SELECT b.*FROM(SELECT*,rid=ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY UserID) FROM EmployeeAllInfo) bWHERE rid =1 转载于:...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章