SQL数据库,select两个字段的非重复值并且排序

发布网友发布时间：2022-05-02 23:46

共1个回答

热心网友时间：2022-06-28 12:02

CREATE
TABLE
message
(
id
int,
name1
varchar(2),
data1
int,
name2
varchar(2),
data2
int
);
INSERT
INTO
message
SELECT
1,
'A',
4,
'A',
1
UNION
ALL
SELECT
2,
'B',
3,
'C',
2
UNION
ALL
SELECT
3,
'A',
3,
'B',
4
UNION
ALL
SELECT
4,
'B',
2,
'D',
6
UNION
ALL
SELECT
5,
'C',
5,
'A',
4
UNION
ALL
SELECT
6,
'A',
4,
'C',
7
UNION
ALL
SELECT
7,
'A',
5,
'A',
8
UNION
ALL
SELECT
8,
'B',
9,
'A',
3
UNION
ALL
SELECT
9,
'B',
7,
'B',
4
UNION
ALL
SELECT
10,
'C',
8,
'C',
5;
SELECT
my_data,
CONCAT('(',
GROUP_CONCAT(my_name),
')')
AS
names
FROM
(
SELECT
'name1'
as
my_name,
data1
as
my_data
FROM
message
WHERE
name1
=
'A'
UNION
SELECT
'name2'
as
my_name,
data2
as
my_data
FROM
message
WHERE
name2
=
'A'
)
subQuery
GROUP
BY
my_data
ORDER
BY
my_data
DESC;
+---------+---------------+
|
my_data
|
names
|
+---------+---------------+
|
8
|
(name2)
|
|
5
|
(name1)
|
|
4
|
(name1,name2)
|
|
3
|
(name1,name2)
|
|
1
|
(name2)
|
+---------+---------------+
5
rows
in
set
(0.00
sec)