通过sqlserver判断每天某列数据出现次数所占比例

[zhujiayi831]

该表每天会自动收集数据，然后表中Model表示种类，Judge表示判断，均不唯一。
现在是想通过分组，在WDate，Model相同情况下，Judge判断为NG的次数占当天该机种的百分比。
例：Z191K，2017-07-25 ，NG ，20%
Z191，2017-07-25，NG，10%

我的公式如下 ，

select a.WDate,a.Model,a.Judge,count(*) 判定,
cast( convert (decimal(18,2),100*cast(count(*) as float)/cast((select b.WDate,b.Model,count(*) from [Hyres].[dbo].[Hyres_W]b group by b.WDate,b.Model) as float) ) as varchar)+'%'
from [Hyres].[dbo].[Hyres_W]a where Judge='NG' group by a.WDate,a.Model,a.Judge

报错是这样；当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。

请问怎么修改？
更多 0

[AcHerat]

没有细看问题，只是看语句修改的

select a.WDate,a.Model,a.Judge,count(*) 判定,  
cast( convert (decimal(18,2),100*cast(count(*) as float)/cast(b.cnt as float) ) as varchar)+'%'  
from [Hyres].[dbo].[Hyres_W] a
  outer apply (select count(*) as cnt from [Hyres].[dbo].[Hyres_W] t where by a.WDate = t.WDate and a.Model = t.Model) b
where Judge='NG'
group by a.WDate,a.Model,a.Judge

[吉普赛的歌]

DECLARE @t TABLE (
	WID INT PRIMARY KEY,
	Model VARCHAR(20),
	Judge VARCHAR(20),
	SerlNo VARCHAR(50),
	WDate DATETIME
)
INSERT INTO @t VALUES (782480,'Z191K','A','XKD7c64090071','2017-03-09')
INSERT INTO @t VALUES (782481,'Z191K','A','XKD7c64090001','2017-03-09')
INSERT INTO @t VALUES (782482,'Z191K','A','XKD7c64090102','2017-03-09')
INSERT INTO @t VALUES (782483,'Z191K','A','XKD7c64090145','2017-03-09')
INSERT INTO @t VALUES (782484,'Z191K','NG','XKD7c64090063','2017-03-09')
INSERT INTO @t VALUES (782485,'Z191K','A','XKD7c64090081','2017-03-09')
INSERT INTO @t VALUES (782486,'Z191K2','A','XKD7c64090094','2017-03-09')
INSERT INTO @t VALUES (782487,'Z191K2','A','XKD7c64090098','2017-03-09')

;WITH cte AS (
	SELECT 
	ROW_NUMBER() OVER (PARTITION BY WDate, Model ORDER BY WID) AS rid,*
	FROM @t
)
SELECT WDate
,model
,Judge
,COUNT(1) AS theCnt
,(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate) AS theDayCount
,CONVERT(DECIMAL(10,2), COUNT(1)*100.0/(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate)) AS [percent]
FROM cte AS a GROUP BY WDate,model,Judge
/*
WDate	model	Judge	theCnt	theDayCount	percent
2017-03-09 00:00:00.000	Z191K	A	5	8	62.50
2017-03-09 00:00:00.000	Z191K	NG	1	8	12.50
2017-03-09 00:00:00.000	Z191K2	A	2	8	25.00
*/

[二月十六]

试试这个：

SELECT  a.WDate ,
        a.Model ,
        a.Judge ,
        COUNT(*) 判定 ,
        CAST(CONVERT (DECIMAL(18, 2), 100 * CAST(COUNT(*) AS FLOAT)
        / CAST(( SELECT COUNT(*)
                 FROM   [Hyres].[dbo].[Hyres_W] b
                 WHERE  a.WDate = b.WDate
                        AND a.Model = b.Model
               ) AS FLOAT)) AS VARCHAR) + '%'
FROM    [Hyres].[dbo].[Hyres_W] a
WHERE   Judge = 'NG'
GROUP BY a.WDate ,
        a.Model ,
        a.Judge;

[zhujiayi831]

谢谢楼上几位老师，@sinat_28984567 的那个执行速度贼快。

[zhujiayi831]

引用 2 楼 yenange 的回复:

