The sum of the first working procedure summary of various kinds of products-CodePudding

The sum of a process with only a summary of various kinds of products the first

The name of the product process quantity
AAA process 1 100
AAA process 2 50
3 AAA process 100
BBB process 2 100
CCC process 1 100
CCC process 3 100

Summary of the first step of each product totaled 300, the first working procedure of procedure 1 AAA, BBB first process for process 2, the first step CCC for step 1

CodePudding user response:

SELECT SUM (quantity)
FROM the TABLE A
WHERE NOT the EXISTS (SELECT 1 FROM the TABLE WHERE the product name=a. product name AND the name of the process & lt; A. process name)

CodePudding user response:

Grouping sorting

 
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB 
The create table # TAB (name nvarchar (500), the class nvarchar (500), the quantity a decimal (18, 2)) 
Insert into # TAB (name, class, quantity) 
Select 'AAA', '1', 100 union all 
Select 'AAA', '2', 50 union all 
Select 'AAA', '3', 100 union all 
Select 'BBB', '2', 100 union all 
Select 'CCC', '1', 100 union all 
Select 'CCC', '1', 100 

Select sum (quantity) from (
The select row_number () over (partition by the name the order by class) as num, * from # TAB 
) t where num='1'

CodePudding user response:

reference 1st floor RINK_1 response:

SELECT SUM (quantity)
FROM the TABLE A
WHERE NOT the EXISTS (SELECT 1 FROM the TABLE WHERE the product name=a. product name AND the name of the process & lt; A. process name)

Process name is just a pronoun, 1 is not necessarily the process, process 2

CodePudding user response:

refer to the second floor _Asura response:

grouping sorting

 
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB 
The create table # TAB (name nvarchar (500), the class nvarchar (500), the quantity a decimal (18, 2)) 
Insert into # TAB (name, class, quantity) 
Select 'AAA', '1', 100 union all 
Select 'AAA', '2', 50 union all 
Select 'AAA', '3', 100 union all 
Select 'BBB', '2', 100 union all 
Select 'CCC', '1', 100 union all 
Select 'CCC', '1', 100 

Select sum (quantity) from (
The select row_number () over (partition by the name the order by class) as num, * from # TAB 
) t where num='1'

Process name is just a pronoun, 1 is not necessarily the process, process 2 this sort of name, also is the name of the other

CodePudding user response:

reference 4 floor snlixing response:

Quote: refer to the second floor _Asura response:

Grouping sorting

 
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB 
The create table # TAB (name nvarchar (500), the class nvarchar (500), the quantity a decimal (18, 2)) 
Insert into # TAB (name, class, quantity) 
Select 'AAA', '1', 100 union all 
Select 'AAA', '2', 50 union all 
Select 'AAA', '3', 100 union all 
Select 'BBB', '2', 100 union all 
Select 'CCC', '1', 100 union all 
Select 'CCC', '1', 100 

Select sum (quantity) from (
The select row_number () over (partition by the name the order by class) as num, * from # TAB 
) t where num='1'

Process name is just a pronoun, 1 is not necessarily the process, process 2 this sort of name, also is the name of the other

That the first step of how to distinguish between AAA and the second procedure?

CodePudding user response:

reference snlixing reply: 3/f

Quote: refer to 1st floor RINK_1 response:

SELECT SUM (quantity)
FROM the TABLE A
WHERE NOT the EXISTS (SELECT 1 FROM the TABLE WHERE the product name=a. product name AND the name of the process & lt; A. process name)

Process name is just a pronoun, is not necessarily working procedure 1, process 2 such

If you can't direct sequence comparison, then you have to have an auxiliary table to define each product each working procedure, at the same time identify the process sequence,

CodePudding user response:

reference 5 floor _Asura reply:

Quote: refer to 4th floor snlixing response:

Quote: refer to the second floor _Asura response:

Grouping sorting

If object_id (' tempdb for.. # # TAB ') is not null drop table TAB
The create table # TAB (name nvarchar (500), the class nvarchar (500), the quantity a decimal (18, 2))
Insert into # TAB (name, class, quantity)
Select 'AAA', '1', 100 union all
Select 'AAA', '2', 50 union all
Select 'AAA', '3', 100 union all
Select 'BBB', '2', 100 union all
Select 'CCC', '1', 100 union all
Select 'CCC', '1', 100

Select sum (quantity) from (
The select row_number () over (partition by the name the order by class) as num, * from # TAB
) t where num='1'

Process name is just a pronoun, 1 is not necessarily the process, process 2 this sort of name, also is the name of the other

That the first step of how to distinguish between AAA and the second procedure?

Existing procedure code is such, LS, NX, YZ, NS, BZ, only need to compare the LS, NX, these three processes of YZ, I write wrong

 SELECT SUM (LSTotalWeight) as TotalWeight, ContractNO, productno, techflag 
The FROM TechniqueInfo A 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull