多列

假设我在 MySQL 数据库的表中有 a, b c, d列。我试图做的是在我的表中选择这4列中所有列的不同值(只选择不同的值)。我试过这样的方法:

SELECT DISTINCT a,b,c,d FROM my_table;
SELECT DISTINCT a,b,c,d FROM my_table GROUP BY a,b,c,d;

这些都没用，有人能帮忙吗？

谢谢你

注意 我想单独使用列 a, b, c d的不同值，而不是值的不同组合

229669 次浏览
小开

Both your queries are correct and should give you the right answer.

I would suggest the following query to troubleshoot your problem.

SELECT DISTINCT a,b,c,d,count(*) Count FROM my_table GROUP BY a,b,c,d
order by count(*) desc

That is add count(*) field. This will give you idea how many rows were eliminated using the group command.

小开

Taking a guess at the results you want so maybe this is the query you want then

SELECT DISTINCT a FROM my_table
UNION
SELECT DISTINCT b FROM my_table
UNION
SELECT DISTINCT c FROM my_table
UNION
SELECT DISTINCT d FROM my_table
小开
最佳答案

can this help?

select
(SELECT group_concat(DISTINCT a) FROM my_table) as a,
(SELECT group_concat(DISTINCT b) FROM my_table) as b,
(SELECT group_concat(DISTINCT c) FROM my_table) as c,
(SELECT group_concat(DISTINCT d) FROM my_table) as d
小开

This will give DISTINCT values across all the columns:

SELECT DISTINCT value
FROM (
SELECT DISTINCT a AS value FROM my_table
UNION SELECT DISTINCT b AS value FROM my_table
UNION SELECT DISTINCT c AS value FROM my_table
) AS derived
小开

Another simple way to do it is with concat()

SELECT DISTINCT(CONCAT(a,b)) AS cc FROM my_table GROUP BY (cc);
小开

I know that the question is too old, anyway:

SELECT a, b
FROM mytable
GROUP BY a, b;

This will give your all the combinations.

小开

You can simply use this to get back one row with four comma separated lists with all values.

SELECT
GROUP_CONCAT(DISTINCT `a`) AS `as`,
GROUP_CONCAT(DISTINCT `b`) AS `bs`,
GROUP_CONCAT(DISTINCT `c`) AS `cs`,
GROUP_CONCAT(DISTINCT `d`) AS `ds`
FROM
`my_table`;

If you need an other separator than the comma, add the SEPARATOR option to GROUP_CONCAT.

小开

select distinct concat( colA , ' ' , colB , ' ' , colC ) from tableName;

小开

Based on your answers I made the same but for extracting a json column vars with arrays of 8 values all merged in 1 column with quotes trimed distinct values.

SELECT DISTINCT TRIM(BOTH '"' FROM T1.`vars`) FROM (
SELECT JSON_EXTRACT(`vars`, '$[1]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[2]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[3]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[4]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[5]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[6]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[7]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
UNION
SELECT JSON_EXTRACT(`vars`, '$[8]') AS `vars`
FROM my_table
WHERE `vars` != '[]'
) T1
WHERE `vars` IS NOT NULL
AND `vars` != 'false';

Hope it can helps...

小开

i do NOT know the values of your columns are ids as mine, so here is my solution if it fits you

SELECT DISTINCT (sender+receiver) AS smart_sum, sender, receiver
FROM chatTable
WHERE sender=1 OR receiver=1
GROUP BY smart_sum

I have a chat table, sender and receiver columns represent the user_id of each, so the sum of them is actually unique.


提交答案