select
city,
COUNT(case when con LIKE '%多云%' THEN 1 ELSE null END) AS cloudy_days,
concat(CAST(COUNT(case when con LIKE '%多云%' THEN 1 ELSE null END)*100/dayofyear('2021-12-31') AS DECIMAL(10,2)), '%') AS p
from weather_rcd_china
where YEAR(dt) = 2021
GROUP BY city
ORDER BY 3 DESC;
select
city,
COUNT(case when con LIKE '%多云%' THEN 1 ELSE null END) AS cloudy_days,
CONCAT(round(COUNT(case when con LIKE '%多云%' THEN 1 ELSE null END)*100/dayofyear('2021-12-31'), 2), '%') AS p
from weather_rcd_china
where YEAR(dt) = 2021
GROUP BY city
ORDER BY p DESC;
select
city,
ROUND(avg(CAST(substring_index(tmp_h, '℃', 1) AS DECIMAL(4,2))), 2) AS avg_tmp_h
FROM weather_rcd_china
WHERE year(dt)=2021
GROUP BY city
order by avg_tmp_h DESC
select
city,
ROUND(avg(CAST(substring_index(tmp_h, '℃', 1) AS FLOAT)), 2) AS avg_tmp_h
FROM weather_rcd_china
WHERE year(dt)=2021
GROUP BY city
order by avg_tmp_h DESC
select user_id from (
SELECT user_id
FROM apple_pchs_rcd
GROUP BY user_id
having
SUM(IF(product_type != 'iPhone', 1, 0)) =0
) as temp
order by user_id ASC;
select user_id from (
SELECT user_id,product_type
FROM apple_pchs_rcd
GROUP BY user_id, product_type
having
SUM(IF(product_type != 'iPhone', 1, 0)) =0
) as temp
order by user_id ASC;
select user_id from (
SELECT user_id, product_type
FROM apple_pchs_rcd
GROUP BY user_id, product_type
having
SUM(IF(product_type != 'iPhone', 1, 0)) =0
) as temp
order by user_id ASC;
select user_id from (
SELECT user_id, product_type
FROM apple_pchs_rcd
GROUP BY user_id, product_type
having
SUM(IF(product_type = 'iPhone', 1, 0)) =1
) as temp
order by user_id ASC;
select user_id from (
SELECT user_id, product_type
FROM apple_pchs_rcd
GROUP BY user_id, product_type
having COUNT(DISTINCT product_type) = 1 AND product_type = 'iPhone'
) AS temp
SELECT DISTINCT user_id
FROM (
SELECT user_id, product_name
FROM apple_pchs_rcd
WHERE product_type = 'iPhone'
) AS temp
GROUP BY user_id
HAVING COUNT(DISTINCT product_name) = 1
ORDER BY user_id ASC;
SELECT A.user_id
FROM (
select
user_id,product_type
from apple_pchs_rcd
GROUP BY user_id, product_type
) AS A
LEFT JOIN (
select
user_id,product_type
from apple_pchs_rcd
WHERE product_type = 'iPhone'
GROUP BY user_id, product_type
) AS B
ON A.product_type = B.product_type AND A.user_id <> B.user_id
select
A.cust_uid, B.cust_uid
from (
SELECT
cust_uid, mch_nm
from mt_trx_rcd1
WHERE cust_uid='MT10000'
GROUP BY cust_uid, mch_nm
) as A
LEFT JOIN (
select DISTINCT
cust_uid, mch_nm from mt_trx_rcd1
) as B
ON A.cust_uid <> B.cust_uid AND A.mch_nm = B.mch_nm
GROUP BY A.cust_uid, B.cust_uid
HAVING COUNT(DISTINCT A.mch_nm) >= 14
select
A.cust_uid, B.cust_uid
from (
SELECT
cust_uid, mch_nm
from mt_trx_rcd1
WHERE cust_uid='MT10000'
GROUP BY cust_uid, mch_nm
) as A
LEFT JOIN (
select DISTINCT
cust_uid, mch_nm from mt_trx_rcd1
GROUP BY cust_uid, mch_nm
) as B
ON A.cust_uid <> B.cust_uid
select
A.cust_uid, B.cust_uid
from (
SELECT
cust_uid, mch_nm
from mt_trx_rcd1
WHERE cust_uid='MT10000'
GROUP BY cust_uid, mch_nm
) as A
LEFT JOIN (
SELECT t2.cust_uid
FROM (
select DISTINCT
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '庄家界(千灯店)'
GROUP BY cust_uid, mch_nm
) as t1
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '黄记烘培宫廷桃酥王'
GROUP BY cust_uid, mch_nm
) as t2
ON t1.cust_uid = t2.cust_uid
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '品众素心素食餐厅'
GROUP BY cust_uid, mch_nm
) as t3
ON t1.cust_uid = t3.cust_uid
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '一枚帅哥做的菜'
GROUP BY cust_uid, mch_nm
) as t4
ON t3.cust_uid = t4.cust_uid
) AS B
on A.cust_uid <> B.cust_uid
group by A.cust_uid , B.cust_uid
order by A.cust_uid, B.cust_uid
select
A.cust_uid, B.cust_uid
from (
SELECT
cust_uid, mch_nm
from mt_trx_rcd1
WHERE cust_uid='MT10000'
GROUP BY cust_uid, mch_nm
) as A
LEFT JOIN (
SELECT t2.cust_uid
FROM (
select DISTINCT
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '庄家界(千灯店)'
GROUP BY cust_uid, mch_nm
) as t1
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '黄记烘培宫廷桃酥王'
GROUP BY cust_uid, mch_nm
) as t2
ON t1.cust_uid = t2.cust_uid
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '品众素心素食餐厅'
GROUP BY cust_uid, mch_nm
) as t3
ON t1.cust_uid = t3.cust_uid
) AS B
on A.cust_uid <> B.cust_uid
group by A.cust_uid , B.cust_uid
order by A.cust_uid, B.cust_uid
select
A.cust_uid, B.cust_uid
from (
SELECT
cust_uid, mch_nm
from mt_trx_rcd1
WHERE cust_uid='MT10000'
GROUP BY cust_uid, mch_nm
) as A
LEFT JOIN (
SELECT t2.cust_uid
FROM (
select DISTINCT
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '庄家界(千灯店)'
GROUP BY cust_uid, mch_nm
) as t1
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '黄记烘培宫廷桃酥王'
GROUP BY cust_uid, mch_nm
) as t2
ON t1.cust_uid = t2.cust_uid
INNER JOIN (
select
cust_uid, mch_nm from mt_trx_rcd1
where mch_nm = '黄记烘培宫廷桃酥王'
GROUP BY cust_uid, mch_nm
) as t3
ON t1.cust_uid = t3.cust_uid
) AS B
on A.cust_uid <> B.cust_uid
group by A.cust_uid , B.cust_uid
order by A.cust_uid, B.cust_uid