DECLARE @t TABLE (
	WID INT PRIMARY KEY,
	Model VARCHAR(20),
	Judge VARCHAR(20),
	SerlNo VARCHAR(50),
	WDate DATETIME
)
INSERT INTO @t VALUES (782480,'Z191K','A','XKD7c64090071','2017-03-09')
INSERT INTO @t VALUES (782481,'Z191K','A','XKD7c64090001','2017-03-09')
INSERT INTO @t VALUES (782482,'Z191K','A','XKD7c64090102','2017-03-09')
INSERT INTO @t VALUES (782483,'Z191K','A','XKD7c64090145','2017-03-09')
INSERT INTO @t VALUES (782484,'Z191K','NG','XKD7c64090063','2017-03-09')
INSERT INTO @t VALUES (782485,'Z191K','A','XKD7c64090081','2017-03-09')
INSERT INTO @t VALUES (782486,'Z191K2','A','XKD7c64090094','2017-03-09')
INSERT INTO @t VALUES (782487,'Z191K2','A','XKD7c64090098','2017-03-09')

;WITH cte AS (
	SELECT 
	ROW_NUMBER() OVER (PARTITION BY WDate, Model ORDER BY WID) AS rid,*
	FROM @t
)
SELECT WDate
,model
,Judge
,COUNT(1) AS theCnt
,(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate) AS theDayCount
,CONVERT(DECIMAL(10,2), COUNT(1)*100.0/(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate)) AS [percent]
FROM cte AS a GROUP BY WDate,model,Judge
/*
WDate	model	Judge	theCnt	theDayCount	percent
2017-03-09 00:00:00.000	Z191K	A	5	8	62.50
2017-03-09 00:00:00.000	Z191K	NG	1	8	12.50
2017-03-09 00:00:00.000	Z191K2	A	2	8	25.00
*/

另外请教下，我这有三张表

其中W表

N表

T表

希望当WDate=NDate=TDate，且三表Model，SerlNo相等时，w,n,t任意一表中的Judge判定为‘NG’，则今天这个序列号判定为‘NG’，
想要写一个每天序列号判定‘NG’的不良率占今天所有序列号的比例。

[zhujiayi831]

引用 5 楼 zhujiayi831 的回复:

[quote=引用 2 楼 yenange 的回复:]

DECLARE @t TABLE (
	WID INT PRIMARY KEY,
	Model VARCHAR(20),
	Judge VARCHAR(20),
	SerlNo VARCHAR(50),
	WDate DATETIME
)
INSERT INTO @t VALUES (782480,'Z191K','A','XKD7c64090071','2017-03-09')
INSERT INTO @t VALUES (782481,'Z191K','A','XKD7c64090001','2017-03-09')
INSERT INTO @t VALUES (782482,'Z191K','A','XKD7c64090102','2017-03-09')
INSERT INTO @t VALUES (782483,'Z191K','A','XKD7c64090145','2017-03-09')
INSERT INTO @t VALUES (782484,'Z191K','NG','XKD7c64090063','2017-03-09')
INSERT INTO @t VALUES (782485,'Z191K','A','XKD7c64090081','2017-03-09')
INSERT INTO @t VALUES (782486,'Z191K2','A','XKD7c64090094','2017-03-09')
INSERT INTO @t VALUES (782487,'Z191K2','A','XKD7c64090098','2017-03-09')

;WITH cte AS (
	SELECT 
	ROW_NUMBER() OVER (PARTITION BY WDate, Model ORDER BY WID) AS rid,*
	FROM @t
)
SELECT WDate
,model
,Judge
,COUNT(1) AS theCnt
,(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate) AS theDayCount
,CONVERT(DECIMAL(10,2), COUNT(1)*100.0/(SELECT COUNT(1) FROM cte b WHERE b.Wdate=a.Wdate)) AS [percent]
FROM cte AS a GROUP BY WDate,model,Judge
/*
WDate	model	Judge	theCnt	theDayCount	percent
2017-03-09 00:00:00.000	Z191K	A	5	8	62.50
2017-03-09 00:00:00.000	Z191K	NG	1	8	12.50
2017-03-09 00:00:00.000	Z191K2	A	2	8	25.00
*/

另外请教下，我这有三张表 其中W表 N表 T表 希望当WDate=NDate=TDate，且三表Model，SerlNo相等时，w,n,t任意一表中的Judge判定为‘NG’，则今天这个序列号判定为‘NG’， 想要写一个每天序列号判定‘NG’的不良率占今天所有序列号的比例。 [/quote] 已经自己搞定，结贴谢谢。