数据分组查询

wwx840723 2015-04-16 05:26:10
有如下表A：
name product_id model Material
TYLER 1 Armband for phone A-AB00P133A-FT01
TYLER 2 Armband for phone A-AB00P133A-FT02
TYLER 3 Armband for phone A-AB00P133A-FT03
TYLER 7 Armband for phone A-AB00P146A-FT01
TYLER 8 Armband for phone A-AB00P146A-FT02
TYLER 9 Armband for phone A-AB00P146A-FT03
TYLER 49 Armband for phone A-AB00P162A-FT01
TYLER 50 Armband for phone A-AB00P162A-FT02
HIPPO 85 Power for Home & Office A-AD00-0002-EU
HIPPO 86 Power for Home & Office A-AD00-0002-ES
KUGA 88 Power for Home & Office A-AD00-0202-EU
WOLF 91 Power for Home & Office A-AD00-0402-BS


我想根据字段Material和model、name同时分组，条件：group by substring(Material,1,length(Material)-2),model,name
但却没有想要的效果。

想要的结果如下，SQL如何写呢？请帮忙，感谢。
name product_id model Material
TYLER 1 Armband for phone A-AB00P133A-FT01
TYLER 7 Armband for phone A-AB00P146A-FT01
TYLER 49 Armband for phone A-AB00P162A-FT01
HIPPO 85 Power for Home & Office A-AD00-0002-EU
KUGA 88 Power for Home & Office A-AD00-0202-EU
WOLF 91 Power for Home & Office A-AD00-0402-BS
...全文
205 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
wwx840723 2015-04-22
  • 打赏
  • 举报
 回复
谢谢版主、 我再琢磨下。
ACMAIN_CHM 2015-04-17
  • 打赏
  • 举报
 回复
引用 4 楼 wwx840723 的回复:
谢谢，为啥我的那种写法不行呢？
至少请楼主把你的SQL语句贴完整出来吧。另外不行是指什么？ 产生的结果什么样？ 问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）
wwx840723 2015-04-17
  • 打赏
  • 举报
 回复
谢谢，为啥我的那种写法不行呢？
引用 2 楼 ACMAIN_CHM 的回复:
mysql> select * from ta;
+-------+------------+-------------------------+------------------+
| name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          2 | Armband for phone       | A-AB00P133A-FT02 |
| TYLER |          3 | Armband for phone       | A-AB00P133A-FT03 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |          8 | Armband for phone       | A-AB00P146A-FT02 |
| TYLER |          9 | Armband for phone       | A-AB00P146A-FT03 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| TYLER |         50 | Armband for phone       | A-AB00P162A-FT02 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| HIPPO |         86 | Power for Home & Office | A-AD00-0002-ES   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
12 rows in set (0.00 sec)

mysql> select *
    -> from ta
    -> group by name,model,left(Material,length(Material)-2)
    -> order by product_id;
+-------+------------+-------------------------+------------------+
| name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
6 rows in set (0.00 sec)

mysql>
WWWWA 2015-04-17
  • 打赏
  • 举报
 回复 
mysql> SELECT * FROM TTLA;
+-------+------------+-------------------------+------------------+
| Name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          2 | Armband for phone       | A-AB00P133A-FT02 |
| TYLER |          3 | Armband for phone       | A-AB00P133A-FT03 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |          8 | Armband for phone       | A-AB00P146A-FT02 |
| TYLER |          9 | Armband for phone       | A-AB00P146A-FT03 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| TYLER |         50 | Armband for phone       | A-AB00P162A-FT02 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| HIPPO |         86 | Power for Home & Office | A-AD00-0002-ES   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM TTLA A WHERE NOT EXISTS(
    -> SELECT 1 FROM TTLA WHERE A.`Name`=`Name`
    -> AND A.`model`=`model`
    -> AND SUBSTRING(A.Material,1,LENGTH(A.Material)-2)=
    -> SUBSTRING(Material,1,LENGTH(Material)-2)
    -> AND A.`product_id`>`product_id`
    -> );
+-------+------------+-------------------------+------------------+
| Name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
6 rows in set (0.00 sec)

mysql>
ACMAIN_CHM 2015-04-16
  • 打赏
  • 举报
 回复 
mysql> select * from ta;
+-------+------------+-------------------------+------------------+
| name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          2 | Armband for phone       | A-AB00P133A-FT02 |
| TYLER |          3 | Armband for phone       | A-AB00P133A-FT03 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |          8 | Armband for phone       | A-AB00P146A-FT02 |
| TYLER |          9 | Armband for phone       | A-AB00P146A-FT03 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| TYLER |         50 | Armband for phone       | A-AB00P162A-FT02 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| HIPPO |         86 | Power for Home & Office | A-AD00-0002-ES   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
12 rows in set (0.00 sec)

mysql> select *
    -> from ta
    -> group by name,model,left(Material,length(Material)-2)
    -> order by product_id;
+-------+------------+-------------------------+------------------+
| name  | product_id | model                   | Material         |
+-------+------------+-------------------------+------------------+
| TYLER |          1 | Armband for phone       | A-AB00P133A-FT01 |
| TYLER |          7 | Armband for phone       | A-AB00P146A-FT01 |
| TYLER |         49 | Armband for phone       | A-AB00P162A-FT01 |
| HIPPO |         85 | Power for Home & Office | A-AD00-0002-EU   |
| KUGA  |         88 | Power for Home & Office | A-AD00-0202-EU   |
| WOLF  |         91 | Power for Home & Office | A-AD00-0402-BS   |
+-------+------------+-------------------------+------------------+
6 rows in set (0.00 sec)

mysql>
wwx840723 2015-04-16
  • 打赏
  • 举报
 回复
就是先通过字段Material（剔除最后2位）分组，然后再根据name和model来分组。 求大牛帮忙。谢谢。
mysql千万级数据分组查询_mysql千万级数据查询
1. mysql 的数据查询 , 大小字段要分开 , 这个还是有必要的 , 除非一点就是你查询的都是索引内容而不是表内容 , 比如只查询 id 等等 2. 查询速度和索引有很大关系也就是索引的大小直接影响你的查询效果 , 但是查询...
mysql千万级数据分组查询_mysql千万级数据量查询出所有重复的记录
但是有些情况下查询缓慢，则需要使用下面其他的方法以下是本人整理出来的查询重复数据的SQL，适用于千万级数据量的MYSQL数据库。1.适用于整数类型的SELECT DISTINCT UId FROM vehicle WHERE 1=1 GROUP B...
EF数据分组查询
由于将mysql的数据进行分组显示在树形结构中。所以使用了下面的查询方式。由于数据库设计的不好，感觉代码比较乱 public string Get() { string result = ""; using (var edm = new wlcloudEntities()) { ...
【数据库设计和SQL基础语法】--查询数据--分组查询
分组查询是一种 SQL 查询技术，通过使用 GROUP BY 子句，将具有相同值的数据行分组在一起，然后对每个组应用聚合函数（如 COUNT、SUM、AVG等）。这允许在数据集中执行汇总和统计操作，以便更清晰地理解和分析数据的...
oracle数据库怎么分组查询,oracle数据库之分组查询
本章内容和大家分享的是数据当中的分组查询。分组查询复杂一点的是建立在多张表的查询的基础之上，(我们在上一节课的学习中已经给大家分享了多表查询的使用技巧，大家可以自行访问：多表查询1 多表查询2)而在实际...
MySQL

56,940

社区成员

56,758

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章