Как использовать MySQL и считать и ранжировать
Я разработчик MS-SQL, теперь я использую этот запрос (MySQL) ↓
SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT FROM CUSTOM_LIST
AS A
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id
Результат:
![enter image description here]()
Я хочу это:
![enter image description here]()
Ответы
Ответ 1
Попробуйте что-нибудь вроде этого:
SELECT ..., C.TOTAL_CNT, (@r := @r + 1) AS rank FROM CUSTOM_LIST, (SELECT @r := 0) t
...
ORDER BY C.TOTAL_CNT DESC
Весь запрос:
SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, (@r := @r + 1) AS rank
FROM CUSTOM_LIST AS A, (SELECT @r := 0) t
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id
ORDER BY C.TOTAL_CNT DESC
Что делать, если мы получили два одинаковых значения в Total_CNT?
Возможно, что-то вроде этого:
SELECT ..., (@last := C.TOTAL_CNT) AS TOTAL_CNT,
IF(@last = C.TOTAL_CNT, @r, @r := @r + 1) AS rank
FROM CUSTOM_LIST, (SELECT @r := 0, @last := -1) t
...
Ответ 2
Обновление
RANK() OVER (ORDER BY TOTAL_CNT DESC DESC) AS Rank
Здесь я получил еще одно очень хорошее решение.
SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, RANK() OVER (ORDER BY TOTAL_CNT DESC) AS Rank FROM CUSTOM_LIST
AS A
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id
