以日期分隔的Mysql查询元素组

Im currenty crwaling a website and saving to a DB all the images from a carousel. each of this images have a position, link, image url, etc.

findle with all the info: sqlfiddle.com/#!2/b3920b/2/0 (thanks to Ollie Jones)

So de DB looks kinda like this:

╔═══╦════════════╦═════════════╦═════════════╦═════════════╦═════════════╦═════════════╗
 ║id ║  element   ║  position   ║   title     ║  url_link   ║   url_jpg   ║     date    ║
 ╠═══╬════════════╬═════════════╬═════════════╬═════════════╬═════════════╬═════════════╣
 ║ 1 ║ foo        ║      1      ║    title1   ║     url1    ║    jpg1     ║  20-12-2013 ║
 ║ 2 ║ foo        ║      2      ║    title2   ║     url2    ║    jpg2     ║  20-12-2013 ║
 ║ 3 ║ foo        ║      3      ║    title3   ║     url3    ║    jpg3     ║  20-12-2013 ║
 ║ 4 ║ foo        ║      4      ║    title4   ║     url4    ║    jpg4     ║  20-12-2013 ║
 ║ 5 ║ foo        ║      5      ║    title5   ║     url5    ║    jpg5     ║  20-12-2013 ║
 ║ 6 ║ foo        ║      6      ║    title6   ║     url6    ║    jpg6     ║  20-12-2013 ║
 ║ 7 ║ foo        ║      1      ║    title1   ║     url1    ║    jpg1     ║  21-12-2013 ║
 ║ 8 ║ foo        ║      2      ║    title2   ║     url2    ║    jpg2     ║  21-12-2013 ║
 ║ 9 ║ foo        ║      3      ║    title3   ║     url3    ║    jpg3     ║  21-12-2013 ║
 ║10 ║ foo        ║      4      ║    title4   ║     url4    ║    jpg4     ║  21-12-2013 ║
 ║11 ║ foo        ║      5      ║    title5   ║     url5    ║    jpg5     ║  21-12-2013 ║
 ║12 ║ foo        ║      6      ║    title6   ║     url6    ║    jpg6     ║  21-12-2013 ║
 ║13 ║ foo        ║      1      ║    title1   ║     url1    ║    jpg1     ║  22-12-2013 ║
 ║14 ║ foo        ║      2      ║    title2   ║     url2    ║    jpg2     ║  22-12-2013 ║
 ║15 ║ foo        ║      3      ║    title3   ║     url3    ║    jpg3     ║  22-12-2013 ║
 ║16 ║ foo        ║      4      ║    title4   ║     url4    ║    jpg4     ║  22-12-2013 ║
 ║17 ║ foo        ║      5      ║    title5   ║     url5    ║    jpg5     ║  22-12-2013 ║
 ║18 ║ foo        ║      6      ║    title6   ║     url6    ║    jpg6     ║  22-12-2013 ║
 ║.. ║    ...     ║     ...     ║     ...     ║     ...     ║     ...     ║      ...    ║
 ╚═══╩════════════╩═════════════╩═════════════╩═════════════╩═════════════╩═════════════╝

And then in my reports, I query the database to show me what element were present in the carousel with start date and end date, if there is a change in position, title, url of the link of jpg then it doest have to show me any range... for instance, the query of the DB before should look like:

 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦══════════════════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║           date           ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬══════════════════════════╣
 ║ foo     ║      1    ║    title1   ║     url1    ║    jpg1   ║ 20-12-2013 to 22-12-2013 ║
 ║ foo     ║      2    ║    title2   ║     url2    ║    jpg2   ║ 20-12-2013 to 22-12-2013 ║
 ║ foo     ║      3    ║    title3   ║     url3    ║    jpg3   ║ 20-12-2013 to 22-12-2013 ║
 ║ foo     ║      4    ║    title4   ║     url4    ║    jpg4   ║ 20-12-2013 to 22-12-2013 ║
 ║ foo     ║      5    ║    title5   ║     url5    ║    jpg5   ║ 20-12-2013 to 22-12-2013 ║
 ║ foo     ║      6    ║    title6   ║     url6    ║    jpg6   ║ 20-12-2013 to 22-12-2013 ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩══════════════════════════╝

