Scenario One:
The data in the table
Name Score
AAA 11
AAA 19
BBB 12
BBB 18
CCC 19
DDD 21
Expected query results are as follows
Name Score
AAA 30
BBB 30
CCC 19
DDD 21
Copy Code code as follows:
---Check if the table exists
if exists (select * from sysobjects where name= ' testsum ')
drop table Testsum
Go
---create a table
CREATE TABLE Testsum
(
TID int primary key identity (1,1),
Tname varchar () NULL,
Tscor int NULL
)
Go
Insert into Testsum (Tname,tscor)
Select ' AAA ', 11
UNION ALL
Select ' AAA ', 19
UNION ALL
Select ' BBB ', 12
UNION ALL
Select ' BBB ', 18
UNION ALL
Select ' CCC ', 19
UNION ALL
Select ' DDD ', 21
---query statement
Select Tname, sum (Tscor) from Testsum GROUP by Tname
---only query for Tscor sum of 30
Select Tname, sum (Tscor) from Testsum GROUP by Tname has sum (tscor) =30
Scenario Two:
Name Account Score
John Language 30
John Math 50
John English 70
Dick Language 50
Dick Math 80
Dick English 90
Expected query Results:
Name Chinese maths English
Zhang 330 50 70
Lee 450 80 90
Copy Code code as follows:
---Check if the table exists
if exists (select * from sysobjects where name= ' Testscore ')
drop table Testscore
Go
---create a table
CREATE TABLE Testscore
(
TID int primary key identity (1,1),
Tname varchar () NULL,
Ttype varchar (TEN) NULL,
Tscor int NULL
)
Go
Inserting Data---
INSERT into Testscore values (' John ', ' language ', 90)
INSERT into Testscore values (' John ', ' math ', 20)
INSERT into Testscore values (' John ', ' English ', 50)
INSERT into testscore values (' Dick ', ' language ', 30)
INSERT into testscore values (' Dick ', ' math ', 47)
INSERT into testscore values (' Dick ', ' English ', 78)
---query
Select Tname as ' name ',
Max (case ttype when ' language ' then Tscor else 0 end) ' Language ',
Max (case ttype when ' math ' then Tscor else 0 end) ' Mathematics ',
Max (case ttype when ' English ' then Tscor else 0 end) ' English '
From Testscore
GROUP BY Tname
Scenario Three:
Table: Table1
Fields: ID, name
Content:
----------------
1,aaa
1,bbb
2,ccc
2,ddd
3,eee
3,fff
--------------
Hope the result:
---------------------
1 Triple A BBB [triple-A-c BBB distinction between half-width spaces, similar to the following]
2 CCC DDD
3 Eee FFF
Copy Code code as follows:
f exists (select * from sysobjects where name= ' test1 ')
drop table Test1
Go
CREATE TABLE Test1
(
TID int primary key identity (1,1),
Tnum int NULL,
Tname varchar () null
)
Go
INSERT into test1 values (1, ' AA ')
INSERT into test1 values (1, ' BB ')
INSERT into test1 values (2, ' CC ')
INSERT into test1 values (2, ' DD ')
INSERT into test1 values (3, ' EE ')
INSERT into test1 values (3, ' FF ')
Select DISTINCT Tnum from Test1
) A
OUTER APPLY (
SELECT tname= STUFF (replace (
(
SELECT Tname from Test1 N
WHERE Tnum = A.tnum
For XML AUTO
), ' <n tname= ', ', ', '/> ', ', 1, 1, '
) N
Scenario Four:
I need to select the data in the table TB to get the data from the second table below, how do I write a SELECT statement?
Table TB
ID a flag class
----------+---------+--------+---------
1 2 1 A
2 2 1 A
3 4 1 A
4 5 2 A
5 3 2 A
6 4 1 A
7 2 1 A
8 3 2 A
9 4 2 A
5 3 A
5 1 B
2 1 B
3 1 B
4 1 B
2 3 B
7 3 B
3 2 B
4 1 B
5 1 B
2 2 B
1 1 B
1 1 C
2 3 C
6 3 C
3 2 C
...
You need to select the following table, grouped by class column, and the A1,a2,a3 field is Flag=1, 2, 3 o'clock TB the sum of the A field in the table
After selection
A1 A2 A3 Class
-----------+------------+-----------------+--------------
SUM (a) sum (a) sum (a) a
SUM (a) sum (a) sum (a) B
SUM (a) sum (a) sum (a) C
SUM (a) sum (a) sum (a) D
SUM (a) sum (a) sum (a) E
SUM (a) sum (a) sum (a) F
SUM (a) sum (a) sum (a) G
Copy Code code as follows:
---Check if the table exists
if exists (select * from sysobjects where name= ' Testflag ')
drop table Testflag
Go
---create a table
CREATE TABLE Testflag
(
TID int primary key identity (1,1),
Tname varchar () NULL,
Tflag int NULL,
Tscor int NULL
)
Go
Inserting Data---
Insert into Testflag (Tname,tflag,tscor)
Select ' AAA ', 1,11
UNION ALL
Select ' AAA ', 2,19
UNION ALL
Select ' AAA ', 3,12
UNION ALL
Select ' AAA ', 1,18
UNION ALL
Select ' AAA ', 2,19
UNION ALL
Select ' AAA ', 3,21
UNION ALL
Select ' BBB ', 1,11
UNION ALL
Select ' BBB ', 2,19
UNION ALL
Select ' BBB ', 3,12
UNION ALL
Select ' BBB ', 1,18
UNION ALL
Select ' BBB ', 2,19
UNION ALL
Select ' BBB ', 3,21
----Query Statement
SELECT DISTINCT Tname, (select SUM (Tscor) from Testflag where tflag=1 and testflag.tname = t.tname) as ' Flag1 ', (select SUM ( Tscor) from Testflag where tflag=2 and testflag.tname = t.tname) as ' Flag2 ' (select SUM (Tscor) from Testflag where tflag=3 and testflag.tname = t.tname) as ' flag3 ' from Testflag T Group by Tname,tflag
