Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to query the cross table with sql statement". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Table 1:

group name

Member 1id

Member 2id

Member 3id

Sample data:

Charge team 1 2 3

Defenders 2 3 4

Table 2:

member ID

names of the members

Sample data:

1 triple

2 Li Si

3 King V

4 Lu2

required results

Charge Team Zhang San Li Si Wang Wu

Defender group Li Si Wang Wu Liu Er

The copy code is as follows:

--Establish a test environment

Create Table 1(groupname varchar(10), member1 id varchar(10), member2 id varchar(10), member3 id varchar(10))

--Insert data

insert into Table 1

select 'charge group',' 1','2','3' union

select 'Defender group',' 2','3','4'

Create Table 2(member id varchar(10), member name varchar(10))

--Insert data

insert into Table 2

select '1','' union

select '2','Li Si' union

select '3','King V' union

select '4','6'

--test statement

select a. group name,

Member 1=(select member name from Table 2 b where a. Member 1id=b. Member id),

Member 1=(select member name from Table 2 b where a. Member 2id=b. Member id),

Member 1=(select member name from Table 2 b where a. Member 3id=b. Member id)

from Table 1a

--Delete test environment

Drop Table 1

Drop Table 2

/*

Group Name Member 1 Member 1 Member 1

---------- ---------- ---------- ----------

Charge Team Zhang San Li Si Wang Wu

Defender group Li Si Wang Wu Liu Er

(The number of rows affected is 2)

*/

The copy code is as follows:

select

a. Group name,

Member 1 = max(case b. Member id = a. Member 1id then b. Member name end),

Member 2 = max(case b. member id = a. member 2id then b. member name end),

Member 3 = max(case b. member id = a. member 3id then b. member name end),

from

table ia,

Table IIb

group by

a. Group name

The copy code is as follows:

select

a. Group name,

Member 1 = max(case b. Member id = a. Member 1id then b. Member name end),

Member 2 = max(case b. member id = a. member 2id then b. member name end),

Member 3 = max(case b. Member id = a. Member 3id then b. Member name end)

from

table ia,

Table IIb

group by

a. Group name

The copy code is as follows:

select a. group name,

Member 1=(select member name from Table 2 b where a. Member 1id=b. Member id),

Member 1=(select member name from Table 2 b where a. Member 2id=b. Member id),

Member 1=(select member name from Table 2 b where a. Member 3id=b. Member id)

from Table 1a

The copy code is as follows:

The correct solution is

select table 1. group name,

(select table 1. member name from table 2 b where table 1. member 1id= table 2. member id) as member 1id,

(select table 1. member name from table 2 b where table 1. member 2id= table 2. member id) as member 2id,

(select Table 1. Member Name from Table 2 b where Table 1. Member 3id= Table 2. Member id) as Member 3id

from Table 1, Table 2

"How to use sql statement query crosstab" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.