The above uses this query:

mysql_query("
 SELECT id, element, position, title, url_link, url_jpg, 
        (case when min(date) <> max(date) 
         then concat(min(date), ' to ', max(date)) 
         else min(date) end) as date
    FROM table 
   GROUP BY element, posistion, title, url_link, url_jpg 
   ORDER BY element, date ASC, position ASC
")

Now the problem is that when all elements change BUT one, like this:

new DB:

 ╔═══╦════════════╦═════════════╦═════════════╦═════════════╦═════════════╦═════════════╗
 ║id ║  element   ║  position   ║   title     ║  url_link   ║   url_jpg   ║     date    ║
 ╠═══╬════════════╬═════════════╬═════════════╬═════════════╬═════════════╬═════════════╣
 ║ 1 ║ foo        ║      1      ║    title1   ║     url1    ║    jpg1     ║  20-12-2013 ║
 ║ 2 ║ foo        ║      2      ║    title2   ║     url2    ║    jpg2     ║  20-12-2013 ║
 ║ 3 ║ foo        ║      3      ║    title3   ║     url3    ║    jpg3     ║  20-12-2013 ║
 ║ 4 ║ foo        ║      4      ║    title4   ║     url4    ║    jpg4     ║  20-12-2013 ║
 ║ 5 ║ foo        ║      5      ║    title5   ║     url5    ║    jpg5     ║  20-12-2013 ║
 ║ 6 ║ foo        ║      6      ║    title6   ║     url6    ║    jpg6     ║  20-12-2013 ║
 ║ 7 ║ foo        ║      1      ║    title1   ║     url1    ║    jpg1     ║  21-12-2013 ║
 ║ 8 ║ foo        ║      2      ║  newtitle2  ║     url2    ║    jpg2     ║  21-12-2013 ║
 ║ 9 ║ foo        ║      3      ║  newtitle3  ║     url3    ║    jpg3     ║  21-12-2013 ║
 ║10 ║ foo        ║      4      ║  newtitle4  ║     url4    ║    jpg4     ║  21-12-2013 ║
 ║11 ║ foo        ║      5      ║  newtitle5  ║     url5    ║    jpg5     ║  21-12-2013 ║
 ║12 ║ foo        ║      6      ║  newtitle6  ║     url6    ║    jpg6     ║  21-12-2013 ║
 ║13 ║ foo        ║      1      ║     title1  ║     url1    ║    jpg1     ║  22-12-2013 ║
 ║14 ║ foo        ║      2      ║  newtitle2  ║     url2    ║    jpg2     ║  22-12-2013 ║
 ║15 ║ foo        ║      3      ║  newtitle3  ║     url3    ║    jpg3     ║  22-12-2013 ║
 ║16 ║ foo        ║      4      ║  newtitle4  ║     url4    ║    jpg4     ║  22-12-2013 ║
 ║17 ║ foo        ║      5      ║  newtitle5  ║     url5    ║    jpg5     ║  22-12-2013 ║
 ║18 ║ foo        ║      6      ║  newtitle6  ║     url6    ║    jpg6     ║  22-12-2013 ║
 ║.. ║    ...     ║     ...     ║     ...     ║     ...     ║     ...     ║      ...    ║
 ╚═══╩════════════╩═════════════╩═════════════╩═════════════╩═════════════╩═════════════╝

The same query will show me:

 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦═════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║   date      ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬═════════════╣
 ║ foo     ║      2    ║    title2   ║     url2    ║    jpg2   ║ 20-12-2013  ║
 ║ foo     ║      3    ║    title3   ║     url3    ║    jpg3   ║ 20-12-2013  ║
 ║ foo     ║      4    ║    title4   ║     url4    ║    jpg4   ║ 20-12-2013  ║
 ║ foo     ║      5    ║    title5   ║     url5    ║    jpg5   ║ 20-12-2013  ║
 ║ foo     ║      6    ║    title6   ║     url6    ║    jpg6   ║ 20-12-2013  ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩═════════════╝
 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦══════════════════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║           date           ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬══════════════════════════╣
 ║ foo     ║      1    ║    title1   ║     url2    ║    jpg2   ║ 20-12-2013 to 22-12-2013 ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩══════════════════════════╝
 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦══════════════════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║           date           ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬══════════════════════════╣
 ║ foo     ║      2    ║    title2   ║     url2    ║    jpg2   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      3    ║    title3   ║     url3    ║    jpg3   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      4    ║    title4   ║     url4    ║    jpg4   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      5    ║    title5   ║     url5    ║    jpg5   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      6    ║    title6   ║     url6    ║    jpg6   ║ 21-12-2013 to 22-12-2013 ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩══════════════════════════╝

And I want it to show me this:

 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║  date      ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬════════════╣
 ║ foo     ║      1    ║    title1   ║     url1    ║    jpg1   ║ 20-12-2013 ║
 ║ foo     ║      2    ║    title2   ║     url2    ║    jpg2   ║ 20-12-2013 ║
 ║ foo     ║      3    ║    title3   ║     url3    ║    jpg3   ║ 20-12-2013 ║
 ║ foo     ║      4    ║    title4   ║     url4    ║    jpg4   ║ 20-12-2013 ║
 ║ foo     ║      5    ║    title5   ║     url5    ║    jpg5   ║ 20-12-2013 ║
 ║ foo     ║      6    ║    title6   ║     url6    ║    jpg6   ║ 20-12-2013 ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩════════════╝

 ╔═════════╦═══════════╦═════════════╦═════════════╦═══════════╦══════════════════════════╗
 ║element  ║  position ║   title     ║  url_link   ║   url_jpg ║           date           ║
 ║═════════╬═══════════╬═════════════╬═════════════╬═══════════╬══════════════════════════╣
 ║ foo     ║      1    ║    title1   ║     url1    ║    jpg1   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      2    ║ newtitle2   ║     url2    ║    jpg2   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      3    ║ newtitle3   ║     url3    ║    jpg3   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      4    ║ newtitle4   ║     url4    ║    jpg4   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      5    ║ newtitle5   ║     url5    ║    jpg5   ║ 21-12-2013 to 22-12-2013 ║
 ║ foo     ║      6    ║ newtitle6   ║     url6    ║    jpg6   ║ 21-12-2013 to 22-12-2013 ║
 ╚═════════╩═══════════╩═════════════╩═════════════╩═══════════╩══════════════════════════╝

(To separate them into tables in the php file I make a new table when date changes)

So I have no idea how could I change the query so the element with title1 separates into the two tables since there was a change in the GROUP date. :(

Hope is well explain, this is really giving me a headache. thank you in advance.

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

1条回答默认最新

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
dtio35880438 2014-01-09 00:03
关注
I believe the problem you are seeing is due to ORDERING problems with your resultset's date column. My Fiddle (here) uses actual DATE values, not dd-mm-yyyy values, because we want them to sort correctly. http://sqlfiddle.com/#!2/b3920b/4/0 Also, I'm using this

ORDER BY element, MIN(date), MAX(date), position ASC

Notice that rows 1, 7, 13 of your second dataset will GROUP together into a single result row with a date range of 2013-12-20 to 2013-12-22.

There's a minor mistake in your SQL.. you don't want to

SELECT id, ....

because it's not mentioned in your GROUP BY. See here: http://sqlfiddle.com/#!2/b3920b/3/0
解决无用
评论打赏
分享
举报

评论

按下Enter换行，Ctrl+Enter发表内容

报告相同问题？

关注问题

按月分隔MySQL查询结果 mysql php
2014-09-22 14:11

回答 2 已采纳 I assume your table report is for just one year. If so, you can just use the month as an index key
Python将字符串以分隔符分隔为列表元素 python
2022-05-17 16:37

回答 8 已采纳先用text.replace('\n\n', ',')把换行符换成逗号，再用text.split(',')用逗号作为分隔符。
python 批量插入mysql 如何把list分隔开来 mysql python
2019-04-10 20:37

回答 2 已采纳第26行，`listxixi.append(aa+bb+cc+dd)`，改成`listxixi.append([aa, bb, cc, dd])`。你的sql写的也有点问题，前面五个字段，后面四个占位
《大数据面试题》面试大数据这一篇就够了
2020-05-05 09:23

abluer~的博客《大数据面试题》面试大数据这一篇就够了 Hadoop 常见面试题 Hive 常见面试题 Spark 常见面试题 Flume 常见面试题 Kafka 常见面试题 Hbase 常见面试题 Redis 20 问
在mysql选择查询中匹配分隔符（|）分隔值字段 database mysql php
2013-05-06 10:47

回答 2 已采纳 Use FIND_IN_SET select id from your_table where find_in_set('3', replace(skills, '|',',') > 0
在单个查询中搜索逗号分隔的MySQL列中的多个值 mysql php sql
2013-10-14 06:13

回答 2 已采纳 After the very useful post by rakeshjain, I managed to transform my query into this: SELECT * FRO
特别是从mysql数据库中获取逗号分隔值 mysql php sql
2017-11-13 06:37

回答 1 已采纳 Get first all id's,by column, and then explode it by comma, $explode = explode(',',$array->col
史上最详细大数据基础知识
2023-03-11 17:25

djyjx的博客 大数据知识详解
使用以搜索表分隔的Ajax数据逗号构建SQL查询 ajax jquery mysql php
2018-10-06 07:30

回答 1 已采纳 You need to specify an object to $.ajax for the data parameter, if you specify a string (as you ha
MYSQL查询多个逗号分隔的字符串 mysql php
2012-11-22 17:39

回答 1 已采纳 That would be too complicated with SQL (and doubt it's possible even) I'd do the following - Get
子句mysql中逗号分隔的字符串IN数组 mysql php
2015-03-02 19:30

回答 3 已采纳 You need to explode() your string and then implode() it - $myarray = 'zzz,aaa,bbb'; $realArray
大数据开发面试知识点复习1
2022-04-25 15:24

爱敲代码的小黑的博客文章目录大数据开发复习课程课程目标1、大数据开发复习2、面试流程2.1、面试准备2.2、准备简历2.3、投递简历2.4、面试2.5、入职3、基础复习3.1、idea3.11、idea常用快捷键3.12、idea常见问题3.2、Java3.2.1、Java中...
如何在MySQL中以单行的形式获取以逗号分隔格式存储的图像？ mysql php
2018-05-07 08:45

回答 4 已采纳 <?php $x=0; $car_img =explode(",",$car_imag
大数据开发面试知识点总结
2021-02-09 11:22

GoAI的博客本文详细介绍大数据hadoop生态圈各部分知识，包括不限于hdfs、yarn、mapreduce、hive、sqoop、kafka、flume、spark、flink等技术，总结内容适合大数据开发者学习，希望能够和大家多多交流。
java大数据开发训练营--mysql数据库JDBC
2020-08-07 20:35

Laozizuiku的博客文章内容输出来源：拉勾教育大数据开发高薪训练营本篇文章是java学习课程中的一部分笔记。本博文主要是记录一些基础的知识点，通过实操更容易理解这章主要讲的是mysql的使用， JDBC JDBC 概述什么是JDBC ...
没有解决我的问题, 去提问

悬赏问题

¥170 如图所示配置eNSP
¥20 docker里部署springboot项目，访问不到扬声器
¥15 netty整合springboot之后自动重连失效
¥15 悬赏！微信开发者工具报错，求帮改
¥20 wireshark抓不到vlan
¥20 关于#stm32#的问题：需要指导自动酸碱滴定仪的原理图程序代码及仿真
¥20 设计一款异域新娘的视频相亲软件需要哪些技术支持
¥15 stata安慰剂检验作图但是真实值不出现在图上
¥15 c程序不知道为什么得不到结果
¥15 键盘指令混乱情况下的启动盘系统重装

以日期分隔的Mysql查询元素组

1条回答 默认 最新

悬赏问题

1条回答默认最新