SQL grouping Query

Source: Internet
Author: User

Scenario 1:
Table Data
Name score
Aaa 11
Aaa 19
Bbb 12
Bbb 18
Ccc 19
Ddd 21
The expected query result is as follows:
Name score
Aaa 30
Bbb 30
Ccc 19
Ddd 21
Copy codeThe Code is as follows:
--- Check whether 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 (30) 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 30 tscor total
Select tname, sum (tscor) from testSum group by tname having sum (tscor) = 30

Scenario 2:
Name subject score
Zhang San Chinese 30
John's mathematics 50
John's English 70
Li Si language 50
Li Si math 80
Li Si English 90

Expected query results:

Name, Chinese, mathematics, and English
Zhang San 30 50 70
Li Si 50 80 90
Copy codeThe Code is as follows:
--- Check whether 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 (30) null,
Ttype varchar (10) null,
Tscor int null
)
Go
--- Insert data
Insert into testScore values ('zhang san', 'China', 90)
Insert into testScore values ('zhang san', 'mat', 20)
Insert into testScore values ('zhang san', 'English ', 50)
Insert into testScore values ('Li si', 'China', 30)
Insert into testScore values ('Li si', 'mat', 47)
Insert into testScore values ('Li si', 'English ', 78)
--- Query
Select tname as 'name ',
Max (case ttype when 'China' then tscor else 0 end) 'China ',
Max (case ttype when 'mate' then tscor else 0 end) 'mate ',
Max (case ttype when 'then tscor else 0 end)''
From testScore
Group by tname

Scenario 3:
Table: table1
Field: id, name
Content:
----------------
1, aaa
1, bbb
2, ccc
2, ddd
3, eee
3, fff
--------------
Expected results:
---------------------
1 aaa bbb [aaa bbb is differentiated by halfwidth spaces, similar to the following]
2 ccc ddd
3 eee fff
Copy codeThe Code is 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 (30) null
)
Go
Insert into test1 values (1, 'A ')
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 * FROM (select distinct tnum FROM test1
)
Outer apply (
SELECT tname = STUFF (REPLACE (
(
SELECT tname FROM test1 N
WHERE tnum = A. tnum
FOR XML AUTO
), '<N tname = "',''), '"/>',''), 1, 1 ,'')
) N

Scenario 4:
I need to select the data in Table tb to get the data in the second table below. How do I write the select statement?
Table tb
Id a flag class
---------- + --------- + -------- + ---------
1 2 1
2 2 1
3 4 1
4 5 2
5 3 2
6 4 1
7 2 1
8 3 2
9 4 2
10 5 3
11 5 1 B
12 2 1 B
13 3 1 B
14 4 1 B
15 2 3 B
16 7 3 B
17 3 2 B
18 4 1 B
19 5 1 B
20 2 2 B
21 1 1 B
22 1 1 C
23 2 3 C
24 6 3 C
25 3 2 C
...
The following table needs to be selected and grouped by class column. The a1, a2, and a3 fields are the sum of fields a in the tb table when flag = 1, 2, and 3 respectively.
After selection
A1 a2 a3 class
----------- + ------------ + ----------------- + --------------
Sum (a)
Sum (a) B
Sum (a) C
Sum (a) D
Sum (a) E
Sum (a) F
Sum (a) G
Copy codeThe Code is as follows:
--- Check whether 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 (30) null,
Tflag int null,
Tscor int null
)
Go
--- Insert 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.