mysql - MySQL Group and count rows with data
问题描述
I have a table that holds different kinds of data. what I would like to achieve is that I want to run a query through the database looking for rows that are equal in number and have the same content like say column has id 1 with 3 rows, and column has id 5 with 3 rows and all these values are same. I want to consider that as a group then count how many they are. Rows with different ids but same values. Then have a table that shows count that x matches were found and contains this+this+this values. This is in MySQL
id | drug
--------------
1 | LAMIVUDINE
----------------
1 | ZIDOVUDINE
----------------
1 | 3TC
----------------
3 | TTC
-------------
4 | KKY
------------
4 | JJ2
-------------
4 | FFF
---------------
5 | LAMIVUDINE
----------------
5 | ZIDOVUDINE
-----------------
5 | 3TC
------------------
8 | KKY
----------------
8 | JJ2
--------------
8 | FFF
---------------
count(id) AS number
drug As name
number | name
-------------------------------------
2 | LAMIVUDINE + ZIDOVUDINE + 3TC
---------------------------------------
2 | KKY + JJ2 + FFF
--------------------------------------`
1 | TTC
---------------------------------------
解决方案
You can use group_concat
with a +
separator for concatenate rows with same id and then do another group by on concatenated values to get the desired output
select count(*) number, names
from (
select count(*) total, group_concat(drug order by drug separator '+') names
from table1
group by id
) t
group by names
Order by clause is important inside group by so that concatenated string values should be in same order
推荐阅读
- javascript - 如何在javascript中显示矩阵行头
- javascript - Google Authentication JS API 重定向到错误的 URL
- angular - angular 6过滤器在界面上不起作用
- azure - 如何在 terraform 中继承/传递变量
- python - 如何在一列上分组并按日期排序,并且每个日期都使用熊猫在时间序列数据中按小时排序
- mysql - 我们如何将 AND & OR 运算符与简单连接一起使用?
- .net - 流的意外结束,在我朋友的电脑上,但不是我的。.Net 核心,SQL
- spring-security - Spring 安全性:ldap 配置
- android - 确定 Google 帐户是否可用
- django - 在进行连接的 Django 过滤器查询方法中使用 TO_CHAR