这个查询给出了它想要的东西,但问题是它不是我想要的,而且我似乎不能去掉重复的内容,而且关于这个主题的其他帖子似乎不适用。它用于销售报告,并从日历中提取日期,但有时当需要回调时,同一销售具有多个日历事件。
日历表只有ID、Title、Location、Start、Quote字段,其余字段来自quoterequests,这是MySQL。
SELECT qr.ID AS ID,
c.ID AS CalendarID,
DATE_FORMAT(QuoteDate,'%m-%d-%Y') AS QuoteDate,
DATE_FORMAT(`Start`,'%m-%d-%Y') AS VerDate,
TIME_FORMAT(`Start`,'%h:%i %p') AS VerTime,
Name, MakeModel, CONCAT_WS('-',Prefix, Telephone) AS Phone,
Email,
Address,
City,
State,
Zipcode,
MilesQuoted,
AmtQuoted,
AmtCharged,
ServiceFee
FROM quoterequests qr
JOIN calendar c
ON Quote = qr.ID
WHERE AmtCharged > 0
AND YEAR(`Start`) = 2015
ORDER BY UNIX_TIMESTAMP(`Start`) ASC
发布于 2018-07-19 04:26:20
没有足够的信息来回答这个问题,但作为一个经验法则:
GROUP BY仅在将累加函数(min、max、count等)与普通列混合使用时使用。它不是用来删除重复项的。
要删除重复项,请使用SELECT DISTINCT
发布于 2018-07-20 02:40:00
也许不必要地令人费解,但这似乎起到了作用,当我在做的时候,我意识到Quote显然是一个保留的词,所以我把它去掉了。事实证明,正如所指出的,主要问题是缺少MIN,但我没有意识到它可以在多个地方使用。一旦我这样做了,它就会给出正确的结果:
SELECT
ID,
DATE_FORMAT(QuoteDate,'%m-%d-%Y') AS QuoteDate,
DATE_FORMAT(MIN(c.`Start`),'%m-%d-%Y') AS VerDate,
TIME_FORMAT(MIN(c.`Start`),'%h:%i %p') AS VerTime,
Name, MakeModel, CONCAT_WS('-',Prefix, Telephone) AS Phone,
Email,
Address,
City,
State,
Zipcode,
MilesQuoted,
AmtQuoted,
AmtCharged,
ServiceFee
FROM quoterequests qr
LEFT JOIN (SELECT `Quote`,
`Start`,
MIN(UNIX_TIMESTAMP(`Start`)) AS `TimeStamp`
FROM calendar c
WHERE YEAR(`Start`) = 2015
GROUP BY ID, `Quote`, `Start`
ORDER BY UNIX_TIMESTAMP(`Start`) ASC) AS c
ON qr.ID = c.`Quote`
WHERE AmtCharged > 0
AND YEAR(c.`Start`) = 2015
GROUP BY `Quote`
ORDER BY UNIX_TIMESTAMP(MIN(c.`Start`)) ASC
https://stackoverflow.com/questions/51410310
复制相似问题