SQL how to achieve this?-CodePudding

Table 1:
The Name Count
AAA 2
BBB 3
AAA - 1
CCC 2
BBB - 1
CCC 1

How to use SQL to the following results:

The Name Count
AAA 1
BBB 1
BBB 1
CCC 1
CCC 1
CCC 1

Asked the Count column is 1, the consolidation of the Count value transformation lines

CodePudding user response:

 


IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(the NAME VARCHAR (10), 
The COUNT INT) 

INSERT INTO # T 
2 UNION ALL SELECT 'AAA' 
3 UNION ALL SELECT 'BBB' 
UNION ALL SELECT 'AAA' - 1 
2 UNION ALL SELECT 'CCC' 
UNION ALL SELECT 'BBB' - 1 
SELECT 'CCC', 1 

GO 

The SELECT A.N AME, 1 
The FROM 
(SELECT NAME, SUM (COUNT) AS QTY FROM # T GROUP BY NAME) AS A 
JOIN the master. The dbo. Spt_values B ON A.Q TY>=B.n umber 
WHERE TYPE='P' AND number> 0

CodePudding user response:

reference 1st floor RINK_1 response:

 


IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(the NAME VARCHAR (10), 
The COUNT INT) 

INSERT INTO # T 
2 UNION ALL SELECT 'AAA' 
3 UNION ALL SELECT 'BBB' 
UNION ALL SELECT 'AAA' - 1 
2 UNION ALL SELECT 'CCC' 
UNION ALL SELECT 'BBB' - 1 
SELECT 'CCC', 1 

GO 

The SELECT A.N AME, 1 
The FROM 
(SELECT NAME, SUM (COUNT) AS QTY FROM # T GROUP BY NAME) AS A 
JOIN the master. The dbo. Spt_values B ON A.Q TY>=B.n umber 
WHERE TYPE='P' AND number> 0

To table B is stem what of