是否可以在PARTITION语句中添加where子句 [英] Is it possible to add a where clause in a PARTITION statement

问题描述

下面是我当前的选择声明，我用来根据症状体重选择我的主要。

 （ CASE   WHEN （ROW_NUMBER（）OVER（PARISTION BY LIstAllStages_Backup3.productserial 
 ORDER BY LIstAllStages_Backup3。 SymptomWeight））=  1  那么 ' < span class =code-comment> Y'ELSE'N'END'Primary' 

这就是我的系统工作方式。



收到产品，进入阶段，选择症状，产品进入下一阶段，症状，症状体重被选中等，大约有10个阶段。



我创建了一个联合所有语句来选择产品，阶段代码，症状，工作完成和已经调用了这个LIstAllStages_Backup3。



以上PARTITION在PR时工作正常系统只会通过系统一次，因为它会根据SymptomWeight选择一个主要症状作为Y.



我遇到的问题是如果产品再次收到并且症状重量小于我报告中的先前主要症状，它将显示主要症状是最高加权症状。



我可以添加一个地方条款，以便它将选择并指定一个主要产品，只有

产品，其范围为> DateReceived和< datedispatched>





表的结构如下

 id（int）
 Serial（nvarchar 10） 
代码（Nvarchar 4）
 passfail（int）
 Faultid int 
 SubFaultid int 
 Symptomid int 
 WorkCompleted int 
 SymptomWeight int 

代码=舞台代码
所有10个舞台

 id |序列号|代码| PassFail | Faultid | SubfaultID |症状|工作完成|症状重量
 422 | 40284 | SLLS | 1 | NULL | NULL | 84 | 2012-01-04 | 999 

[/ END]





Partion by根据症状对序列进行排名。如果Serial只进入系统一次，那就太好了。这样我就能选择体重最大的症状



样本数据

症状

 NFF 900 
 NFD 850 
 NFC 800 
 NFS 750 
 NHF 700 
 NNF 650 

收货表

<前lang =text> id | serial |工作完成|症状| SymptomWeight
1 | 100 | 2013-03-01 | NULL | 999（如果症状为空，则为默认值）
2 | 100 | 2013-04-11 | NULL | 999

发货表

<前lang =text> id | serial |工作完成|症状|症状重量
1 | 100 | 2013-03-12 | NULL | 999
2 | 100 | 2013-04-15 | NULL | 999

表1

 id | serial |工作完成|症状|症状重量
 1 | 100 | 2013-03-03 | NFF | 900 
 3 | 100 | 2013-04-11 | NFD | 850 

表2

 id | serial |工作完成|症状|症状重量
 1 | 100 | 2013-03-04 | NFD | 850 
 5 | 100 | 2013-04-12 | NFC | 800 

表A，B联盟，接收和发送VwListallstages

 id | serial |工作完成|症状|症状重量
 1 | 100 | 2013-03-01 | NULL | 999（如果症状为空，则为默认值）
 2 | 100 | 2013-04-11 | NULL | 999 
 1 | 100 | 2013-03-12 | NULL | 999 
 2 | 100 | 2013-04-15 | NULL | 999 
 1 | 100 | 2013-03-03 | NFF | 900 
 3 | 100 | 2013-04-11 | NFD | 850 
 1 | 100 | 2013-03-04 | NFD | 850 
 5 | 100 | 2013-04-12 | NFC | 800 

期望的结果

这是我目前的选择陈述

 选择 id | serial |工作完成|症状| SymptomWeight 
  CASE   WHEN （ROW_NUMBER（） OVER （ PARTITION   BY  LIstAllStages.serial 
  ORDER   BY  LIstAllStages.SymptomWeight））=  1  那么 '  Y'  ELSE  '  N'  END  '  listallstages的主要
  

 id | serial |工作完成|症状| SymptomWeight | Primary 
 1 | 100 | 2013-03-01 | NULL | 999 | N 
 1 | 100 | 2013-03-12 | NULL | 999 | N 
 1 | 100 | 2013-03-03 | NFF | 900 | N 
 1 | 100 | 2013-03-04 | NFD | 850 | Y 
 2 | 100 | 2013-04-11 | NULL | 999 | N 
 2 | 100 | 2013-04-15 | NULL | 999 | N 
 3 | 100 | 2013-04-11 | NFD | 850 | N 
 5 | 100 | 2013-04-12 | NFC | 800 |是

当前结果

 id | serial |工作完成|症状| SymptomWeight | Primary 
 1 | 100 | 2013-03-01 | NULL | 999 | N 
 1 | 100 | 2013-03-12 | NULL | 999 | N 
 1 | 100 | 2013-03-03 | NFF | 900 | N 
 1 | 100 | 2013-03-04 | NFD | 850 | N 
 2 | 100 | 2013-04-11 | NULL | 999 | N 
 2 | 100 | 2013-04-15 | NULL | 999 | N 
 3 | 100 | 2013-04-11 | NFD | 850 | N 
 5 | 100 | 2013-04-12 | NFC | 800 | Y 

[/ EDIT]

解决方案

你是经济学的，有你的问题描述...所以，我只能猜测你需要用另一个替换 ROW_NUMBER（）排名函数（T-SQL） [ ^ ]，例如 DENSE_RANK（） [ ^ ]功能。 DENSE_RANK（）返回结果集分区内的行级别，排名没有任何差距;一行的等级是一个加上有关行之前的不同等级的数量。

（ CASE   WHEN （DENSE_RANK（） OVER （ PARTITION   BY  LIstAllStages_Backup3.productserial  ORDER   BY  LIstAllStages_Backup3.SymptomWeight  ASC ））=  1   THEN  '  Y'  ELSE  '  N'  END  '  Primary' 

我做了这样的事情：

DECLARE @ Receiving 表（id INT ，serial INT ，Workcompleted DATETIME ，症状 VARCHAR （ 30 ），SymptomWeight INT ）
INSERT INTO @Receiving （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 1 ， 100 ，' 2013-03-01' ， NULL ， 999 ） - （症状为空时的默认值）
INSERT INTO @ Receiving （id，serial ，Workcompleted，症状，症状重量）
VALUES （ 2 ， 100 ，' 2013-04-11'， NULL ， 999 ）

DECLARE @ Dispatch TABLE （id INT ，serial INT ，Workcompleted DATETIME ，症状 VARCHAR （ 30 ），SymptomWeight INT ）
INSERT INTO @ D ispatch （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 1 ， 100 ，' 2013-03-12'， NULL ， 999 ）
INSERT INTO @ Dispatch （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 2 ， 100 ，< span class =code-string>' 2013-04-15'， NULL ， 999 ）

DECLARE @ Table1 TABLE （id INT ，serial INT ，Workcompleted DATETIME ，症状 VARCHAR （ 30 ），SymptomWeight INT ）
INSERT INTO @ Table1 （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 1 ， 100 ，' 2013-03-03'，' NFF'， 900 ）
INSERT INTO @ Table1 （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 3 ， 100 ，' 2013-04-11'，' NFD'， 850 ）

DECLARE @ Table2 TABLE （id < span class =code-keyword> INT ，serial INT ，Workcompleted DATETIME ，症状 VARCHAR （ 30 ），SymptomWeight INT ）
INSERT INTO @ Table2 （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 1 ， 100 ，' 2013-0 3-04'，' NFD'， 850 ）
INSERT INTO @ Table2 （id，serial，Workcompleted，symptom，SymptomWeight）
VALUES （ 5 ， 100 ，' 2013-04-12 '，' NFC'， 800 ）

- 表A，B的联合，接收和发送VwListallstages

DECLARE @ VwListallstages TABLE （id INT ，serial INT ，Workcomplet ed DATETIME ，症状 VARCHAR （ 30 ） ，SymptomWeight INT ，stage INT ）

INSERT INTO @ VwListallstages （id，serial，Workcompleted，symptom，SymptomWeight，阶段）
SELECT *
FROM （
SELECT *， 1 AS 阶段
FROM @ Receiving
UNION ALL
SELECT *， 4 < span class =code-keyword> AS 阶段
FROM @ Dispatch
UNION ALL
SELECT *， 2 AS 阶段
FROM @ Table1
UNION ALL
SELECT *， 3 AS 阶段
FROM @ Table2
） AS T

