=================================版权声明=================================
版权声明:原创文章 谢绝转载
请通过右侧公告中的“联系邮箱(wlsandwho@foxmail.com)”联系我
勿用于学术性引用。
勿用于商业出版、商业印刷、商业引用以及其他商业用途。
本文不定期修正完善。
本文链接:http://www.cnblogs.com/wlsandwho/p/5039358.html
耻辱墙:http://www.cnblogs.com/wlsandwho/p/4206472.html
=======================================================================
没啥说的,鄙视那些无视版权随意抓取博文的爬虫小网站,祝你们早升极乐。
=======================================================================
直接上问题
首先,事主的提问风格我还是很喜欢的,一目了然。
但是,就实际问题来讲,是不是2条就4列?如果有100条,岂不是爆炸了?
所以,实际上事主应该是想要一个水平显示的效果,而不是一定要有很多列。
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAVQAAAA0CAIAAABac1cMAAAHFElEQVR4nO1c25WtKBAlkPmcKAzIeIzGSO6fMUwMzIentagXLzn21b0Xq9dpLAqk2BSgVvjzMvz3z7803d2cX4Ro4O52AaMQ/thWfyQY+e9uDgDcA5Af5AdeigryhxCyOX4p63ehqlpJCyA/AERJ/pCCijrkLy+1/6sSuESbvNowFzDyh3k9r61zCGFatiRjXuO2TLLiveS2TOcPBqqJCdBaLaQa8yUOeSJKdJRUWYfj3uOn67Sb11pF8Onh5P9nmeDsmbQxl6DDBAr51d/Zf63Mox1O+2lzq9pgNcMHJz+xyDqHeZ5JF23LFKZlS7qYIRl5qQwd1+uc9P06Z4dCyoptmTKD75AnBdnPawffPta0Nmkt4CT/wU8Xn0UfZQJ6e1YXtKPLBDr52V9Gzph6aVaWQlJXqpLy8m+h/kIw8pPhss5hXj9/j5xp2bRRdcAZeUdx7Rob9AJHWVFRVv6QTHRkyldhdyXTPGsqGeFEqxRdzzWBr7AD/SbIk59dOn4zAltFCl16FBNKVmF2KrHAyH9207ZMHxdDpmpz5PzAdzvn9C+KZ4Ze3s/ozUjazVpzHfnX1eqW5LbyrUob9mgT5OaaOvSboJT8KsFK/L91SdXme/4qbQ4Y+enokj/I0BFgo0qT+fS0PuyzbCR7uOwQ/O7I0yrVsorIH59vgk/DBu/51awR5FeZL3OkEtVXU+b75Ldg9I0Cftr/4xzOteZPF505bW7nIFzjyEskM+PPpVmlCyuGfsvsuDFP/jeYoK62cnSYoIj81lzAGCt5KDMtSlvVWTOO8zsLTv6Pl2H7zHTn2b7hDPN60Q7Q9922jbdlGuJzeKVaRiH5n26CxupKm9RogqIDP3rJgSrg89yStMSGeP596K3nNlPJ+f5pk+Il/OMm/VxnlM83WiRGd8GB3+faY02gC1yFDhPoL/mohLeo6AhE16tblTpiQzz/3j3TRLtsW6Z5notOyu2Rl4yt6udM3GFnN+2Z50wj4HoZs1UaHmsCetvbMl1ujQ4TKOQvJJ4voEqWLP59sUHkV8YBGyjqaRNbTyoyihWMSzpogQIWH+LnxtlvUC/4SDOfMuQrf6gJ0kZ94dilwgSlL/nsRdXaNYsoSoKguszxxfwGqJdU4PVeAIj4sAfkB16Lk/yMEkhISI9P4Y+Ib4GEhPSGBPIjIb00gfxISC9N2PMjIb00jQ3mIcuqemhZX1U/+P2zp6SIJFEO7Q0TXlnuMTd/+ed5Jhj0oF9VS9ur2KDpOX/2XyuT2iEWTy5Be+Z/FTj5EUmiESuLJHHe01kvqdW4Zfba3NNMUNADTTDU0s8jvFbFODqYh7zkTy60xjhsCmDkRySJBuyuJI0kYXzHln1Bl3958iwTlPRAAwy1GRv47/Y7rGO8Za7bJ6q8Wq5tNPkRSaIBSiSJ/MC21h7rS0ww5pteqlb7eKDnq74jX13Ssx9qqdplv1x3VPRDARj56ehCJIk60ErXOUzLqm4uD1n7c/6HmyDXAz0gavfPo5L7HkL+oDG/PMfSX1i2B4z8h3NYEUmiFoz8lGlV0UReYILKVUQpErX8G0bZJ/Xkt+YCyliWoyr0Jan8UHDyb4gk0Qrh+dXN7ca+jFX1PNoE+R5ogqtWPSupJL/j4RkcMqtUt2aE7ATRCU7+feghkkQDzD0/W62XrJgfa4Jv+HyvMcUHfjtUwqvs9QVipef/wpo/viqYBx2+2mFQL9JbPkcZPYEqm3Eea4LxG32l+piYu/RRX7TP2OWy3xFgkiUc/touQJJfGQdsoKinTYgkwcl21nZOPm6PpG1/oAkqeqAGllrD3MrAiKODeYTcwt5R7tTYCYX8APA+4Ht+JKT3JnzVh4T00gTyIyG9NIH8SEgvTdjzIyG9NF0QvbfhWF493u9sRiHY/X+nUgD4bciE7lafzMl8/xmeLB77QoN0gk9+4oEoIklUNU1/FJ+LJAHcDuU5P2VdsB/7R9tXB/GcXwpn5wJHfyc4+RFJohqG2rUikgRwO0zP75P/yKHThPTVVIkUk3OEX91VYORHJIlqXBFJArgd5ht+Km9pSbY6cAhsFWeqylcWnWDkRySJPjRGkgBuR+bdfou91r5AZXiJZPyi/2fkRySJHhC1dZEkgNvRSH4mqc4FTnFrmnBquRD8tB+RJFqRqOUf0Mk+Afl/F4o+6c2u21m+X9yaC+gMwnKuBSc/Ikk0oTOSBHA7MuSPLvPlQjjr+R0P77ThWojn/IgkUY3+SBLA7ch/z++v/KmMBZXw6vLeF7gK8iUfRJKowxWRJIDbUXTaL/9VhaWAVTCKieALW/0Dyht+iCRRg0siSQC3w3vDT1Jd9cyOx3Z+W9zWKDb4wA8AXonM672PBMgPABHkv5/8+obCP9WvkQcAAx/yvwqM/Hc35xfBHyjj6rpc+d+LL3fL/1hoviz1vdVKAAAAAElFTkSuQmCC" alt="" />
不用说我是不是瞎猜的,我就是瞎猜的。哈哈哈。
上代码。
=======================================================================
--by wls 20151211
--网络代码有风险
--复制粘贴须谨慎
USE tempdb
GO IF OBJECT_ID (N't_TestbyWLS', N'U') IS NOT NULL
DROP TABLE t_TestbyWLS;
GO CREATE TABLE t_TestbyWLS(Author NVARCHAR(9),Caption NVARCHAR(50),ReadCount INTEGER)
GO INSERT INTO t_TestbyWLS VALUES('WLS','Hello1',100)
INSERT INTO t_TestbyWLS VALUES('WLS','Hello2',100)
INSERT INTO t_TestbyWLS VALUES('thbytwo','Hello2',200)
INSERT INTO t_TestbyWLS VALUES('thbytwo','Hello1',220)
INSERT INTO t_TestbyWLS VALUES('wlsandwho','Hello1',220)
GO SELECT * FROM t_TestbyWLS
GO WITH TempCRC
AS
(
SELECT Author,caption+' '+CAST(readcount AS NVARCHAR(20)) AS CRC FROM t_TestbyWLS
)
,TempXML
AS
(
SELECT Author,(SELECT CRC+',' FROM TempCRC a WHERE a.author=b.author FOR XML PATH('') ) AS CRCXML FROM TempCRC B GROUP BY author
)
SELECT author,LEFT(crcxml,LEN(crcxml)-1) AS NewCRCXML FROM TempXML
GO
运行结果。
执行计划。
=======================================================================
千万不要吐槽我的第二个CTE,那是没办法的事情。写了这么多SQL典型例子,大家见我哪次用子查询了?真是迫不得已。
=======================================================================
(友情支持请扫描这个)
微信扫描上方二维码捐赠