SELECT YEAR(list_date), COUNT(distinct ts_code) FROM stock_info WHERE industry = '银行' AND ts_code LIKE '%SZ' GROUP BY YEAR(list_date)

select YEAR(list_date),COUNT(DISTINCT ts_code) from stock_info where name LIKE '%银行%' AND area = '深圳' group by YEAR(list_date)

select YEAR(list_date),COUNT(DISTINCT ts_code) from stock_info where name LIKE '%银行%' group by YEAR(list_date)

select YEAR(list_date),COUNT(*) from stock_info where name LIKE '%银行%' group by YEAR(list_date)

select YEAR(list_date),COUNT(*) from stock_info where industry = '银行' group by YEAR(list_date)

select YEAR(list_date),COUNT(*) from stock_info group by YEAR(list_date)

select 
city,
COUNT(1)
from weather_rcd_china 
WHERE MONTH(dt) IN ('1','2','12')
AND con LIKE "%雪%"
GROUP BY city
ORDER BY 2 DESC

select 
city,
COUNT(1)
from weather_rcd_china 
WHERE MONTH(dt) IN ('1','2','12')
AND con LIKE "%雪%"
GROUP BY city

select 
* 
from 
didi_order_rcd 
where cust_uid = 'kjhd30' AND DATE(finish_time) = '1970-01-01'
ORDER BY call_time LIMIT 1

select 
* 
from 
didi_order_rcd 
where cust_uid = 'kjhd30' AND DATE(finish_time) != "1970-01-01"
ORDER BY call_time LIMIT 1

SELECT 
    sum(grab_time-call_time)/count(1)/60 AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

SELECT 
    sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

SELECT 
sum(grab_time - call_time) / COUNT(1)
 FROM didi_order_rcd WHERE date(grab_time ) != "1970-01-01"

SELECT 
t1.live_id,
t2.live_type,
t1.cnt,
t2.live_nm
FROM (
SELECT 
live_id,
COUNT(*) AS cnt
FROM ks_live_t1
WHERE 
enter_time >= '2021-09-12 23:00:00' 
AND enter_time < '2021-09-13 00:00:00'
GROUP BY live_id
) t1
JOIN (
SELECT 
live_id, 
live_type,
live_nm
FROM ks_live_t2
) t2 
ON t1.live_id = t2.live_id
JOIN (
SELECT 
live_type,
live_id,
ROW_NUMBER() OVER (PARTITION BY live_type ORDER BY cnt DESC) AS rn
FROM (
SELECT 
t2.live_type,
t1.live_id,
COUNT(*) AS cnt
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 ON t1.live_id = t2.live_id
WHERE 
t1.enter_time >= '2021-09-12 23:00:00' 
AND t1.enter_time < '2021-09-13 00:00:00'
GROUP BY t2.live_type, t1.live_id
) ranked
) t3 
ON t1.live_id = t3.live_id 
 AND t2.live_type = t3.live_type
WHERE t3.rn = 1
ORDER BY t2.live_type;

SELECT t1.live_id,t2.live_nm,t1.cnt FROM (SELECT live_id,COUNT(*) cnt FROM ks_live_t1 WHERE DATE(enter_time) = "2021-09-12" AND HOUR(enter_time) = 23 GROUP BY live_id ORDER BY CNT DESC LIMIT 5)t1
JOIN ks_live_t2 t2 ON t1.live_id = t2.live_id

SELECT HOUR(enter_time) ,COUNT(1) FROM ks_live_t1 GROUP BY HOUR(enter_time) order by hour(enter_time)

SELECT HOUR(enter_time) ,COUNT(1) FROM ks_live_t1 GROUP BY HOUR(enter_time)

SELECT
	count(IF(right(card1,1)= right(card2,1) ,1,null)),
COUNT(1),
	count(IF(right(card1,1)= right(card2,1) ,1,null))/ COUNT(1)
FROM
	hand_permutations

SELECT
	count(IF(right(card1,1)= right(card2,1) ,1,null))/ COUNT(1)
FROM
	hand_permutations

select * from hand_permutations WHERE SUBSTR(card1,2,1) = SUBSTR(card2,2,1)