SQL 2008 R2 查询统计

[Jakey_Ch]

有如下一个表：

查询规则：
1、根据station_id和ng_code统计数据
2、test_count字段为该station_id的测试总数
3、ng_count字段为该station_id且为该ng_code的数量
想要的效果如下：
prduct station_id test_count ng_count ng_code
P_001 S_001 6 2 N_001
P_001 S_001 6 1 N_002
P_001 S_002 4 2 N_001
P_001 S_002 4 1 N_002
P_001 S_002 4 1 N_003

TableName： [statistics_table]
万分感谢！

[xdashewan]

两个子查询求和，分别group by prduct station_id ng_code和prduct station_id，两个子查询做联结，条件是prduct station_id

[Jakey_Ch]

引用 1 楼 xdashewan 的回复:

两个子查询求和，分别group by prduct station_id ng_code和prduct station_id，两个子查询做联结，条件是prduct station_id

能不能写一下额？谢谢了~那个子查询是这样么？怎么联结额？

--SELECT 
--	[product],	
--	[station_id],
--	[ng_code]
--FROM
--	[statistics_table]	
--WHERE
--	[product] = 'P_001'
--	and [station_id] = 'S_001'
--	GROUP BY [product],[station_id],[ng_code]
	
SELECT 
    [product],	
	[station_id]
FROM
	[statistics_table]	
WHERE
	[product] = 'P_001'
	and [station_id] = 'S_001'
	GROUP BY [product],[station_id]

[--小F--]

select
    [product], [station_id],
    count()over(partition by station_id) as  test_count    ,
    count(1) as ng_count ,ng_code
from
    [statistics_table] 
group by
    [product],[station_id],[ng_code]

--TRY

[唐诗三百首]

try this,

select a.product,a.station_id,a.test_count,
       b.ng_count,b.ng_code
 from
(select product,
        station_id,
        count(1) 'test_count'
 from [表名]
 group by product,station_id) a
inner join
(select product,station_id,ng_code,count(1) 'ng_count'
 from [表名]
 where result='FALSE'
 group by product,station_id,ng_code) b 
on a.product=b.product and a.station_id=b.station_id

[Jakey_Ch]

引用 3 楼 fredrickhu 的回复:

select
    [product], [station_id],
    count()over(partition by station_id) as  test_count    ,
    count(1) as ng_count ,ng_code
from
    [statistics_table] 
group by
    [product],[station_id],[ng_code]

--TRY

谢谢！ count(*) over(partition by station_id) as test_count ,这句有语法错误，count缺少参数

引用 4 楼 ap0405140 的回复:

try this,

select a.product,a.station_id,a.test_count,
       b.ng_count,b.ng_code
 from
(select product,
        station_id,
        count(1) 'test_count'
 from [表名]
 group by product,station_id) a
inner join
(select product,station_id,ng_code,count(1) 'ng_count'
 from [表名]
 where result='FALSE'
 group by product,station_id,ng_code) b 
on a.product=b.product and a.station_id=b.station_id

谢谢！ 这个能得到我想要的结果。

[Jakey_Ch]

引用 4 楼 ap0405140 的回复:

try this
[/code]

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。
万分感谢~~