这是我的表格示例:
+-----+-----+------------+--------+-------------+--------------+
| LID | AID | Created | TypeID | PaymentDate | PaymentValue |
+-----+-----+------------+--------+-------------+--------------+
| 1 | 529 | 2017-05-12 | 1 | 2017-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 2 | 529 | 2018-04-10 | 4 | 2018-04-10 | 200 |
+-----+-----+------------+--------+-------------+--------------+
| 3 | 441 | 2014-01-23 | 3 | 2014-01-23 | 300 |
+-----+-----+------------+--------+-------------+--------------+
| 4 | 324 | 2017-09-14 | 1 | 2017-09-14 | 400 |
+-----+-----+------------+--------+-------------+--------------+
| 5 | 111 | 2018-05-12 | 0 | 2018-05-12 | 340 |
+-----+-----+------------+--------+-------------+--------------+
| 6 | 529 | 2018-05-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 7 | 529 | 2018-06-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 8 | 529 | 2018-07-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 9 | 529 | 2018-08-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 10 | 529 | 2018-09-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 11 | 529 | 2018-01-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 12 | 529 | 2018-05-14 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 13 | 529 | 2018-05-21 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
| 14 | 529 | 2018-03-12 | 1 | 2018-05-12 | 100 |
+-----+-----+------------+--------+-------------+--------------+
这是另一个表格
+-----+-------+
| ID |caption|
+-----+-------+
| 0 | bad |
+-----+-------+
| 1 | good |
+-----+-------+
我需要为每个 AID 获取 10 条最新记录。如果某些 AID 的记录少于 10 条,我需要获取十行并将"无付款日期"放入"付款日期"和"已创建"字段中,将 Null 放入 TypeID 中,将 0 放入付款值。我可以得到 10 条或更少的最新记录
select *
from (select *,
(@rn := if(@c = AID, @rn + 1,
if(@c := AID, 1, 1)
)
) as rn
from history cross join
(select @rn := 0, @c := -1) params
order by AID, Created desc
) t
having rn <= 10;
但我不知道如何强制 mysql 为每个 AID 输出 10 行。请帮帮我。
结果应采用表单
AID,类型 ID,已创建,标题
我已经做到了。此查询需要创建一行包含 10 条记录,以便与表中的不同 AID 值合并。 我能够显示金额和创建日期的结果,并将其留给您继续,因为您会明白这个想法。关键部分是构建一个具有 10 行乘以不同 AID 的表,因此表 r 中大约有 40 行。然后对表 t 进行左连接,这与你所做的类似。表 t 获得最多 10 条记录的排名。任何缺失的排名最多 10 个 recs 将由表 r 填充。Coalesce 将为日期分配默认值,例如 0 fro 金额和"无创建日期"。
http://sqlfiddle.com/#!9/855c21/2
SELECT coalesce(r.aid, t.aid) as aid,
coalesce(t.paymentvalue, 0) as paymentvalue,
coalesce(cast(t.created as char), 'no create date') as created
FROM (select * from (
select 1 as rw union
select 2 union select 3
union select 4 union select 5
union select 6 union select 7
union select 8 union select 9
union select 10) u
cross join (select distinct aid
from history) h
) as r
LEFT JOIN (
SELECT a.aid, a.paymentvalue,
a.created, count(*) rn
FROM history a
JOIN history b
ON a.aid = b.aid
AND a.created <= b.created
GROUP BY a.aid, a.created
HAVING COUNT(*) <= 10) t
on r.rw=t.rn and r.aid=t.aid
order by aid, created;
我添加了RIGHT JOIN
以引入空行以每AID
最多增加 10(或 n(行。 最初,我使用 SELECT 1 UNION SELECT 2 ...
来生成 10 行。为了更容易地增加行数(比如 100 行(,我正在尝试generate_series mysql 等效的想法。为此,表中history
行数必须大于每个AID
所需的行数。
select t1.lid
,t2.aid
,coalesce(t1.created, "no created date") as created
,t1.typeID
,coalesce(t1.paymentdate, "no payment date") as paymentDate
,coalesce(t1.paymentvalue, 0) as paymentValue
,t2.rn
from
(
select *,
(@rn := if(@c = AID, @rn + 1,
if(@c := AID, 1, 1)
)
) as rn
from history cross join
(select @rn := 0, @c := -1) params
order by AID, Created desc
) t1
right join
( select *
from (select distinct aid from history ) h1
cross join
(select rn -- generate table with n rows numbered from 1 to n
from
(select
@num:= 0) init
cross join
(select @num := @num +1 rn
from history ) t -- assume history has at least 10 rows
limit
10 ) h2 -- n = 10; change it to the number of rows per aid required
) t2
on t1.aid = t2.aid and t1.rn = t2.rn
order by t2.aid, t2.rn