I have a table "Student", now I would like to query the result as same as the second table. One group must have 2 students.
I have a table Student
, now I would like to query the result the same as the second table. One group must have 2 students.
Student ID Name Gender Group
1 A M A1
2 B F A1
3 C M A2
4 D M A2
5 E F A3
6 F F A3
Name1 Gender1 Name2 Gender2 Group
A M B F A1
C M D 开发者_开发技巧 M A2
E F F F A3
Something like:
select t1.name, t1.gender, t2.name, t2.gender, t1.group
from student t1, student t2
where t1.group = t2.group and t1.id < t2.id
Here is the complete example.
CREATE TABLE testing
(id char(10),
name char(10),
gender char(1),
grp char(10))
insert into testing values ('1','A','M','A1')
insert into testing values ('2','B','F','A1')
insert into testing values ('3','C','M','A2')
insert into testing values ('4','D','M','A2')
insert into testing values ('5','E','F','A3')
insert into testing values ('6','F','F','A3')
select name1, gender1, name2, gender2, a.grp from
(
SELECT name1 = CASE CONVERT(int,id)%2 WHEN 1 THEN name ELSE null END,
gender1 = CASE CONVERT(int,id)%2 WHEN 1 THEN gender ELSE null END,
grp FROM testing where CONVERT(int,id)%2 =1
) a
left join
(
SELECT name2 = CASE CONVERT(int,id)%2 WHEN 0 THEN name ELSE null END,
gender2 = CASE CONVERT(int,id)%2 WHEN 0 THEN gender ELSE null END,
grp FROM testing where CONVERT(int,id)%2 =0
) b on a.grp=b.grp
SELECT s1.name AS Name1, s1.gender AS Gender1,
s2.name AS Name2, s2.gender AS Gender2,
groups.group_s
FROM (
SELECT MIN(id) AS st_first, MAX(id) AS st_second, group_s
FROM students
GROUP BY group_s
) AS groups
LEFT JOIN students s1 ON groups.st_first=s1.id
LEFT JOIN students s2 ON groups.st_second=s2.id
students
is the name of your table and group_s
is the group column
精彩评论