本文介绍了如何使用sql server解开表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 WITH T AS ( SELECT Sno,Water,Milk FROM ( VALUES ( 1 , 50 , 100 ),( 2 , 22 , 120 ),( 3 , 11 , 142 ))t(Sno,水,牛奶)) SELECT Sno = ROW_NUMBER() OVER ( ORDER BY SUM(Upvt.Qnty)), upvt。 Type , Qnty = SUM(Upvt.Qnty) FROM T UNPIVOT (数量 FOR 类型 IN ([水], [牛奶]))upvt GROUP BY upvt。类型 ORDER BY Qnty; 它的工作正常http://sqlfiddle.com/#!3/d41d8/36356 [ ^ ] 但在我的sql中没有激发..它显示关键字'VALUES'附近的语法错误。解决方案 SQL Server 2008不支持这种声明。 以上陈述不属实。请看Andrius对我的回答的评论。 谢谢你,Andrius;) 你可以实现这个目标有两种方式: 1)使用临时表(变量类型表) DECLARE @ products TABLE (Sno INT ,Water INT ,Milk INT ) INSERT INTO @ products VALUES ( 1 , 50 , 100 ),( 2 , 22 , 120 ),( 3 , 11 , 142 ) SELECT ROW_NUMBER() OVER ( ORDER BY SUM(Qnty)) AS RowNo ,产品,SUM(Qnty) AS Qnty FROM ( SELECT 产品,Qnty FROM ( SELECT * FROM @ products ) AS pvt UNPIVOT(Qnty FOR 产品 IN ( [Water],[Milk])) AS unpvt ) AS T GROUP BY 产品 或 2)使用 UNION ALL [ ^ ]声明 ; WITH T AS ( SELECT Sno,Water,Milk FROM ( SELECT 1 AS Sno, 50 AS 水, 100 AS 牛奶 UNION 所有 SELECT 2 , 22 , 120 UNION ALL SELECT 3 , 11 , 142 )t(Sno,水,牛奶)) SELECT Sno = ROW_NUMBER() OVER ( ORDER BY SUM(Upvt.Qnty)), upvt。 Type , Qnty = SUM(Upvt.Qnty) FROM T UNPIVOT (Qnty FOR 类型 IN ([水],[牛奶]))upvt GROUP BY upvt。类型 订购 BY Qnty; 请参阅MSDN文档。 WITH T AS( SELECT Sno, Water, Milk FROM (VALUES (1, 50, 100), (2, 22, 120), (3, 11, 142)) t (Sno, Water, Milk))SELECT Sno = ROW_NUMBER() OVER(ORDER BY SUM(Upvt.Qnty)), upvt.Type, Qnty = SUM(Upvt.Qnty)FROM T UNPIVOT ( Qnty FOR Type IN ([Water], [Milk]) ) upvtGROUP BY upvt.TypeORDER BY Qnty;It works fine in http://sqlfiddle.com/#!3/d41d8/36356[^]But in my sql is not excuting..it shows an "Incorrect syntax near the keyword 'VALUES'. 解决方案 SQL Server 2008 does not support this kind of statement.[EDIT]Above statement is not true. Please see Andrius's comment to my answer.Thank you, Andrius ;)You can achieve that in 2 ways:1) using temporary table (variable type table)DECLARE @products TABLE(Sno INT, Water INT, Milk INT)INSERT INTO @productsVALUES (1, 50, 100), (2, 22, 120), (3, 11, 142)SELECT ROW_NUMBER() OVER(ORDER BY SUM(Qnty)) AS RowNo, Product, SUM(Qnty) AS QntyFROM ( SELECT Product, Qnty FROM ( SELECT * FROM @products ) AS pvt UNPIVOT (Qnty FOR Product IN ([Water],[Milk])) AS unpvt) AS TGROUP BY Productor2) using UNION ALL[^] statement;WITH T AS(SELECT Sno, Water, Milk FROM ( SELECT 1 AS Sno, 50 AS Water, 100 AS Milk UNION ALL SELECT 2, 22, 120 UNION ALL SELECT 3, 11, 142 ) t (Sno, Water, Milk))SELECT Sno = ROW_NUMBER() OVER(ORDER BY SUM(Upvt.Qnty)), upvt.Type, Qnty = SUM(Upvt.Qnty)FROM T UNPIVOT ( Qnty FOR Type IN ([Water], [Milk]) ) upvtGROUP BY upvt.TypeORDER BY Qnty;Please,refer MSDN documentation. 这篇关于如何使用sql server解开表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
11-03 06:05