Sql Server：多行合并成一行，并做分组统计的两个方法
Sql Server：多行合并成一行，并做分组统计的两个方法
代码如下:--创建 test 表 ，插入数据CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)INSERT test SELECT '001', 'aa',1UNION ALL SELECT '001', 'bb',2UNION ALL SELECT '002', 'aaa',4UNION ALL SELECT '002', 'bbb',5UNION ALL SELECT '002', 'ccc',3;&--方法一--将多行合并成一行，并做分组统计SELECT code,&&&&&& [values] =&&&&&& stuff(b.[values].value('/R[1]', 'nvarchar(max)'),,,&&&&&&&&&&&& ''),[count]& FROM (SELECT& code,sum([count]) as [count]&&&&&&&&& FROM test&&&&&&&& GROUP BY code) a&CROSS apply (&&&&&&& SELECT [values] =(&&&&&&&&&&& SELECT N',' + [values] FROM test&&&&&&&&&&&&& WHERE code = a.code&&&&&&&&&&&&&&&&&&&&&&&& FOR XML PATH(''), ROOT('R'), TYPE&&&&&&& ))&--方法二---SQL2005中的新解法&& 使用XMLSELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]FROM test t1GROUP BY code&--查询结果--001&&& aa,bb&&& 3--002&&& aaa,bbb,ccc&&& 12&drop table test
Copyright & 2016 phpStudy本文讲的是一条sql实现字符串分组合并，
declare @t table (Id int, Value char(5))
insert into @t
select '1','09:01' union
select '1','1
declare @t table (Id int, Value char(5))
insert into @t
select '1','09:01' union
select '1','12:05' union
select '2','08:04' union
select '2','17:09' union
select '3','99888'
select * from @t
select id,stuff((select ','+rtrim(value)
where id=a.id for xml path('')
),1,1,'') as [values]
from (select distinct id from @t) a
以上是云栖社区小编为您精心准备的的内容，在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索字符串
sql分组合并字符串、mysql分组合并字符串、sql分组字符串累加、sql 分组拼接字符串、sql 分组 字符串，以便于您获取更多的相关知识。
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效率，降低 IT 成本...
RDS是一种稳定可靠、可弹性伸缩的在线数据库服务。支持MySQL、SQL Server、PostgreSQL、高...
云栖社区()为您免费提供相关信息，包括
的信息，还有sql分组合并字符串、mysql分组合并字符串、sql分组字符串累加、sql 分组拼接字符串、sql 分组 字符串等
，所有相关内容均不代表云栖社区的意见！新手园地& & & 硬件问题Linux系统管理Linux网络问题Linux环境编程Linux桌面系统国产LinuxBSD& & & BSD文档中心AIX& & & 新手入门& & & AIX文档中心& & & 资源下载& & & Power高级应用& & & IBM存储AS400Solaris& & & Solaris文档中心HP-UX& & & HP文档中心SCO UNIX& & & SCO文档中心互操作专区IRIXTru64 UNIXMac OS X门户网站运维集群和高可用服务器应用监控和防护虚拟化技术架构设计行业应用和管理服务器及硬件技术& & & 服务器资源下载云计算& & & 云计算文档中心& & & 云计算业界& & & 云计算资源下载存储备份& & & 存储文档中心& & & 存储业界& & & 存储资源下载& & & Symantec技术交流区安全技术网络技术& & & 网络技术文档中心C/C++& & & GUI编程& & & Functional编程内核源码& & & 内核问题移动开发& & & 移动开发技术资料ShellPerlJava& & & Java文档中心PHP& & & php文档中心Python& & & Python文档中心RubyCPU与编译器嵌入式开发驱动开发Web开发VoIP开发技术MySQL& & & MySQL文档中心SybaseOraclePostgreSQLDB2Informix数据仓库与数据挖掘NoSQL技术IT业界新闻与评论IT职业生涯& & & 猎头招聘IT图书与评论& & & CU技术图书大系& & & Linux书友会二手交易下载共享Linux文档专区IT培训与认证& & & 培训交流& & & 认证培训清茶斋投资理财运动地带快乐数码摄影& & & 摄影器材& & & 摄影比赛专区IT爱车族旅游天下站务交流版主会议室博客SNS站务交流区CU活动专区& & & Power活动专区& & & 拍卖交流区频道交流区
富足长乐, 积分 6686, 距离下一级还需 1314 积分
论坛徽章:3
30可用积分
之前曾经请教过如何分组查询，经过模仿，学会了。
但是有个问题是，我的需求必须要查询出年、月、日三个方向的数据。
目前有三个SQL语句，我想把它合并，方便程序使用。
请教各位大拿，如何合并呢？
这个是年的：select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%2013%') t group by method_复制代码这个是月的：select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%2013-11%') t group by method_复制代码这个是日的：select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%%') t group by method_复制代码查询结果格式如下：+-------------+--------------+
| method_name | count_method |
+-------------+--------------+
|& && && &&&1 |& && && & 148 |
|& && && &&&3 |& && && &&&22 |
+-------------+--------------+
2 rows in set, 1 warning (0.00 sec)
复制代码期望合并后的结果是：+-------------+-------------+-------------+-----------+
| method_name | count_year&&| count_month | count_day |
+-------------+-------------+-------------+-----------+
|& && && &&&1 |& && && &148 |& && && &100 |& && & 300 |
|& && && &&&3 |& && && & 22 |& && && &400 |& && & 200 |
+-------------+-------------+-------------+-----------+
2 rows in set, 1 warning (0.00 sec)
复制代码当然，如果这种合并结果不行的话，尽可能把它合并就行。。。- - #
还有一个小需求，method_name必须要有 1,2,3 如果只有1,3的话，那么2默认设置为0.。。（这个需求如果难做的话，就放弃。）
由于你没有表的源结构，看似复杂了点，试下，我没法测试
&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp|&&nbsp&&nbsp|&&nbsp
家境小康, 积分 1471, 距离下一级还需 529 积分
论坛徽章:2
由于你没有表的源结构，看似复杂了点，试下，我没法测试select a.method_name,a.count_method count_year ,b.count_method count_month ,c.count_method count_day
from
(select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%2013%') t group by method_name) a,
(select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%2013-11%') t group by method_name) b,
(select method_name,count(method_name) count_method from (select (select method_level from w3a_log_method where id=w3a_log_monitor_attack.method_name) method_name from w3a_log_monitor_attack where attack_user='192.168.31.135' and attack_date like '%%') t group by method_name) c
where a.method_name=b.method_name and a.method_name=c.method_name复制代码
家境小康, 积分 1471, 距离下一级还需 529 积分
论坛徽章:2
把你现有的数据源格式贴出来，最好导成sql文件放到附件里面。
富足长乐, 积分 6686, 距离下一级还需 1314 积分
论坛徽章:3
tiankafeiwu
& & .....我没法导出..在服务机上。。。
富足长乐, 积分 6686, 距离下一级还需 1314 积分
论坛徽章:3
tiankafeiwu
& & 谢谢哈。。
& & 我直接用程序实现了。。就不管了。。
& & 分给你把。MARK一下，以后不懂就找你。。嘿嘿..{:3_193:}其他回答(1)
你的意思是根据c.equipment_name和A.KEYSPARENAME两个字段 去重？
&&&您需要以后才能回答，未注册用户请先。温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
一个爱笑,却总笑得傻傻的家伙
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
职位表有一个ISPUBLIC字段，该字段用于表明职位是否为共有职位（ISPUBLIC=1表示该职位为共有职位，ISPUBLIC=0表示该职位为非共有职位）。如果一个职位被定岗到一个组织，那么，需要更新该职位的ISPUBLIC=0；如果一个职位被定岗到多个组织，那么，需要更新该职位的ISPUBLIC=1。一个职位的职位ID在组织定岗表中可能出现一条记录或者多条记录，所以我们要先查出每个职位ID在组织定岗表中出现的次数。SELECT
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODE.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODE.JOBCODEID或者SELECT
CPCJOBCODETODEPT.JOBCODEID ,
COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODETODEPT.JOBCODEID或者 SELECT
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODE.JOBCODEID这里就涉及到。第三种写法便于理解“职位表中的职位ID在组织定岗表中出现的次数”，其实写了这三种，只是想让自己明白，SELECT的字段必须要是分组（GROUP BY）的依据。当然，SELECT的字段可以有多个，这时，分组（GROUP BY）的依据也要有多个。对分组完成后的数据集进行再次筛选（Having）&上面说到，如果一个职位被定岗到一个组织，那么，需要更新该职位的ISPUBLIC=0；如果一个职位被定岗到多个组织，那么，需要更新该职位的ISPUBLIC=1。也就是，当 COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU=1时，更新CPCJOBCODE.ISPUBLIC=0；
当 COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU&1时，更新CPCJOBCODE.ISPUBLIC=1；所以，最终更新ISPUBLIC的SQL如下：UPDATE
CPCJOBCODESET
ISPUBLIC = 0FROM
CPCJOBCODE
JOIN ( SELECT
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODE.JOBCODEID HAVING COUNT(CPCJOBCODE.JOBCODEID)=1
) temp ON CPCJOBCODE.JOBCODEID = temp.JOBCODEIDUPDATE
CPCJOBCODESET
ISPUBLIC = 1FROM
CPCJOBCODE
JOIN ( SELECT
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODE.JOBCODEID HAVING COUNT(CPCJOBCODE.JOBCODEID)&1
) temp ON CPCJOBCODE.JOBCODEID = temp.JOBCODEID当然，我们也可以把这里的HAVING子句改写成WHERE子句：UPDATE
CPCJOBCODESET
ISPUBLIC = 1FROM
CPCJOBCODE
JOIN ( SELECT
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODETODEPT.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED = 0
GROUP BY CPCJOBCODE.JOBCODEID
) temp ON CPCJOBCODE.JOBCODEID = temp.JOBCODEIDWHERE
temp.GESHU & 1&使用聚合函数进行分组后，可以再次使用放在Group By子句后的Having子句对分组后的数据进行再次的过滤。Having子句在某些方面很像Where子句。使用having子句非常简单，但需要注意的是，having子句后面不能跟在select语句中出现的别名，而必须将Select语句内的表达式再写一遍。就上面的例子而言，用Having子句的时候，我们只能写成HAVING COUNT(CPCJOBCODE.JOBCODEID)&1而不能写成HAVING GESHU&1用WHERE子句的时候，我们只能写成WHERE
temp.GESHU & 1而不能写成WHERE COUNT(CPCJOBCODE.JOBCODEID)&1这就是HAVING子句强大的地方：HAVING子句可以使用聚合函数作为表达式，而这在WHERE子句中是不允许的。到这里，把上面的更新语句放到SQL里执行，Perfect！此时，我们的批量更新操作算完成了吧？NO NO NO，因为当你把上面的更新语句放到Oracle里执行的时候，Oh，my god！竟然报错了&啊哦，我不知道要怎么解决了。于是请教别的同事，同事说，批量更新是你这么写的吗？我说，是啊，至少它在SQL里完全没问题啊，同事无语......于是给我发了一个SQL，说，你仿照这个SQL重新写一下可以在Oracle环境里执行的批量更新SQL吧。同事给的SQL：MERGE INTO PAY_TW_INSURECALRESULT_H
USING PSNACCOUNT
(PAY_TW_INSURECALRESULT_H.PERSONID = PSNACCOUNT.PERSONID)
WHEN MATCHED
PAY_TW_INSURECALRESULT_H.IDNO = PSNACCOUNT.CERTIFICATENUMBER;好吧，我又华丽丽的鄙陋了一把，囧。。。在此之前，完全不知道有MERGE的存在，于是啃呲啃呲百度去了。。。参考：&&&好了，大概了解Merge：在Oracle中，到Oracle 9i开始引入，可以同时进行INSERT和UPDATE。于是，我的仿照行动开始，仿照结果如下：MERGE INTO CPCJOBCODE
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODE.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED=0
CPCJOBCODE.JOBCODEID
( CPCJOBCODE.JOBCODEID = temp.JOBCODEID
AND temp.GESHU = 1
WHEN MATCHED
CPCJOBCODE.ISPUBLIC = 0 ;
MERGE INTO CPCJOBCODE
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODE.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED=0
CPCJOBCODE.JOBCODEID
( CPCJOBCODE.JOBCODEID = temp.JOBCODEID
AND temp.GESHU & 1
WHEN MATCHED
CPCJOBCODE.ISPUBLIC = 1 ; 放到SQL Navigator里执行，OK！可是，事情还没结束，我把改造后的SQL放到SQL Server 2008中执行，咦？同样也可以执行呢！于是继续百度：于是对Merge的了解又多了一点：在SQL中，直到SQL Server 2008才被引入，可以同时写INSERT，UPDATE，DELETE子句。可是，事情还是没有结束，我一个不小心，看到了下面这两段话：在Oracle 10g中MERGE有如下一些改进：&1、UPDATE或INSERT子句是可选的&2、UPDATE和INSERT子句可以加WHERE子句&3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表&4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行&&在SQL Server中，仅仅是MATCHED这种限制条件往往不能满足实际需求，我们可以在MATCHED语句的基础上加上AND附加额外的限制条件。Oracle：MERGE INTO CPCJOBCODE
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODE.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED=0
CPCJOBCODE.JOBCODEID
( CPCJOBCODE.JOBCODEID = temp.JOBCODEID
WHEN MATCHED
SET CPCJOBCODE.ISPUBLIC = 1 WHERE temp.GESHU & 1 ;SQL：MERGE INTO CPCJOBCODE
CPCJOBCODE.JOBCODEID ,
COUNT(CPCJOBCODE.JOBCODEID) GESHU
CPCJOBCODETODEPT
INNER JOIN CPCJOBCODE ON CPCJOBCODE.JOBCODEID = CPCJOBCODETODEPT.JOBCODEID
AND CPCJOBCODE.ISDELETED = 0
AND CPCJOBCODE.ISEFFECT = 1
AND CPCJOBCODETODEPT.ISDELETED=0
CPCJOBCODE.JOBCODEID
( CPCJOBCODE.JOBCODEID = temp.JOBCODEID
WHEN MATCHED AND temp.GESHU & 1
SET CPCJOBCODE.ISPUBLIC = 1注：Merge关键字的一些限制&&& 使用Merge关键字只能更新一个表&&& 源表中不能有重复的记录&好啦，到此可以小结一下，准备说再见咯，(*^__^*) 嘻嘻……1.用到聚合函数时，通常都会要对数据进行分组（GROUP BY），分组之后还想加过滤条件的话，可以用HAVING子句。常见的简单的聚合函数有：Avg,Sum,Max,Min这些函数的参数职能是数字类型，在SQL中，数字类型具体包括:tinyint,smallint,int,bigint,decimal,money,smallmoney,float,real.还有一个聚合函数就是COUNT()啦。2.如果要批量更新数据的话，可以考虑用Merge哦
阅读(2611)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'分组查询（GROUP BY）和批量更新（Merge）',
blogAbstract:'职位表CPCJOBCODE组织定岗表CPCJOBCODETODEPT职位表有一个ISPUBLIC字段，该字段用于表明职位是否为共有职位（ISPUBLIC=1表示该职位为共有职位，ISPUBLIC=0表示该职位为非共有职位）。如果一个职位被定岗到一个组织，那么，需要更新该职位的ISPUBLIC=0；如果一个职位被定岗到多个组织，那么，需要更新该职位的ISPUBLIC=1。一个职位的职位ID在组织定岗表中可能出现一条记录或者多条记录，所以我们要先查出每个职位ID在组织定岗表中出现的次数。SELECT
CPCJOBCODE.JOBCODEID',
blogTag:'批量更新,sql,oracle,group,merge',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:2,
publishTime:9,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:true,
hostIntro:'一个爱笑,却总笑得傻傻的家伙',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}