在SQl服务器查询中设置的逐步序列号 [英] Step by Step Sequence Number set in SQl Server Query

问题描述

嗨....

嗨..结婚早安



我的问题是：



ID代码数量

1 A1 A1 1

2 A2 A2 1

3 A3 A3 1 br />
4 A5 A5 1

5 A6 A6 1

6 A7 A7 1

7 A11 A11 1

8 A12 A12 1

9 A13 A13 1



结束此范围

ID来自至数量

1 A1 A3 3

2 A5 A7 3

3 A11 A13 3



请快速重播我..

如何查询或Vb代码.. ????

解决方案

当然，这很简单：

选择SClrMemberCd，SMemberCd，SClientCd，SGroupId，BClrMemberCd，BMemberCd，BClientCd，BGroupId，
 WRNOFrom，WRNOTo，W.MastName Warehouse ，Rate，Qty，ExpiryDate，P.ProductName 
来自AllocationCallSub A，WarehouseMast W，ProductMast P 
其中A.WarehouseId = W.MastId 
 AND A.ProductId = P.ProductId 
订购[WRNOForm] ASC 

不包括使用ORDER BY语句导致看似随机返回SELECT命中。这是一个很好的例子，imho，为什么ORDER BY不是直觉自动的或基于之前的SELECT ......那么多的现场成员。

尝试这个



假设你的表名ABCD

  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  [ FROM ]  ASC ），[ FROM ] = '  A' + CAST（[ FROM ]  AS   VARCHAR ），[ TO ] = '  A' + CAST（[ TO ]  AS   VARCHAR ），SUM（QTY） AS  QTY 
  FROM （
  SELECT  
（ CASE  
  WHEN  Code = '  A1' 或 Code = '  A2'  OR 代码= '  A3' 那么  1  
  WHEN 代码= '  A5'  OR 代码= '  A6'  OR 代码= '  A7' 那么  5  
  WHEN 代码= '  A11'  OR 代码= '  A12' 或代码= ' < span class =code-string> A13'  THEN   11  
 < span class =code-keyword> END ） AS  '  FROM'，
（ CASE  
  WHEN  Code = '  A1' 或代码= '  A2' 或代码= < span class =code-string>'  A3' 那么  5  
  WHEN 代码= '  A5' 或代码= '  A6' 或代码= '  A7' 那么  7  
  WHEN 代码= '  A11' 或代码= '  A12'  OR 代码= '  A13' 那么  13  
  END ） AS  '  TO' 
，数量
 < span class =code-keyword> FROM  ABCD）t 
  GROUP   BY  t。[ FROM ]，t。[到] 

  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  [ FROM ]  ASC ） AS  ID，[ FROM ] = '  A' + CAST（[ FROM ]  AS   VARCHAR ），[ TO ] = '  A' + CAST（[ TO ]  AS   VARCHAR ），SUM（QTY） AS  QTY 
  FROM （
  SELECT  
（ CASE  
  WHEN  SubString（代码，PatIndex（' ％[0- 9 .-]％'，代码），LEN（代码）） BETWEEN   1  < span class =code-keyword> AND   3  那么  1  
  WHEN  SubString（代码，PatIndex（' < span class =code-string>％[0-9 .-]％'，代码），LEN（代码）） BETWEEN   5   AND   7  那么  5  
  WHEN  SubString（代码，PatIndex（' ％[0-9 .-]％'，代码），LEN（代码）） BETWEEN   11   AND   13  那么  11  
  END ） AS  '  FROM'，
（ CASE  
  WHEN  SubString（代码，PatIndex（' ％[0-9 .-]％'，代码），LEN（代码）） BETWEEN   1   AND   3  那么  5  
  WHEN  SubString（代码，PatIndex（' ％[0-9.- ]％'，代码），LEN（代码）） BETWEEN   5   AND   7  那么  7  
  WHEN  SubString（代码，PatIndex（' ％[0-9 .-]％'，代码），LEN（代码）） BETWEEN   11   AND   13  那么  13  
  END ） AS  '  TO' 
，数量
  FROM  ABCD）t 
  GROUP   BY  t。[ FROM ]，t。[< span class =code-keyword> To ] 

 FROM到QTY 
 ---- ---- ----------- 
 A11 A13 3 
 A1 A5 3 
 A5 A7 3 

Hi....
Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

解决方案

Sure, that''s easy:

Select SClrMemberCd,SMemberCd,SClientCd,SGroupId,BClrMemberCd,BMemberCd,BClientCd,BGroupId,
WRNOFrom, WRNOTo,W.MastName Warehouse,Rate,Qty,ExpiryDate,P.ProductName
From AllocationCallSub A,WarehouseMast W,ProductMast P
Where A.WarehouseId = W.MastId
AND A.ProductId = P.ProductId
ORDER BY [WRNOForm] ASC

Not including the ORDER BY statement results in seemingly random return of SELECT hits. This is a great example, imho, of why the ORDER BY isn''t intuitively automatic or based upon a previous SELECT ... so many field members.

TRY THIS

Suppose your table name "ABCD"

SELECT ROW_NUMBER() OVER(ORDER BY [FROM] ASC) , [FROM]= 'A' + CAST([FROM] AS VARCHAR) , [TO] = 'A' + CAST([TO] AS VARCHAR), SUM(QTY) AS QTY
FROM (
SELECT 
(CASE
	WHEN Code ='A1' OR Code ='A2' OR Code='A3' THEN 1
	WHEN Code ='A5' OR Code ='A6' OR Code='A7' THEN 5
	WHEN Code ='A11' OR Code ='A12' OR Code='A13' THEN 11
END) AS 'FROM',
(CASE
	WHEN Code ='A1' OR Code ='A2' OR Code='A3' THEN 5
	WHEN Code ='A5' OR Code ='A6' OR Code='A7' THEN 7
	WHEN Code ='A11' OR Code ='A12' OR Code='A13' THEN 13
END) AS 'TO'
,Qty
FROM ABCD) t
GROUP BY t.[FROM],t.[To]

OR

SELECT ROW_NUMBER() OVER(ORDER BY [FROM] ASC) AS ID , [FROM]= 'A' + CAST([FROM] AS VARCHAR) , [TO] = 'A' + CAST([TO] AS VARCHAR), SUM(QTY) AS QTY
FROM (
SELECT 
(CASE
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 1 AND 3 THEN 1
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 5 AND 7 THEN 5
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 11 AND 13 THEN 11	
END) AS 'FROM',
(CASE
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 1 AND 3 THEN 5
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 5 AND 7 THEN 7
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 11 AND 13 THEN 13
END) AS 'TO'
,Qty
FROM ABCD) t
GROUP BY t.[FROM],t.[To]

FROM To   QTY
---- ---- -----------
A11  A13  3
A1   A5   3
A5   A7   3

这篇关于在SQl服务器查询中设置的逐步序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！