我已经尝试解决这个问题两个多小时了。我需要你的帮助。我想使用此查询对两个表中的数据进行排序:
SELECT *
FROM (
SELECT INV.`id` , INV.`userid` , Cli.firstname, Cli.lastname, Cli.companyname, Cli.currency, INV.`total` , INV.`date` , INV.`duedate`
FROM tblinvoices AS INV, tblclients Cli
WHERE now( ) >= DATE_ADD( INV.duedate, INTERVAL +2
DAY )
AND INV.STATUS = 'unpaid'
AND Cli.id = INV.userid
AND INV.total >=200
AND INV.`date` != INV.`duedate`
) AS X
ORDER BY X.duedate ASC
我得到这个结果:
id userid firstname lastname companyname currency total date duedate
3 3 younes Abakarim 1 154877.00 2014-10-18 2014-11-01
9 12 Faissal Marouan 1 2568.00 2014-10-23 2014-11-06
34 3 younes Abakarim 1 252.60 2015-01-10 2015-01-15
31 12 Faissal Marouan 1 288.00 2015-01-01 2015-01-15
36 3 younes Abakarim Bestgallery 1 288.00 2015-01-16 2015-01-19
但我想要的是这个:
id userid firstname lastname companyname currency total date duedate
3 3 younes Abakarim 1 154877.00 2014-10-18 2014-11-01
34 3 younes Abakarim 1 252.60 2015-01-10 2015-01-15
36 3 younes Abakarim Bestgallery 1 288.00 2015-01-16 2015-01-19
9 12 Faissal Marouan 1 2568.00 2014-10-23 2014-11-06
31 12 Faissal Marouan 1 288.00 2015-01-01 2015-01-15
并且仍然按duedate
排序.
首先,您应该修复查询,使其使用正确的join
语法并且具有一定的可读性:
SELECT *
FROM (SELECT INV.`id` , INV.`userid` , Cli.firstname, Cli.lastname,
Cli.companyname, Cli.currency,
INV.`total` , INV.`date` , INV.`duedate`
FROM tblinvoices INV JOIN
tblclients Cli
ON Cli.id = INV.userid
WHERE NOW() >= DATE_ADD(INV.duedate, INTERVAL + 2 DAY) AND
INV.STATUS = 'unpaid' AND
INV.total >= 200 AND
INV.`date` <> INV.`duedate`
) ic
ORDER BY Xic.duedate ASC;
接下来,得到你想要的东西有点复杂。 您希望每个客户端的截止日期相同。 这需要加入其中。 如果MySQL支持CTE,这将简单得多,但是唉:
SELECT ic.*
FROM (SELECT INV.`id` , INV.`userid` , Cli.firstname, Cli.lastname,
Cli.companyname, Cli.currency,
INV.`total` , INV.`date` , INV.`duedate`
FROM tblinvoices INV JOIN
tblclients Cli
ON Cli.id = INV.userid
WHERE NOW() >= DATE_ADD(INV.duedate, INTERVAL + 2 DAY) AND
INV.STATUS = 'unpaid' AND
INV.total >= 200 AND
INV.`date` <> INV.`duedate`
) ic JOIN
(SELECT INV.`userid`, MAX(INV.`duedate`) as maxdd
FROM tblinvoices INV
WHERE NOW() >= DATE_ADD(INV.duedate, INTERVAL + 2 DAY) AND
INV.STATUS = 'unpaid' AND
INV.total >= 200 AND
INV.`date` <> INV.`duedate`
GROUP BY INV.`userid`
) ud
ON ic.userid = ud.userid
ORDER BY ud.maxdd ASC, ic.userid;
我不确定您是否想要排序截止日期的最大值还是最小值。 对于您的示例,这两者是等效的。
添加带逗号的第二个排序属性:
SELECT *
FROM (
SELECT INV.`id` , INV.`userid` , Cli.firstname, Cli.lastname, Cli.companyname, Cli.currency, INV.`total` , INV.`date` , INV.`duedate`
FROM tblinvoices AS INV, tblclients Cli
WHERE now( ) >= DATE_ADD( INV.duedate, INTERVAL +2
DAY )
AND INV.STATUS = 'unpaid'
AND Cli.id = INV.userid
AND INV.total >=200
AND INV.`date` != INV.`duedate`
) AS X
ORDER BY X.userid ASC, x.duedate ASC
使用这个:
SELECT *
FROM (
SELECT INV.`id`
,INV.`userid`
,Cli.firstname
,Cli.lastname
,Cli.companyname
,Cli.currency
,INV.`total`
,INV.`date`
,INV.`duedate`
FROM tblinvoices AS INV
,tblclients Cli
WHERE now() >= DATE_ADD(INV.duedate, INTERVAL + 2 DAY)
AND INV.STATUS = 'unpaid'
AND Cli.id = INV.userid
AND INV.total >= 200
AND INV.`date` != INV.`duedate`
) AS X
ORDER BY x.userid ASC
,X.duedate ASC;
试试这个 - 添加一个分组依据子句:
SELECT *
FROM (
SELECT INV.`id` , INV.`userid` , Max(Cli.firstname) firstname, Max(Cli.lastname) lastname, Max(Cli.companyname) companyname, Max(Cli.currency) currency, Max(INV.`total`) total , Max(INV.`date`) date , Max(INV.`duedate`) duedate
FROM tblinvoices AS INV, tblclients Cli
WHERE now( ) >= DATE_ADD( INV.duedate, INTERVAL +2
DAY )
AND INV.STATUS = 'unpaid'
AND Cli.id = INV.userid
AND INV.total >=200
AND INV.`date` != INV.`duedate`
group by INV.userid, INV.id) AS X
ORDER BY X.duedate ASC