create table ab(product_id int,product_name varchar(10), product_type_id varchar(10));
insert into ab values(1,'产品A','1,2'),(2,'产品B','2,3');
create table ac(product_type_id int,product_type_name varchar(10));
insert into ac values(1,'类别1'),(2,'类别2'),(3,'类别3');
select product_id,product_name,ab.product_type_id,group_concat(product_type_name) as typename from ab,ac where instr(ab.product_type_id,ac.product_type_id) group by product_id,product_name;
root @localhost : bbb 14:45:24>select * from ab;
+------------+--------------+-----------------+
| product_id | product_name | product_type_id |
+------------+--------------+-----------------+
| 1 | 产品A | 1,2 |
| 2 | 产品B | 2,3 |
+------------+--------------+-----------------+
2 rows in set (0.01 sec)
root @localhost : bbb 14:55:54>select * from ac;
+-----------------+-------------------+
| product_type_id | product_type_name |
+-----------------+-------------------+
| 1 | 类别1 |
| 2 | 类别2 |
| 3 | 类别3 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
root @localhost : bbb 14:55:56>select product_id,product_name,ab.product_type_id,group_concat(product_type_name) as typename from ab,ac where instr(ab.product_type_id,ac.product_type_id) group by product_id,product_name;
+------------+--------------+-----------------+-----------------+
| product_id | product_name | product_type_id | typename |
+------------+--------------+-----------------+-----------------+
| 1 | 产品A | 1,2 | 类别2,类别1 |
| 2 | 产品B | 2,3 | 类别2,类别3 |
+------------+--------------+-----------------+-----------------+
2 rows in set (0.01 sec)
root @localhost : bbb 14:56:00>
from:http://topic.csdn.net/u/20120913/15/22d39ecf-da3e-4088-a7d1-aa095cae4b7a.html
有两张表:
a: id name 1 tom 2 terry ... b: fid ids 1 1,2 ...
如果需要根据表b的ids列找出表a对应的name:
select name from a where find_in_set (id ,(select ids from b where fid = 1))
http://topic.csdn.net/u/20120921/16/3f940141-7d64-46b9-9a87-c0dbf4ed4ae9.html
GROUP BY Modifiers 官方手册里面对这个rollup有一个专门的页面介绍 地址在这里,说得非常详细,我这里做一个简单的例子重现
建一个简单的表并插入几条简单的数据
CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
insert into t valeu(11,11),(12,12),(13,13);
先来做一个查询
root@test 03:44:32>select id,sum(id2),avg(id2) from t group by id with rollup;
+——+———-+———-+
| id | sum(id2) | avg(id2) |
+——+———-+———-+
| 11 | 11 | 11.0000 |
| 12 | 12 | 12.0000 |
| 13 | 13 | 13.0000 |
| NULL | 36 | 12.0000 |
+——+———-+———-+
4 rows in set (0.00 sec)
我们可以看到,对于group by的列,with rollup将不会做任何的操作,而是返回一个NULL,而没有group by的列,则根据前面的avg函数和sum函数值产生的列做了统计处理。这样 group by + 聚合函数 统计了行数据,而 with rollup 产生了列数据,即生成了一张行列交错的统计报表。
mysql> select * from table1;
+----+------+
| id | num |
+----+------+
| 1 | 1001 |
| 2 | 1001 |
| 3 | 1001 |
| 4 | 1001 |
| 5 | 1002 |
| 6 | 1002 |
| 7 | 1001 |
| 8 | 1001 |
| 9 | 1002 |
+----+------+
9 rows in set (0.00 sec)
mysql> select num,
-> concat('连续',count(*) ,'次 从', min(sid) ,'至',eid) as cnt
-> from (
-> select a.id as sid,max(b.id) as eid,a.num
-> from table1 a , table1 b
-> where a.id<=b.id
-> and a.num=b.num
-> and not exists (select 1 from table1 where id between a.id and b.id and num!=a.num)
-> group by a.id
-> ) t
-> group by eid;
+------+----------------+
| num | cnt |
+------+----------------+
| 1001 | 连续4次 从1至4 |
| 1002 | 连续2次 从5至6 |
| 1001 | 连续2次 从7至8 |
| 1002 | 连续1次 从9至9 |
+------+----------------+
4 rows in set (0.00 sec)
mysql>
http://bbs.csdn.net/topics/390420571
数据如下:
id date fromId toId -------------------------------------- 1 2013-01-01 1 2 2 2013-01-02 2 1 3 2013-01-03 1 3 4 2013-01-04 3 1 5 2013-01-05 4 1 6 2013-01-06 1 4
如何才能查询出fromId或toId包含某个值,但fromId和toId不相互重复的数据? 例如,查询fromId或toId包含1,去除fromId和toId中数据互换的列,仅取日期最大的值,查询结果为:
id date fromId toId -------------------------------------- 2 2013-01-02 2 1 4 2013-01-04 3 1 6 2013-01-06 1 4
SELECT max(`date`),maxId,minId FROM (SELECT `date`,IF(fromId>toId,fromId,toId) AS maxId,IF(fromId>toId,toId,fromId) AS minId FROM `table`) AS `tmp` GROUP BY maxId,minId
http://segmentfault.com/q/1010000000191842
我有表table_1 name class score 张三 数学 80 张三 语文 70 李四 数学 70 李四 语文 80 一个sql查询出每个人每科的总分:
select name,
sum(CASE WHEN class ='数学' THEN score END) as `数学`,
sum(CASE WHEN class ='语文' THEN score END) as `语文`,
from table_1
where name='张三'
group by name
http://bbs.csdn.net/topics/390445500
id pid (tab) 33 0 55 52 54 52 52 0
结果:
id 子结点数 33 0 52 2
select id,(select count(*) from tab where pid=t.id) as 子结点数
from tab t
where pid=0
http://bbs.csdn.net/topics/390473594
abc表: 工程ID 项目 状态 ---------------------------------------------------------- 1 东部污水处理 正在进行中 1 建设路排水 未开始 1 东方广场 已完成 1 德明旅馆装修 已完成 2 创业大厦 已完成 2 星光酒店水电 已完成 3 丽冬广场 正在进行中 3 新世界广场 已完成 xyz表: 工程ID 全部完成 ------------------------------------------ 1 否 2 是 3 否 判断abc表同一工程ID下全部项目是否标记已完成,如果是,则update xyz表对应的列”全部完成“为”是“
update xyz inner join (select max(if(状态='已完成',0,1)) as K from abc group by 工程ID) b on xyz.工程ID=b.工程ID
set 全部完成=IF(b.K=0,'是','否')
http://bbs.csdn.net/topics/390478379
CREATE TABLE `goods` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`brand_id` varchar(5) NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL,
`click_count` varchar(10) NOT NULL DEFAULT '0',
`number` varchar(5) NOT NULL DEFAULT '0',
`salesnum` varchar(10) NOT NULL DEFAULT '0',
`desc` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;
INSERT INTO `goods` VALUES (901, '26', 'T恤---售馨', '939', '1', '', '');
INSERT INTO `goods` VALUES (783, '26', '雪纺上衣', '639', '5', '', '');
INSERT INTO `goods` VALUES (814, '26', '雪纺上衣', '1059', '19', '', '');
INSERT INTO `goods` VALUES (1577, '26', '短袖荷叶领', '647', '87', '', '');
INSERT INTO `goods` VALUES (1769, '26', '透气纯色纽扣上衣', '2451', '24', '', '');
INSERT INTO `goods` VALUES (1642, '26', ' 纯色短袖', '534', '64', '', '');
-- 注意 find_in_set 中的待匹配字符串不允许有空格
mysql> SELECT * FROM `goods` WHERE id IN ('783','769','814','1577','1769') ORDER BY FIND_IN_SET(id, '783,769,814,1577,1769' ) ;
+------+----------+--------------------------+-------------+--------+----------+------+
| id | brand_id | name | click_count | number | salesnum | desc |
+------+----------+--------------------------+-------------+--------+----------+------+
| 783 | 26 | 雪纺上衣 | 639 | 5 | | |
| 814 | 26 | 雪纺上衣 | 1059 | 19 | | |
| 1577 | 26 | 短袖荷叶领 | 647 | 87 | | |
| 1769 | 26 | 透气纯色纽扣上衣 | 2451 | 24 | | |
+------+----------+--------------------------+-------------+--------+----------+------+
4 rows in set (0.02 sec)
mysql> SELECT * FROM `goods` WHERE id IN ('783','769','814','1577','1769') ORDER BY FIND_IN_SET(id, '814,1577,1769,783,769' ) ;
+------+----------+--------------------------+-------------+--------+----------+------+
| id | brand_id | name | click_count | number | salesnum | desc |
+------+----------+--------------------------+-------------+--------+----------+------+
| 814 | 26 | 雪纺上衣 | 1059 | 19 | | |
| 1577 | 26 | 短袖荷叶领 | 647 | 87 | | |
| 1769 | 26 | 透气纯色纽扣上衣 | 2451 | 24 | | |
| 783 | 26 | 雪纺上衣 | 639 | 5 | | |
+------+----------+--------------------------+-------------+--------+----------+------+
4 rows in set (0.00 sec)
mysql>
http://bbs.csdn.net/topics/390528130
有两个表:
create table t1(
id int,
key1 int,
key2 int,
key3 int,
key4 int
);
insert into t1 values
(1, 112, 222, 333, null),
(2, 112, 222, 333, null),
(3, 134, 222, 333, 444 ),
(4, 134, 000, 333, 444 ),
(5, 178, 212, 312, 412 );
create table t2(
id int
);
insert into t2 values
(1),
(1),
(2),
(4),
(5);
我想要对该表进行信息的统计: 1)查询table2,id去重;得到id=1、2、4、5; 2)利用id与table1关联,并根据key1、key2、key3、key4进行统计:id=1和id=2的几个key都一样,因此这几个key为一组,且出现次数为2 示例结果: key1 key2 key3 key4 times 112 222 333 null 2 134 222 333 444 0 134 000 333 444 1 178 212 312 412 1
SELECT a.`key1`,a.`key2`,a.`key3`,a.`key4`,COUNT(DISTINCT b.id) FROM t1 a
LEFT JOIN t2 b ON a.`id`=b.`id`
GROUP BY a.`key1`,a.`key2`,a.`key3`,a.`key4`;
http://bbs.csdn.net/topics/390598979
类似的问题:有一个表:
create table tt(
id int,
`key` int,
type varchar(10)
);
insert into tt values
(123,11111, "first"),
(456,11111, "second"),
(456,11111, "second" ),
(789,22222, "second" ),
(890,22222, "second" ),
(456,22222, "first" );
我想要对该表进行信息的统计: 1)根据id要去重; 2)统计key的次数; 3)统计每个key对应的type的次数 示例结果: key times first_times second_times 11111 2 1 1 22222 3 1 2
select `key`,count(*),sum(if(type='first',1,0)),
sum(if(type='second',1,0))
from (
select distinct id,`key`,type from tt) a
group by `key`;
http://bbs.csdn.net/topics/390598827?page=1#post-395639648
DELIMITER $$
DROP PROCEDURE IF EXISTS tt$$
CREATE PROCEDURE tt()
BEGIN
SET @i=1;
SET @a=CONCAT(CURDATE(),' 00:00:00');
insert into lsb values(date_add(@a, interval 10 minute));
WHILE @i<=144 DO
insert into lsb values(date_add(@a, interval 10 minute));
SET @a=DATE_ADD(@a, INTERVAL 10 MINUTE);
SET @i=@i+1;
END WHILE;
END$$
DELIMITER ;
现在想按 26-31号,1-5号,6-10号,11-15号,16-20号,21-15号
这6个时间段内重量的总和 这个sql要怎么写?
要点:使用 TIMESTAMPDIFF 求得每个日期的所属区间范围,进而分组求值。
select TIMESTAMPDIFF(day,recdate,'2013-08-26') div 5,sum(weight)
from tb
group by TIMESTAMPDIFF(day,recdate,'2013-08-26') div 5
table 1 id table2_id 1 1,2,3 table 2 id value1 1 a 2 b 3 c 想要的是 id value 1 a,b,c
select table1.id,group_concat(table2.value1)
from table1,table2
where find_in_set(table2.id,table1.table2_id)
group by table1.id
http://bbs.csdn.net/topics/390706383
求出所有人中,这次考试比上次考试得分高的有几个 事例表如下 ID Name Score TestOrder 1 小明 90 1 2 小王 80 1 3 小红 80 1 4 小明 60 2 5 小王 90 2 6 小红 80 2
select A.*
from tb A,tb B
where A.name=B.name and A.testorder = B.testorder-1 and A.score>B.score;
或者:
select * from TABLE A
where exists (select 1 from TABLE where A.score> score and A.name = name and testorder = 2)
and A.testorder = 1;
CREATE TABLE user_test(
id int,
uid int
);
insert into user_test values(11,22),(22, 33),(44, 22),(11,22),(22, 33),(44, 22);
update user_test
set
id = case when id in (44) then 4444 else id end,
uid = case when uid in(22) then 2222 else uid end
where id > 0;
表 score,包含三个字段 stdno(int),subject(int),score(int) 分别表示 学号、科目、成绩 数据示例:1, 1, 50 表示学号为1的学生,第1个科目成绩为50 2,2,60 表示学号为2的学生,第2个科目的成绩为60 现在要求按照以下规则排序: 1、以科目3的成绩从大到小对学生进行排序 2、单个学生的记录按科目从小到大进行排序 排序后的结果如下: 5、1、xxx 5、2、xxx 5、3、100 5、4、xxx 2、1、xxx 2、2、xxx 2、3、99 2、4、xxx 8、1、xxx 8、2、xxx 8、3、98 8、4、xxx ...
select A.* from (
select stdno, score from score where subjectId = 3 order by score desc) B
inner join score A
on A.stdno = B.stdno order by B.score desc, A.subjectId asc;
select *
from score s
order by (select score from score where stdno=s.stdno and subject=3) desc, a.subject asc;
http://bbs.csdn.net/topics/390803079
mysql表如下:
CREATE TABLE `test` (
`id`int(11) NOT NULL auto_increment,
`cnt` double(15,3) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=167 DEFAULT CHARSET=utf8 PACK_KEYS=0;
我想在这张表里批量修改cnt字段,需要在这个字段的原值上加上某一个值。 如果只是修改一行记录,这样写就行了 update test set cnt = cnt+某值 where id = 某行id;
分2条SQL执行,
UPDATE、INSERT
或者:
INSERT INTO test4(id,cnt) VALUES
(167,2),
(4,3),
(6,5),
(7,9)
ON DUPLICATE KEY UPDATE id = VALUES(id), cnt= VALUES(cnt)+ cnt
http://bbs.csdn.net/topics/390840353
108.聚合去重查询优化:count(1) 在列为 Null 时的陷阱:
--谈谈分布式Aggregation
Q1:SELECT A, COUNT(DISTINCT B), SUM(C) FROM T GROUP BY A;
Q2:SELECT A, COUNT(1), SUM(SC) FROM
(SELECT A, COUNT(1), SUM(C) AS SC
FROM T GROUP BY A, B) TT GROUP BY A;
Q1和Q2的语义在B有null的时候并不等价,将Q2改为如下,这样两个查询的语义就是相同的了:
SELECT A, COUNT(B), SUM(SC) FROM (SELECT A, B, SUM(C) AS SC FROM T GROUP BY A, B) TT GROUP BY A
109.查询一段时间内的用户下单次数分布情况:
-- 首先计算每个用户的下单次数,然后使用 CASE..WHEN 语法来分组。
SELECT
CASE
WHEN c < 10 THEN '<10'
WHEN c < 20 THEN '<20'
WHEN c < 100 THEN '<100'
ELSE '>100'
END,
COUNT(*)
FROM (
SELECT user_id, COUNT(*) AS c FROM events
WHERE date BETWEEN '2015-09-01' AND '2015-09-20' AND event = 'SubmitOrder'
GROUP BY 1
)a
GROUP BY 1
110.查询做了行为 A 而没有做行为 B 的用户数
--使用 LEFT OUTER JOIN 计算差集。
SELECT a.user_id FROM (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'BuyGold'
) a
LEFT OUTER JOIN (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
) b
ON a.user_id = b.user_id
WHERE b.user_id IS NULL
111.计算用户的使用时长
--使用分析函数,根据每个用户相邻的两个事件的间隔估算累计使用时长,如果两次使用间隔超出10分钟则不计算。
SELECT
user_id,
SUM(
CASE WHEN
end_time - begin_time < 600
THEN
end_time - begin_time
ELSE
0
END
) FROM (
SELECT
user_id,
EXTRACT(EPOCH FROM time) AS end_time,
LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
FROM events
WHERE date='2015-5-1'
) a
GROUP BY 1
112.查询漏斗第 1 步的流失用户的使用时长
SELECT
AVG(
CASE WHEN
end_time - begin_time < 600
THEN
end_time - begin_time
ELSE
0
END
) FROM (
SELECT
user_id,
EXTRACT(EPOCH FROM time) AS end_time,
LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
FROM events
WHERE date='2015-5-1' and user_id in (funnel_user(12, '2015-05-01', '2015-05-01', '1.8', true, 0))
) a
来源: