原帖地址:http://bbs.csdn.net/topics/390822475
排序出现非预期结果,因原帖已结,故再发一帖,希望有人帮忙一下~
USE [XXXX]
SELECT
a.product,
a.station_id,
a.test_count,
b.ng_count,
c.ng_code_count,
c.ng_code,
cast(cast(round(c.ng_code_count/cast(b.ng_count as decimal(6, 1))*100, 2) as numeric(9, 2)) as varchar(10))+'%' as 'percentage'
FROM
(
SELECT
product,
station_id,
count(1) 'test_count'
FROM
[statistics_table]
WHERE
result is not NULL
--and product = 'P_001'
--and station_id = 'S_001'
GROUP BY
product,
station_id
) a
inner join
(
SELECT
product,
station_id,
count(1) 'ng_count'
FROM
[statistics_table]
WHERE
result = 'FALSE'
GROUP BY
product,
station_id
) b
ON
a.product = b.product
and a.station_id = b.station_id
inner join
(
SELECT
product,
station_id,
ng_code,
count(1) 'ng_code_count'
FROM
[statistics_table]
WHERE
result = 'FALSE'
GROUP BY
product,
station_id,
ng_code
) c
ON
a.product = c.product
and a.station_id = c.station_id
当我给查询加上其他条件后,排序就乱掉了
WHERE
result is not NULL
--and product = 'P_001'
未加product限制前
加了限制后:
然后我想再加个累计百分比的字段,该如何写呢:
累计百分比:
如图所示,按照从上往下,累计起百分比,比如S_002的,第一个ng_code占百分50,累计为50,
第二个ng_code占百分25,累计为(50+25 = )75,最后个为(50+25+25=)100。
万分感谢~~
------解决方案--------------------
排序那个无所谓的吧 非要排序的话 ORDER BY station_id,ng_code就可以了
累计百分比这个 最好加个排序列
然后
select ,*(select sum(百分比) from tb where product=t.product and station_id=t.station_id and ng_code=t.ng_code and px<=t.px) from tb t
当然这里百分比的计算就你自己去做了
------解决方案--------------------
原来不用递归也行,像F姐学习了。。。
WITH t(id,num) AS
(
SELECT 'a',66.67 UNION ALL
SELECT 'a',33.33 UNION ALL
SELECT 'b',50.00 UNION ALL
SELECT 'b',25.00 UNION ALL
SELECT 'b',25.00 UNION ALL