SELECT *
FROM （
SELECT t1。*，< span class =code-keyword> CASE WHEN t2。[主要] IS NULL 那么 ' N' ELSE ' < span class =code-string> Y' END AS [主]
FROM @ VwListallstages AS t1 LEFT JOIN （
SELECT serial，MONTH（WorkCompleted） AS MonthOfWC，MAX（WorkCompleted） AS WorkCompleted，MAX（阶段） AS [主要]
FROM @ VwListallstages
WHERE 症状 IS 不 NULL
GROUP BY serial，MONTH（WorkCompleted）
） AS t2 ON t1.serial = t2.serial AND t1.WorkCompleted = t2.WorkCompleted
） AS T
ORDER BY id，stage

我的结果：

 1 100 2013-03-01 00：00：00.000 NULL 999 1 N 
 1 100 2013-03-03 00 ：00：00.000 NFF 900 2 N 
 1 100 2013-03-04 00：00：00.000 NFD 850 3 Y 
 1 100 2013-03-12 00：00：00.000 NULL 999 4 N 
 2 100 2013-04-11 00：00：00.000 NULL 999 1 N 
 2 100 2013-04-15 00：00：00.000 NULL 999 4 N 
 3 100 2013-04-11 00：00：00.000 NFD 850 2 N 
 5 100 2013-04-12 00：00：00.000 NFC 800 3 Y 

[/ EDIT]

Hi below is my current select statement i am using to select my primary based on the Symptom weight.

( CASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages_Backup3.productserial
ORDER BY LIstAllStages_Backup3.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primary'

this is how my system works.

the product is received,goes to a stage ,a symptom is selected,the product goes the the next stage,symptom,SymptomWeight is selected etc,There are about 10 stages.

I have created a union all statement to select product,stagecode,symptom,workcompleted and have called this LIstAllStages_Backup3.

The above PARTITION works perfect when the product only goes through the system once as it would select one primary symptom as Y based on the SymptomWeight.

The problem i am having is if the product is received again and the symptomWeight is less than the previous primary symptom on my report it would show the primary symptom to be the highest weighted symptom.

Is it possible for me to add a where clause so that it will select and assign a primary only to
products that are in the range of >DateReceived and <datedispatched>

[EDIT #1]
Structure of the table is as follows

id (int)
Serial	(nvarchar 10)
code    (Nvarchar 4)
passfail (int )
Faultid	int
SubFaultid int
Symptomid int
WorkCompleted int
SymptomWeight	int

Code = Stage code
for all 10 stages

id| serial|code|PassFail|Faultid|SubfaultID|Symptomid|Workcompleted|SymptomWeight
422|40284 |SLLS|  1	|NULL	|NULL	   |	84   |	2012-01-04 |   999

[/END]

[EDIT #2]
The Partion by is ranking the serial based on the Symptom. Which is great if the Serial has only come into the system once. That way i get to select my symptom with the greatest weight

Sample data
Symmptoms

NFF 900
NFD 850
NFC 800
NFS 750
NHF 700
NNF 650

Receiving Table

id|serial | Workcompleted |symptom | SymptomWeight
1|100     | 2013-03-01    |NULL    | 999(Default Value if Symptom is null)
2|100     | 2013-04-11    |NULL    | 999

Dispatch Table

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-12     | NULL   | 999
2| 100    |2013-04-15     | NULL   | 999

Table 1

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-03     | NFF    | 900
3| 100    |2013-04-11     | NFD    | 850

Table 2

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-04     | NFD    | 850
5| 100    |2013-04-12     | NFC    | 800

Union of Table A,B,Receiving and Dispatch VwListallstages

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    | 2013-03-01    |NULL    | 999(Default Value if Symptom is null)
2| 100    | 2013-04-11    |NULL    | 999
1| 100    |2013-03-12     | NULL   | 999
2| 100    |2013-04-15     | NULL   | 999
1| 100    |2013-03-03     | NFF    | 900
3| 100    |2013-04-11     | NFD    | 850
1| 100    |2013-03-04     | NFD    | 850
5| 100    |2013-04-12     | NFC    | 800

Desired Results
This is my current select statement

select id|serial | Workcompleted |symptom | SymptomWeight
CASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages.serial
ORDER BY LIstAllStages.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primary
from listallstages

id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100    | 2013-03-01    |NULL    | 999          | N
1| 100    |2013-03-12     | NULL   | 999          | N
1| 100    |2013-03-03     | NFF    | 900          | N
1| 100    |2013-03-04     | NFD    | 850          | Y
2|100     | 2013-04-11    |NULL    | 999          | N
2| 100    |2013-04-15     | NULL   | 999          | N
3| 100    |2013-04-11     | NFD    | 850          | N
5| 100    |2013-04-12     | NFC    | 800          | Y

Current Result

id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100    | 2013-03-01    |NULL    | 999          | N
1| 100    |2013-03-12     | NULL   | 999          | N
1| 100    |2013-03-03     | NFF    | 900          | N
1| 100    |2013-03-04     | NFD    | 850          | N
2|100     | 2013-04-11    |NULL    | 999          | N
2| 100    |2013-04-15     | NULL   | 999          | N
3| 100    |2013-04-11     | NFD    | 850          | N
5| 100    |2013-04-12     | NFC    | 800          | Y

[/EDIT]

解决方案

You''re economicla with words, with description of your issue... So, i can only guess that you need to replace ROW_NUMBER() with another Ranking Functions (T-SQL)[^], for example DENSE_RANK()[^] function. DENSE_RANK() returns the rank of rows within the partition of a result set, without any gaps in the ranking; the rank of a row is one plus the number of distinct ranks that come before the row in question.

( CASE WHEN (DENSE_RANK() OVER (PARTITION BY LIstAllStages_Backup3.productserial ORDER BY LIstAllStages_Backup3.SymptomWeight ASC)) = 1 THEN 'Y' ELSE 'N' END 'Primary'

[EDIT #1]
I have done something this:

DECLARE @Receiving Table (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Receiving (id, serial, Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-01', NULL, 999) --(Default Value if Symptom is null)
INSERT INTO @Receiving (id, serial, Workcompleted, symptom, SymptomWeight)
VALUES(2, 100, '2013-04-11', NULL, 999)

DECLARE @Dispatch TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Dispatch (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-12', NULL, 999)
INSERT INTO @Dispatch(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(2, 100, '2013-04-15' ,NULL, 999)

DECLARE @Table1 TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Table1 (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-03', 'NFF', 900)
INSERT INTO @Table1(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(3, 100, '2013-04-11', 'NFD', 850)

DECLARE @Table2 TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Table2 (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-04', 'NFD', 850)
INSERT INTO @Table2(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(5, 100, '2013-04-12', 'NFC', 800)

--Union of Table A,B,Receiving and Dispatch VwListallstages

DECLARE @VwListallstages TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT, stage INT)

INSERT INTO @VwListallstages (id, serial,  Workcompleted, symptom, SymptomWeight, stage)
SELECT *
FROM (
	SELECT *, 1 AS stage
	FROM @Receiving
	UNION ALL
	SELECT *, 4 AS stage
	FROM @Dispatch
	UNION ALL
	SELECT *, 2 AS stage
	FROM @Table1
	UNION ALL
	SELECT *, 3 AS stage
	FROM @Table2
) AS T

SELECT * 
FROM (
	SELECT t1.*, CASE WHEN t2.[Primary] IS NULL THEN 'N' ELSE 'Y' END AS [Primary]
	FROM @VwListallstages AS t1 LEFT JOIN (
			SELECT serial, MONTH(WorkCompleted) AS MonthOfWC, MAX(WorkCompleted) AS WorkCompleted, MAX(stage) AS [Primary]
			FROM @VwListallstages
			WHERE symptom IS NOT NULL 
			GROUP BY serial, MONTH(WorkCompleted)
			) AS t2 ON t1.serial = t2.serial AND t1.WorkCompleted = t2.WorkCompleted
	) AS T
ORDER BY id, stage

My results:

1   100 2013-03-01 00:00:00.000 NULL    999 1   N
1   100 2013-03-03 00:00:00.000 NFF 900 2   N
1   100 2013-03-04 00:00:00.000 NFD 850 3   Y
1   100 2013-03-12 00:00:00.000 NULL    999 4   N
2   100 2013-04-11 00:00:00.000 NULL    999 1   N
2   100 2013-04-15 00:00:00.000 NULL    999 4   N
3   100 2013-04-11 00:00:00.000 NFD 850 2   N
5   100 2013-04-12 00:00:00.000 NFC 800 3   Y

[/EDIT]

这篇关于是否可以在PARTITION语句中添加where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！