是否可以在PARTITION语句中添加where子句

本文介绍了是否可以在PARTITION语句中添加where子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 下面是我当前的选择声明，我用来根据症状体重选择我的主要。 （ CASE WHEN （ROW_NUMBER（）OVER（PARISTION BY LIstAllStages_Backup3.productserial ORDER BY LIstAllStages_Backup3。 SymptomWeight））= 1 那么 ' 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 收货表 id | serial |工作完成|症状| SymptomWeight 1 | 100 | 2013-03-01 | NULL | 999（如果症状为空，则为默认值） 2 | 100 | 2013-04-11 | NULL | 999 发货表 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 ，' 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 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 AS 阶段 FROM @ Dispatch UNION ALL SELECT *， 2 AS 阶段 FROM @ Table1 UNION ALL SELECT *， 3 AS 阶段 FROM @ Table2 ） AS T SELECT * FROM （ SELECT t1。*， CASE WHEN t2。[主要] IS NULL 那么 ' N' ELSE ' 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.productserialORDER 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 toproducts that are in the range of >DateReceived and <datedispatched>[EDIT #1]Structure of the table is as followsid (int)Serial(nvarchar 10)code (Nvarchar 4)passfail (int )FaultidintSubFaultid intSymptomid intWorkCompleted intSymptomWeightintCode = Stage codefor all 10 stagesid| serial|code|PassFail|Faultid|SubfaultID|Symptomid|Workcompleted|SymptomWeight422|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 weightSample dataSymmptomsNFF 900NFD 850NFC 800NFS 750NHF 700NNF 650Receiving Tableid|serial | Workcompleted |symptom | SymptomWeight1|100 | 2013-03-01 |NULL | 999(Default Value if Symptom is null)2|100 | 2013-04-11 |NULL | 999Dispatch Tableid|serial | Workcompleted |symptom | SymptomWeight1| 100 |2013-03-12 | NULL | 9992| 100 |2013-04-15 | NULL | 999Table 1id|serial | Workcompleted |symptom | SymptomWeight1| 100 |2013-03-03 | NFF | 9003| 100 |2013-04-11 | NFD | 850Table 2id|serial | Workcompleted |symptom | SymptomWeight1| 100 |2013-03-04 | NFD | 8505| 100 |2013-04-12 | NFC | 800Union of Table A,B,Receiving and Dispatch VwListallstagesid|serial | Workcompleted |symptom | SymptomWeight1| 100 | 2013-03-01 |NULL | 999(Default Value if Symptom is null)2| 100 | 2013-04-11 |NULL | 9991| 100 |2013-03-12 | NULL | 9992| 100 |2013-04-15 | NULL | 9991| 100 |2013-03-03 | NFF | 9003| 100 |2013-04-11 | NFD | 8501| 100 |2013-03-04 | NFD | 8505| 100 |2013-04-12 | NFC | 800Desired ResultsThis is my current select statementselect id|serial | Workcompleted |symptom | SymptomWeightCASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages.serialORDER BY LIstAllStages.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primaryfrom listallstagesid|serial | Workcompleted |symptom | SymptomWeight|Primary1| 100 | 2013-03-01 |NULL | 999 | N1| 100 |2013-03-12 | NULL | 999 | N1| 100 |2013-03-03 | NFF | 900 | N1| 100 |2013-03-04 | NFD | 850 | Y2|100 | 2013-04-11 |NULL | 999 | N2| 100 |2013-04-15 | NULL | 999 | N3| 100 |2013-04-11 | NFD | 850 | N5| 100 |2013-04-12 | NFC | 800 | YCurrent Resultid|serial | Workcompleted |symptom | SymptomWeight|Primary1| 100 | 2013-03-01 |NULL | 999 | N1| 100 |2013-03-12 | NULL | 999 | N1| 100 |2013-03-03 | NFF | 900 | N1| 100 |2013-03-04 | NFD | 850 | N2|100 | 2013-04-11 |NULL | 999 | N2| 100 |2013-04-15 | NULL | 999 | N3| 100 |2013-04-11 | NFD | 850 | N5| 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 VwListallstagesDECLARE @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 stageFROM @ReceivingUNION ALLSELECT *, 4 AS stageFROM @DispatchUNION ALLSELECT *, 2 AS stageFROM @Table1UNION ALLSELECT *, 3 AS stageFROM @Table2) AS TSELECT *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 @VwListallstagesWHERE symptom IS NOT NULLGROUP BY serial, MONTH(WorkCompleted)) AS t2 ON t1.serial = t2.serial AND t1.WorkCompleted = t2.WorkCompleted) AS TORDER BY id, stageMy results:1 100 2013-03-01 00:00:00.000 NULL 999 1 N1 100 2013-03-03 00:00:00.000 NFF 900 2 N1 100 2013-03-04 00:00:00.000 NFD 850 3 Y1 100 2013-03-12 00:00:00.000 NULL 999 4 N2 100 2013-04-11 00:00:00.000 NULL 999 1 N2 100 2013-04-15 00:00:00.000 NULL 999 4 N3 100 2013-04-11 00:00:00.000 NFD 850 2 N5 100 2013-04-12 00:00:00.000 NFC 800 3 Y[/EDIT] 这篇关于是否可以在PARTITION语句中添加where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！