select * 
from scores
where exam_date='2024-06-30'
	and (subject = '历史' and score >= 90
 or subject = '政治' and score >= 90
 or subject = '地理' and score >= 90)
order by score desc ,student_id,subject;

select *
from hand_permutations
where 
    concat(card1, card2) like '%A%A%' or
    concat(card1, card2) like '%A%K%' or
    concat(card1, card2) like '%K%K%' or
    concat(card1, card2) like '%K%A%'
order by id;

SELECT *
FROM hand_permutations
WHERE 
(LEFT(card1, 1) = 'A' AND LEFT(card2, 1) = 'A') 
OR (LEFT(card1, 1) = 'K' AND LEFT(card2, 1) = 'K') 
OR (
(
(LEFT(card1, 1) = 'A' AND LEFT(card2, 1) = 'K') 
OR (LEFT(card1, 1) = 'K' AND LEFT(card2, 1) = 'A')
) 
AND RIGHT(card1, 1) = RIGHT(card2, 1)
) 
OR (
(
(LEFT(card1, 1) = 'A' AND LEFT(card2, 1) = 'K') 
OR (LEFT(card1, 1) = 'K' AND LEFT(card2, 1) = 'A')
) 
AND RIGHT(card1, 1) != RIGHT(card2, 1)
)
ORDER BY id ASC;

SELECT 
t1.live_id,
t2.live_nm,
COUNT(*) AS enter_count
FROM 
ks_live_t1 t1
INNER JOIN 
ks_live_t2 t2 
ON t1.live_id = t2.live_id
WHERE 
enter_time >= '2021-09-12 23:00:00' 
AND enter_time < '2021-09-13 00:00:00'
GROUP BY 
t1.live_id, t2.live_nm
ORDER BY 
enter_count DESC
LIMIT 5;

select DATE_FORMAT(enter_time, '%H') AS hour_entered,
 		count(*) as enter_count
FROM ks_live_t1
GROUP BY hour_entered
ORDER BY hour_entered;

SELECT *
FROM numbers_for_fun
WHERE
    (a = 0 AND (
        (b = 0 AND c < 0) 
        OR (b != 0 AND (
            (b > 0 AND c < 0) 
            OR (b < 0)        
        ))
    ))
    OR
    (a != 0 AND (
        (a > 0 AND (c < 0 OR (b < 0 AND 4*a*c < b*b)))
        OR
        (a < 0)
    ));

SELECT *
FROM numbers_for_fun
WHERE
(a = 0 AND (
(b = 0 AND c > 0) 
OR (b > 0 AND c > 0)
OR (b < 0) 
))
OR
(a != 0 AND (
(a > 0 AND (c < 0 OR (b < 0 AND b*b - 4*a*c > 0)))
OR
(a < 0)
));

SELECT *
FROM numbers_for_fun
WHERE
(a = 0 AND (
(b = 0 AND c < 0) 
OR 
(b != 0 AND (
(b > 0 AND c <= 0) 
OR 
(b < 0 AND c <= 0)
))
))
OR
(a != 0 AND (
(a > 0 AND (
c <= 0 
AND 
b >= 0 
))
));

SELECT 
id,
a,
b,
c
FROM 
numbers_for_fun
WHERE 
(a <> 0 AND ((a > 0 AND (b > 0 OR c > 0)) OR (a < 0 AND c > 0)))
OR (a = 0 AND (b < 0 OR (b > 0 AND c > 0)));

WITH user_login_days AS (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM 
        user_login_log
    WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
),
distinct_login_days AS (
    SELECT 
        usr_id,
        COUNT(DISTINCT login_date) AS login_days
    FROM 
        user_login_days
    GROUP BY 
        usr_id
)
SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    distinct_login_days;

SELECT *
FROM numbers_for_fun
WHERE (a = 0 and b >= 0 and c <= 0) 
	or (a < 0 and 
	((b >= 0 and c <= 0) or (b < 0 and c <= (b * b) / (4 * a))
))

select 
    * 
from
    numbers_for_fun
where
    a = 0 and b<>0 and c<>0
order by 1

select 
    * 
from
    numbers_for_fun
where
    a = 0 and b<>0 and c>0
order by 1

SELECT * FROM numbers_for_fun WHERE a = 0 AND b <> 0 AND c<>0 order by 1;

SELECT * FROM numbers_for_fun WHERE a = 0 AND b > 0 AND c<>0 order by 1;

SELECT * FROM numbers_for_fun WHERE a = 0 AND b > 0 AND c<>0;

SELECT id, a, b, c FROM numbers_for_fun WHERE a = 0 AND b > 0 AND c<>0;

SELECT id, a, b, c FROM numbers_for_fun WHERE a = 0 AND (b > 0 OR b < 0);

SELECT *
FROM numbers_for_fun
WHERE a = 0
AND b != 0
AND (c * c) / ABS(b) >= 10;

select
	*
from
	numbers_for_fun
where
	a=0
and c!=0
and (b=1 or b=-1)
order by	
	id