假设在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
的不同值,而不是不同组合的值。
假设在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
的不同值,而不是不同组合的值。
我知道这个问题太老了,不过:
SELECT a, b
FROM mytable
GROUP BY a, b;
这将为您提供所有组合。
这个能有所帮助吗?
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
REPLACE(group_concat(DISTINCT d),',',' ')
。 - Slam另一种简单的方法是使用concat()
函数
SELECT DISTINCT(CONCAT(a,b)) AS cc FROM my_table GROUP BY (cc);
根据你所需的结果猜测,也许这就是你想要的查询
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
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
- T. Brian JonesSELECT e,f FROM ( SELECT DISTINCT zdjh AS e FROM
tj_xhqdUNION SELECT DISTINCT sjsj AS f FROM
tj_xhqdUNION SELECT DISTINCT xhqd AS g FROM
tj_xhqd) a
gives me Error Code: 1054 Unknown column 'f' in 'field list'
- Moeez这将在所有列之间提供DISTINCT值:
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
你提出的两个查询都是正确的,应该能够给你正确的答案。
我建议使用以下查询来解决你的问题。
SELECT DISTINCT a,b,c,d,count(*) Count FROM my_table GROUP BY a,b,c,d
order by count(*) desc
这是添加count(*)字段。这将让您了解使用group命令消除了多少行。
where
条件?我只想选出计数大于1的行。尝试了 select distinct a,b,c,count(*) from MyTempTable group by a,b,c order by count(*) desc where count(*)>1;
但它没有起作用。 - Navhaving count(*) > 1
而不是where
,因为这是聚合查询。顺便说一句,我的答案并没有完全回答上面提出的问题。无论如何,很高兴能帮助到你。 - TheTechGuydistinct a,b,c,count(*) from MyTable group by a,b,c having count(*) > 1 order by count(*) desc
。 - Nav根据您的答案,我编写了类似的代码,但用于提取一个名为vars
的json列,其中包含8个值的数组,所有值均合并在一个列中,并修剪引号以去重。
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';
希望它能有所帮助...
我不知道你的列值是否像我的一样是ID,所以这里是我的解决方案,如果适合你的话。
SELECT DISTINCT (sender+receiver) AS smart_sum, sender, receiver
FROM chatTable
WHERE sender=1 OR receiver=1
GROUP BY smart_sum
我有一张聊天表,发送者和接收者列代表每个用户的user_id,因此它们的总和实际上是唯一的。
您可以使用此方法获取一个包含四个逗号分隔列表的行,其中包含所有值。
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`;
如果您需要使用逗号以外的其他分隔符,请将SEPARATOR
选项添加到GROUP_CONCAT
中。