mysql查询由于日期不同而产生重复

ql3eal8s 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(327)

这个查询给出的正是它所要求的，但问题是，这不是我想要的，我似乎无法摆脱重复和其他职位的主题似乎不适用。它用于销售报告，它从日历中提取日期，但有时同一销售在需要回拨时有多个日历事件。
日历表只有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

sql mysql

来源：https://stackoverflow.com/questions/51410310/mysql-query-giving-duplicates-due-to-different-dates

2条答案

按热度按时间

fslejnso1#

也许不必要的复杂，但这似乎做的把戏，而我是在它，我意识到，报价显然是一个保留字，所以我回勾它。结果发现，主要的问题，正如所指出的，是缺乏分钟，但我没有意识到，它可以在多个地方使用。一旦我这么做了，它就给出了正确的结果：

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

赞(0）回复(0）举报 2021-06-20

yzuktlbb2#

没有足够的信息来回答这个问题，但根据经验：
group by仅在将AGRAGATE函数（min、max、count等）与普通列混合时使用。它不是用来删除重复项的。
要删除重复项，请使用 SELECT DISTINCT

赞(0）回复(0）举报 2021-06-20