所以m.zam得出了答案。非常感谢。有人能再进一步吗?这是两张桌子。我现在有两件事要做,用"新歌"替换返回的空日期,我想知道自从一首歌上次播放以来,已经有多少节目了。
song_id song_name
51 | Song Name A
368 | Song Name B
168 | Song Name C
568 | Song Name D
13 | Song Name E
song_id | song_order| show_date |show_id
368 | 1 | 2010-02-17 00:00:00 | 367
368 | 0 | 2012-04-06 00:00:00 | 499
51 | 2 | 2012-01-19 00:00:00 | 399
51 | 2 | 2013-04-24 00:00:00 | 870
51 | 8 | 2013-07-19 00:00:00 | 899
368 | 2 | 2013-07-19 00:00:00 | 899
13 | 5 | 2013-07-19 00:00:00 | 899
568 | 2 | 2013-07-19 00:00:00 | 899
368 | 4 | 2012-06-08 00:00:00 | 799
168 | 2 | 2013-06-28 00:00:00 | 896
568 | 2 | 2013-07-03 00:00:00 | 897
568 | 2 | 2010-02-21 00:00:00 | 897
我下面的代码正在返回这个,但我需要更多的数据。我想把第一张桌子变成第二张。提前感谢!代码可在此处找到:http://sqlfiddle.com/#!2/f3e29/1
首次
SHOW_DATE | SHOW_ID | SONG_NAME | SONG_ID | PREVDATE |
July, 19 2013 | 899 | Song Name A | 51 | April, 24 2013 |
July, 19 2013 | 899 | Song Name B | 368 | June, 08 2012 |
July, 19 2013 | 899 | Song Name E | 13 | (null) |
July, 19 2013 | 899 | Song Name D | 568 | July, 03 2013 |
第二
SHOW_DATE | SHOW_ID | SONG_NAME | SONG_ID | PREVDATE | SHOWS SINCE
July, 19 2013 | 899 | Song Name A | 51 | April, 24 2013 | 2
July, 19 2013 | 899 | Song Name B | 368 | June, 08 2012 | 4
July, 19 2013 | 899 | Song Name E | 13 | NEW SONG | 0
July, 19 2013 | 899 | Song Name D | 568 | July, 03 2013 | 0
SELECT a.show_date, a.show_id,
b.song_name, a.song_id, (
SELECT
IFNULL(MAX(show_date),'NEW SONG')
FROM tbl_shows AS c
WHERE a.show_date > c.show_date and a.song_id = c.song_id
) As PrevDate
FROM tbl_shows a, tbl_songs b
WHERE a.song_id = b.song_id
AND a.show_id = 899
虽然IFNULL在SQL中有效,但我使用PHP将返回日期的格式更改为类似mm/dd/yy的格式,我可以在将其发送到PHP之前更改SQL中的日期吗?或者在PHP中编写一个IFELSE语句以返回mm/dd/y或"NEW SONG"
echo "<td style='padding: 10px; width:45px;'>" .date("m/d/y",
strtotime($row["PrevDate"]))." </td>";
第一部分(新歌)试试这个
SELECT a.show_date, a.show_id,
b.song_name, a.song_id,
(
SELECT IFNULL(MAX(show_date),'NEW SONG')
FROM tbl_shows AS c
WHERE a.show_date > c.show_date and a.song_id = c.song_id
) As PrevDate
FROM tbl_shows a, tbl_songs b
WHERE a.song_id = b.song_id
AND a.show_id = 899