问题
1. 查询条件不一样,返回多个结果整合 在查询的时候,有时候经常需要用到子查询,比如查询今天的订单数量, 7天的订单数量,31天的订单数量, 这种条件不一样,但是非要整在一个表中来显示就需要很多子查询来实现了:select(select xx from xx where 条件一) as 结果一,select(select xx from xx where 条件二) as 结果二,select(select xx from xx where 条件三) as 结果三这个时候我们需要在一条sql将这三个结果查出来,可以使用UNION和UNION ALL
UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行
也就是我们可以拼成一个表,比如现在有这么一个表:
如果我们需要分别查出年龄小于18和大于18的人数,由于查询条件不一样,所以需要用到两个查询SELECT COUNT(*) FROM USER WHERE age < 18 ;SELECT COUNT(*) FROM USER WHERE age >= 18 ;使用UNION:
SELECT * FROM (SELECT COUNT(*) FROM USER WHERE age < 18 UNION SELECT COUNT(*) FROM USER WHERE age >= 18) a但是查出来的结果是在一列里面,无法得知哪个是18以下的
我们可以拼接一个0使其为两个字段(两列),这里注意两条子查询的别名都要一样
SELECT * FROM (SELECT COUNT(*) AS '18以下人数', 0 AS '18以上人数' FROM USER WHERE age < 18 UNION SELECT 0 AS '18以下人数', COUNT(*) AS '18以上人数' FROM USER WHERE age >= 18) a
这个时候是两条记录,分别使用SUM得到总数就可以变成一条记录了
2. 查询条件一样,返回多个结果整合
但是有时候, 几个结果的条件是一样的, 但是子查询只能返回一个结果, 也就是我们需要分成多个子查询来查询相同的条件SELECT(SELECT xx FROM xx WHERE 条件一) AS 结果一,SELECT(SELECT xx FROM xx WHERE 条件一) AS 结果二,SELECT(SELECT xx FROM xx WHERE 条件一) AS 结果三,SELECT(SELECT xx FROM xx WHERE 条件二) AS 结果四处理 在网上搜罗一圈后, 有一个解决办法挺新颖的, 大概就是先把同样条件的多个结果, 先拼接成一个, 再在外面进行拆分, 在java里面比如是"aa,bb,cc".split(",")
select SUBSTRING_INDEX(temp.结果一,',',1) as 第一列数据,SUBSTRING_INDEX(SUBSTRING_INDEX(temp.结果一,','2),',',-1) as 第二列数据,SUBSTRING_INDEX(temp.结果一,','-1) as 第三列数据,temp.结果二 as 第四列数据FROM( select(select concat_ws(',',数据一, 数据二,数据三) from xx where 条件一) as 结果一, select(select xx from xx where 条件二) as 结果二)temp [补充]关于substring_index的用法 SUBSTRING_INDEX('待切割的字符串', '截取分割的字符,比如逗号' , 长度) 长度为负数,从右边开始, 比如"a,b,c,d", 长度为-1取得是"d"(可以理解为在java中,利用split分割之后,取数组中的前几个元素) 取a: SUBSTRING_INDEX("a,b,c,d" , "," , 1)取b: SUBSTRING_INDEX(SUBSTRING_INDEX("a,b,c,d" , "," , 2) ,",",-1) 先拿到前2位,再取最后一位取d: SUBSTRING_INDEX("a,b,c,d" , "," , -1)取cd: SUBSTRING_INDEX("a,b,c,d" , "," , -2) 示例1: SELECT SUBSTRING_INDEX(temp.today, ",", 1) AS `今日加购量`, SUBSTRING_INDEX(temp.today, "," ,- 1) AS `今日加购商家数`FROM ( SELECT CONCAT_WS( ',', COUNT(*), COUNT(DISTINCT c.MALL_STORE_ID) ) AS today FROM mall_goods_cart c WHERE STR_TO_DATE(c.CREATE_TIME, '%Y-%m-%d') = CURDATE() ) AS temp
mysql日期函数函数:
SELECT NOW(),CURDATE(),CURTIME()
| NOW() | CURDATE() | CURTIME() |
|---|---|---|
| 2008-12-29 16:25:46 | 2008-12-29 | 16:25:46 |
示例2:需求：关联查询另一个大表数据的某些（一个以上）字段
方案：因关联查询的表数据太大。多表查询影响效率，单个子查询又有些多余。所以采用多列拼接子查询，然后根据SUBSTRING_INDEX(SOURCE,SEPARETOR,INDEX+1)。
原理：子查询返回拼接列；函数截取还原列
SELECTSUBSTRING_INDEX(temp.temp_column,",",1) showFirst,SUBSTRING_INDEX(temp.temp_column,",",-1) showOver,SUBSTRING_INDEX(SUBSTRING_INDEX(temp.temp_column,',',2),',',-1) showTwo,temp.*FROM( SELECT ( SELECT CONCAT_WS(',','12','23','34','45')) temp_column) temp; 当然,如果仅仅只是在返回的多行数据之中随便抽取一列即可,可以使用limit,,找到一个符合条件的就可以了
select * from table1 where table1.colums=(select columns from table2 limit 1);
或者使用any函数:
select * from table1 where table1.colums=any(select columns from table2);