T-SQL计数问题
我需要总结下表
ID A B C D E F G
----------------------------------
1-100 1 2 1 1 1 1 1
1-201 1 2 1 2 2 2 2
1-322 1 1 1 1 2 2 1
2-155 1 1 2 1 1 2 2
2-167 2 1 2 1 2 1 2
2-389 2 2 1 2 1 1 2
2-423 1 2 2 2 1 1 1
3-10 2 1 1 1 2 2 2
3-222 1 1 1 1 2 2 1
3-397 2 1 1 2 2 1 1
在上表中,值 1 编码为 S,而 2 编码为 R。此外,ID 是 XX、YY 或 XX 代码,其中 - 之前的数字代表 XX 、YY 或 XX。
我想要的摘要是这样的
XX YY ZZ
------------------------------------------
A S 3 100% 2 50% 1 33%
R 0 0% 2 50% 2 66%
B S 2 66% 2 50% 3 100%
R 1 33% 2 50% 0 0%
C S 3 100% 3 75% 3 100%
R 0 0% 1 25% 0 0%
D S 2 66% 2 50% 2 66%
R 1 33% 2 50% 1 33%
E S 1 33% 3 75% 0 0%
R 2 66% 1 25% 3 100%
F S 1 33% 3 75% 2 66%
R 2 66% 1 25% 1 33%
G S 2 66% 1 25% 1 33%
R 1 33% 3 75% 2 66%
所以我需要旋转表格,计算 1/2 并创建百分比。
这让我很困惑,并且我在如何做到这一点上陷入了一些死胡同(更不用说如何优雅地做到这一点)
提前致谢!
在马丁的帮助下,我离目标如此之近。我的数据当然比我给出的例子有点愚蠢,所以我仍然遇到困难。我已经谴责了数据,并放入了我想要的正确编码 - 是的,编码确实很愚蠢,我无法控制它们:)
我已经扩展了 Martins SQL 来链接到我的数据,但还有两个问题。事物列中行的顺序并不完全是我想要的。
当我尝试以下代码时,我得到一个“必须声明标量变量@order” - 它不喜欢加入到名为 myOrder 的临时表。
DECLARE @myOrder TABLE (rug varchar(3), rugOrder int)
INSERT @myOrder
SELECT 'INH', 1 UNION ALL
SELECT 'RIF', 2 UNION ALL
SELECT 'KM', 3 UNION ALL
SELECT 'AK', 4 UNION ALL
SELECT 'CM', 5 UNION ALL
SELECT 'MOX', 6 UNION ALL
SELECT 'OFX', 7;
WITH YourData(ID, INH, RIF, KM, AK, CM, MOX, OFX) As
(SELECT Sample_ID, INH, RIF, KM, AK, CM, MOX, OFX
FROM dbo.[GCT_Rug] WHERE Sample_ID NOT LIKE '99%')
, Unpivoted AS
(
SELECT S_R_Flag,
Thing,
Site =
CASE
WHEN LEFT(ID,1) = 1 THEN 1
WHEN LEFT(ID,1) = 6 THEN 1
WHEN LEFT(ID,1) = 8 THEN 2
WHEN LEFT(ID,1) = 9 THEN 3 END
FROM YourData
UNPIVOT
(S_R_Flag FOR Thing IN (INH, RIF, KM, AK, CM, MOX, OFX)
)AS unpvt)
SELECT Thing
,SRFLAG =
CASE
WHEN S_R_Flag = 1 THEN 'S'
WHEN S_R_Flag = 2 THEN 'R'
END
,[1] AS IND
,round(CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0)*100,1) AS 'Ind Percent'
,[2] AS MD
,round(CAST([2] AS FLOAT) / NULLIF(SUM([2]) OVER (PARTITION BY Thing),0)*100,1) AS 'MD Percent'
,[3] AS 'SA'
,round(CAST([3] AS FLOAT) / NULLIF(SUM([3]) OVER (PARTITION BY Thing),0)*100,1) AS 'SA Percent'
FROM Unpivoted
INNER JOIN @myOrder
ON Unpivoted.Thing= @myOrder.rug
PIVOT (COUNT (Site) FOR Site IN ( [1], [2], [3])) AS pvt
ORDER BY rugOrder,
SRFLAG;
错误“必须声明标量变量@myOrder”是什么意思以及为什么我不能加入它?
再次感谢你们(尤其是马丁)太棒了!
I have the following table that I need to summarize
ID A B C D E F G
----------------------------------
1-100 1 2 1 1 1 1 1
1-201 1 2 1 2 2 2 2
1-322 1 1 1 1 2 2 1
2-155 1 1 2 1 1 2 2
2-167 2 1 2 1 2 1 2
2-389 2 2 1 2 1 1 2
2-423 1 2 2 2 1 1 1
3-10 2 1 1 1 2 2 2
3-222 1 1 1 1 2 2 1
3-397 2 1 1 2 2 1 1
In the table above, the values 1 is coded as S while 2 is coded as R. Also, the ID is a code as XX, YY or XX where the digit before the - represents XX, YY, or XX.
The summary I would like to have is this
XX YY ZZ
------------------------------------------
A S 3 100% 2 50% 1 33%
R 0 0% 2 50% 2 66%
B S 2 66% 2 50% 3 100%
R 1 33% 2 50% 0 0%
C S 3 100% 3 75% 3 100%
R 0 0% 1 25% 0 0%
D S 2 66% 2 50% 2 66%
R 1 33% 2 50% 1 33%
E S 1 33% 3 75% 0 0%
R 2 66% 1 25% 3 100%
F S 1 33% 3 75% 2 66%
R 2 66% 1 25% 1 33%
G S 2 66% 1 25% 1 33%
R 1 33% 3 75% 2 66%
So I need to rotate the table, count the 1/2 and create percentages.
This has got me quite puzzled and I have gone down a few dead ends on how to do this (let alone how to do it elegantly)
Thanks in advance!
With Martin's help I am ever so close. My data is of course a bit goofier than the example I gave so I am still having difficulties. I have censured the data as well as put the proper codings in that I want - yeah the codings are real goofy, I have no control over them :)
I have extended Martins SQL to link to my data but there are two remaining issues. The order of the rows in the Thing Column is not quite what I want.
When I try the following code, I get a "Must declare the scalar variable @order" - it does not like joining to my temp table called myOrder.
DECLARE @myOrder TABLE (rug varchar(3), rugOrder int)
INSERT @myOrder
SELECT 'INH', 1 UNION ALL
SELECT 'RIF', 2 UNION ALL
SELECT 'KM', 3 UNION ALL
SELECT 'AK', 4 UNION ALL
SELECT 'CM', 5 UNION ALL
SELECT 'MOX', 6 UNION ALL
SELECT 'OFX', 7;
WITH YourData(ID, INH, RIF, KM, AK, CM, MOX, OFX) As
(SELECT Sample_ID, INH, RIF, KM, AK, CM, MOX, OFX
FROM dbo.[GCT_Rug] WHERE Sample_ID NOT LIKE '99%')
, Unpivoted AS
(
SELECT S_R_Flag,
Thing,
Site =
CASE
WHEN LEFT(ID,1) = 1 THEN 1
WHEN LEFT(ID,1) = 6 THEN 1
WHEN LEFT(ID,1) = 8 THEN 2
WHEN LEFT(ID,1) = 9 THEN 3 END
FROM YourData
UNPIVOT
(S_R_Flag FOR Thing IN (INH, RIF, KM, AK, CM, MOX, OFX)
)AS unpvt)
SELECT Thing
,SRFLAG =
CASE
WHEN S_R_Flag = 1 THEN 'S'
WHEN S_R_Flag = 2 THEN 'R'
END
,[1] AS IND
,round(CAST([1] AS FLOAT) / NULLIF(SUM([1]) OVER (PARTITION BY Thing),0)*100,1) AS 'Ind Percent'
,[2] AS MD
,round(CAST([2] AS FLOAT) / NULLIF(SUM([2]) OVER (PARTITION BY Thing),0)*100,1) AS 'MD Percent'
,[3] AS 'SA'
,round(CAST([3] AS FLOAT) / NULLIF(SUM([3]) OVER (PARTITION BY Thing),0)*100,1) AS 'SA Percent'
FROM Unpivoted
INNER JOIN @myOrder
ON Unpivoted.Thing= @myOrder.rug
PIVOT (COUNT (Site) FOR Site IN ( [1], [2], [3])) AS pvt
ORDER BY rugOrder,
SRFLAG;
What does the error "Must declare the scalar variable @myOrder" mean and why can't I join to it ?
Thanks again you guys (especially Martin) are awesome !
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这本质上为您提供了您需要的结果,尽管我没有费心将数值映射到代码
This essentially gives you the results you need though I haven't bothered mapping the numeric values to the codes