表A:
ID color
a red
b blue表 B:
Index ID conclusion
1 a +
2 a +
3 a -
4 b +
5 b -要得到结果:
ID color +数量/总数量 +%
a red 2/3 66.7%
b blue 1/2 50%
求SQL语句
ID color
a red
b blue表 B:
Index ID conclusion
1 a +
2 a +
3 a -
4 b +
5 b -要得到结果:
ID color +数量/总数量 +%
a red 2/3 66.7%
b blue 1/2 50%
求SQL语句
A.ID,
A.color,
RTRIM(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END))+'/'+RTRIM(COUNT(*)) AS [+数量/总数量],
CAST(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END)*100./COUNT(*) AS DECIMAL(10,2)) AS [+%]
FROM tb1 AS A
JOIN tb2 AS B
ON A.ID=B.ID
GROUP BY A.ID,A.color
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID VARCHAR(1),color VARCHAR(4))
INSERT INTO @tb1
SELECT 'a','red' UNION ALL
SELECT 'b','blue'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE ([Index] INT,ID VARCHAR(1),conclusion VARCHAR(2))
INSERT INTO @tb2
SELECT 1,'a','+' UNION ALL
SELECT 2,'a','+' UNION ALL
SELECT 3,'a','-' UNION ALL
SELECT 4,'b','+' UNION ALL
SELECT 5,'b','-'--SQL查询如下:SELECT
A.ID,
A.color,
RTRIM(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END))+'/'+RTRIM(COUNT(*)) AS [+数量/总数量],
CAST(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END)*100./COUNT(*) AS DECIMAL(10,2)) AS [+%]
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.ID=B.ID
GROUP BY A.ID,A.color
/*
ID color +数量/总数量 +%
---- ----- ------------------------- ---------------------------------------
a red 2/3 66.67
b blue 1/2 50.00(2 行受影响)
*/
非常感谢,不过还有点问题:
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID VARCHAR(1),color VARCHAR(10))
INSERT INTO @tb1
SELECT 'a','red' UNION ALL
SELECT 'b','blue' UNION ALL
SELECT 'c','yellow'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE ([Index] INT,ID VARCHAR(1),conclusion VARCHAR(2))
INSERT INTO @tb2
SELECT 1,'a','+' UNION ALL
SELECT 2,'a','+' UNION ALL
SELECT 3,'a','-' UNION ALL
SELECT 4,'b','+' UNION ALL
SELECT 5,'b','-' UNION ALL
select 6,'b','-' UNION ALL
select 7,'b','-'--SQL查询如下:SELECT
A.ID,
A.color,
RTRIM(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END))+'/'+RTRIM(COUNT(*)) AS [+数量/总数量],
CAST(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END)*100./COUNT(*) AS DECIMAL(10,2)) AS [+%]
FROM @tb1 AS A
left JOIN @tb2 AS B
ON A.ID=B.ID
GROUP BY A.ID,A.colorb blue 1/4 25.00
a red 2/3 66.67
c yellow 0/1 .00此时c不对,应该是:
c yellow 0/0 0.00 应该怎样修改?
A.ID,
A.color,
RTRIM(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END))+'/'+RTRIM(COUNT(B.conclusion)) AS [+数量/总数量],
CAST(SUM(CASE WHEN B.conclusion='+' THEN 1 ELSE 0 END)*100./COUNT(*) AS DECIMAL(10,2)) AS [+%]
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.ID=B.ID
GROUP BY A.ID,A.color
b blue 1/4 25.00
c yellow 0/1 .00
但是正确的结果是:
a red 2/3 66.67
b blue 1/4 25.00
c yellow 0/0 .00
ps:怎么可以贴出来SQL代码呢? 我帖的怎么都没有颜色?