我有一个正在运行的查询。然而,它只是按公司名称订购,而不是按这些公司内部发票的日期订购:
SELECT C.FULLNAME,C.COMPANY_ID,I.INVOICE_DATE FROM COMPANY C
JOIN #dsn2_alias#.INVOICE I ON I.COMPANY_ID = C.COMPANY_ID
WHERE I.INVOICE_ID IS NOT NULL AND I.INVOICE_DATE <= #attributes.date#
AND C.COMPANY_ID NOT IN (
SELECT C.COMPANY_ID FROM COMPANY C JOIN #dsn2_alias#.INVOICE I ON I.COMPANY_ID = C.COMPANY_ID WHERE I.INVOICE_ID IS NOT NULL AND I.INVOICE_DATE >= #attributes.date#
)
GROUP BY C.COMPANY_ID,C.FULLNAME,I.INVOICE_DATE ORDER BY C.FULLNAME
输出:
<cfoutput query="get_companies" group="company_id">
<tr height="20" onMouseOver="this.className='color-light';" onMouseOut="this.className='color-row';" class="color-row">
<td style="text-align:center;">#row#</td>
<td style="text-align:center;">#dateformat(INVOICE_DATE,'dd/mm/yyyy')#</td>
<td>#fullname#</td>
</tr>
<cfset row++/>
</cfoutput>
实际上,group by
子句在查询中不起作用。它在cfoutput中分组。
不管怎样,这里有一份公司名单。每个公司都有多个销售(发票)。我想列出在一段时间内没有销售的公司。我已经做到了,但有点小问题。我不能按时订购。我理解这里的错误,因为有多个发票,每次公司都是重复的,不能按时间订购。然而,如果它们是订购的,它是在发票日期完成的,因此公司是重复的。但我只想看到最后一次销售日期时间的公司名单。每次都不会重复。希望我清楚:)
谢谢你的帮助!
好的,我通过使用草莓关于GROUPWISE-MAXIMUM:的提示自己解决了
SELECT C.FULLNAME,C.COMPANY_ID,I.INVOICE_DATE
FROM COMPANY C INNER JOIN
(
SELECT I.COMPANY_ID,MAX(I.INVOICE_DATE) AS INVOICE_DATE
FROM #dsn2_alias#.INVOICE I GROUP BY I.COMPANY_ID
)
I ON I.COMPANY_ID = C.COMPANY_ID
WHERE I.INVOICE_DATE <= #attributes.date#
AND C.COMPANY_ID NOT IN
(
SELECT C.COMPANY_ID
FROM COMPANY C JOIN #dsn2_alias#.INVOICE I ON I.COMPANY_ID = C.COMPANY_ID
WHERE I.INVOICE_ID IS NOT NULL
AND I.INVOICE_DATE >= #attributes.date#
)
GROUP BY C.COMPANY_ID,C.FULLNAME,I.INVOICE_DATE ORDER BY I.INVOICE_DATE
现在它是按时间排序的,不会重复公司:)