My SQL level co., LTD., recently need to query the database, but I tried many methods, or can not get the desired results, for everyone here,
I have a query table X, content is as follows:
ID NAME CLASS AREA
0001 A1 100
0002 A2 200
0003 c A1 100
0004 B1 400
0005 c B2 300

At the same time I want to be in the NAME, if exist A1 and A2, A1 is preserved, to add summary AREA at the same time, the results are as follows:
ID NAME CLASS AREA
0001 A1 300
0003 c A1 100
0004 B1 400
0005 c B2 300

CodePudding user response:

 
The CREATE TABLE # A 
(
ID VARCHAR (20), 
The NAME VARCHAR (20), 
CLASS a VARCHAR (20), 
AREA INT 
) 
# INSERT INTO A VALUES (' 0001 ', 'A', 'A1, 100) 
# INSERT INTO A VALUES (' 0002 ', 'A', 'A2' 200) 
# INSERT INTO A VALUES (' 0003 ', 'c', 'A1, 100) 
# INSERT INTO A VALUES (' 0004 ', 'has',' B1, 400) 
# INSERT INTO A VALUES (' 0005 ', 'c', 'B2, 300) 

SELECT Anderson D, A.N AME, A.C LASS, A.A REA + ISNULL ((SELECT B.A REA # FROM A to B WHERE biggest LASS='A2' AND B.N AME=A.N AME), 0) FROM # aa WHERE A.C LASS! 
='A2'
DROP TABLE # A 

CodePudding user response:

 
- gather together a jollification 


Select ID, NAME, case CLASS when 'A3' then 'A1 else CLASS end the CLASS, the sum (area) 
The from x 
Group by ID NAME, case CLASS when 'A3' then 'A1 else CLASS end 

CodePudding user response:

 

The CREATE TABLE # T 
(
ID VARCHAR (10), the NAME VARCHAR (10), a CLASS a VARCHAR (10), AREA INT 
) 

INSERT INTO # t VALUES (' 0001 ', 'a', 'A1, 100) 
INSERT INTO # t VALUES (' 0002 ', 'a', 'A2' 200) 
INSERT INTO # t VALUES (' 0003 ', 'c', 'A1, 100) 
INSERT INTO # t VALUES (' 0004 ', 'has',' B1, 400) 
INSERT INTO # t VALUES (' 0005 ', 'c', 'B2, 300) 
INSERT INTO # t VALUES (' 0006 ', 'd', 'A2' 700) 

The SELECT MIN (id) AS id, name, CLASS, the SUM (AREA) AS AREA FROM 
(
SELECT ID, NAME, AREA, and the CASE WHEN the CLASS IN (' A1 ', 'A2') THEN 'A1 ELSE CLASS END AS a CLASS FROM # T 
) a 
GROUP BY name, the CLASS 
The ORDER BY id 

CodePudding user response:

reference 1/f, kun jins kao kun jins kao response:

 
The CREATE TABLE # A 
(
ID VARCHAR (20), 
The NAME VARCHAR (20), 
CLASS a VARCHAR (20), 
AREA INT 
) 
# INSERT INTO A VALUES (' 0001 ', 'A', 'A1, 100) 
# INSERT INTO A VALUES (' 0002 ', 'A', 'A2' 200) 
# INSERT INTO A VALUES (' 0003 ', 'c', 'A1, 100) 
# INSERT INTO A VALUES (' 0004 ', 'has',' B1, 400) 
# INSERT INTO A VALUES (' 0005 ', 'c', 'B2, 300) 

SELECT Anderson D, A.N AME, A.C LASS, A.A REA + ISNULL ((SELECT B.A REA # FROM A to B WHERE biggest LASS='A2' AND B.N AME=A.N AME), 0) FROM # aa WHERE A.C LASS! 
='A2'
DROP TABLE # A 

No, if there are two armor A2 such complains, select the other to change
The SELECT Anderson D, A.N AME, A.C LASS, A.A REA +
ISNULL ((SELECT SUM (B.A REA) FROM # B, WHERE biggest LASS='A2' AND B.N AME=A.N AME), 0)
The FROM # aa WHERE A.C LASS!='A2'

CodePudding user response:

 
The SELECT MIN (Anderson, D) ID, A.N AME, MIN (A.C LASS) CLASS, the SUM (A.A REA) AREA FROM # aa WHERE A.C LASS LIKE 'A %' GROUP BY A.N AME 
UNION ALL 
SELECT * FROM # aa WHERE A.C LASS, NOT LIKE 'A %' 

is # 3 is a reminder of the, I will write directly to filter out all A2

CodePudding user response:

refer to fifth floor kun jins kao kun jins kao response:

 
The SELECT MIN (Anderson, D) ID, A.N AME, MIN (A.C LASS) CLASS, the SUM (A.A REA) AREA FROM # aa WHERE A.C LASS LIKE 'A %' GROUP BY A.N AME 
UNION ALL 
SELECT * FROM # aa WHERE A.C LASS, NOT LIKE 'A %' 

is # 3 is a reminder of the, I will write directly to filter out all A2

So soon found

CodePudding user response:

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

The CREATE TABLE # T 
(
ID VARCHAR (20), 
The NAME VARCHAR (20), 
CLASS a VARCHAR (20), 
AREA INT 
) 
INSERT INTO # T VALUES (' 0001 ', 'a', 'A1, 100) 
INSERT INTO # T VALUES (' 0002 ', 'a', 'A2' 200) 
INSERT INTO # T VALUES (' 0003 ', 'c', 'A2' 100) 
INSERT INTO # T VALUES (' 0004 ', 'has',' A2 '400) 
INSERT INTO # T VALUES (' 0005 ', 'c', 'A1, 300) 
INSERT INTO # T VALUES (' 0006 ', 'c', 'A3, 300) 
INSERT INTO # T VALUES (' 0007 ', 'has',' A2 '400) 

GO 

SELECT ID, NAME, CLASS, (SELECT SUM (AREA) the FROM # T WHERE NAME=A.N AME AND CLASS IN (' A1 ', 'A2')) AS AREA 
The FROM # T A 
WHERE the EXISTS (SELECT 1 FROM # T WHERE A.N AME=NAME AND CLASS='A2' AND ID<> Anderson, D) 
AND CLASS='A1' 
nullnullnullnullnullnullnullnullnullnull