select count(stockcode) as num,stockcode from wxuserstock GROUP BY stockcode ORDER BY num desc;
select count(wxuserstock.stockcode) as num,wxuserstock.stockcode from wxuserstock,xg2022 where wxuserstock.stockcode=xg2022.stockcode GROUP BY wxuserstock.stockcode ORDER BY
num desc;

select wxuserstock.stockcode,count(wxuserstock.stockcode) as anaqty, username
from xg2022,wxuserstock 
where 1
and xg2022.stockcode=wxuserstock.stockcode
and xg2022.QSm like "%+e-e%" 
and xg2022.stockname not like "%ST%" 
and xg2022.stockname not like "%退%" 
and (xg2022.QSm like "%-1ab%" or xg2022.QSm like "%-2ab")
group by wxuserstock.stockcode


select stockcode,stockname,QSm,A.anaqty 
from xgall,
(select count(stockcode) as anaqty,stockcode as stockcodea from wxuserstock GROUP BY stockcode ORDER BY stockcode) as A
where 1
and A.stockcodea=stockcode
and QSm like "%+e-e%" 
and stockname not like "%ST%" 
and stockname not like "%退%" 
and (QSm like "%-1ab%" or QSm like "%-2ab")
order by profitPm desc

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

select xgalla.*, IFNULL(wxstockanalyzea.anaqty, 0) as anaqty
from 
(select stockcode,stockname,QSm 
 from xgall
 where 1
 and QSm like "%+e-e%"
 and stockname not like "%ST%"
 and stockname not like "%退%"
 and (QSm like "%-1ab%" or QSm like "%-2ab")
 order by profitPm desc) as xgalla 
left join
(select count(stockcode) as anaqty,stockcode as stockcodea from wxstockanalyze GROUP BY stockcode ORDER BY stockcode) as wxstockanalyzea
on xgalla.stockcode=wxstockanalyzea.stockcodea;

select 0 as `index`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xgall.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,tusharestocklist.industry
from xgall,tusharestocklist
where 1
and tusharestocklist.ts_code=xgall.stockcode
and QSm like "%+e-e%" 
and stockname not like "%ST%" 
and stockname not like "%退%" 
and (QSm like "%-1ab%" or QSm like "%-2ab")
order by profitPm desc

select xgalla.*, IFNULL(wxstockanalyzea.anaqty, 0) as anaqty
from
(
select 0 as `index1`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 
    0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 
    0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 
    0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 
    0 as b5v, xgall.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,
    tusharestocklist.industry
from xgall,tusharestocklist
where 1
and tusharestocklist.ts_code=xgall.stockcode
and QSm like "%+e-e%" 
and stockname not like "%ST%" 
and stockname not like "%退%" 
and (QSm like "%-1ab%" or QSm like "%-2ab")
order by profitPm desc
 ) as xgalla
left join
(
    select count(stockcode) as anaqty,stockcode as stockcodea 
    from wxstockanalyze 
    GROUP BY stockcode 
    ORDER BY stockcode
) as wxstockanalyzea
on xgalla.stockcode=wxstockanalyzea.stockcodea;

//month old
select 0 as `index`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xgall.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,tusharestocklist.industry
from xgall,tusharestocklist  
where (INSTR(QSm, "-2a") >0 or INSTR(QSm, "-1a")> 0)             
and tusharestocklist.ts_code=xgall.stockcode
and (ConfirmedJGm=9 
and ConfirmedJGw=9 
and ConfirmedJGd=9 
and ConfirmedJGt=9)             
and (stockname not like "%ST%") 
and (stockname not like "%退%")                          
order by profitPm desc

//week old
select 0 as `index`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xgall.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,tusharestocklist.industry
from xgall,tusharestocklist
where (INSTR(QSw, "-2ab") >0 or INSTR(QSw, "-1ab")> 0)
and tusharestocklist.ts_code=xgall.stockcode
and (ConfirmedJGw=9 and ConfirmedJGd=9 and ConfirmedJGt=9)             and (stockname not like "%ST%")
and (stockname not like "%退%")
order by profitPw desc

//day old
select 0 as `index`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xg2022.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,tusharestocklist.industry
from xg2022,tusharestocklist
where 1
and tusharestocklist.ts_code=xg2022.stockcode
and (ConfirmedJGd=9 or ConfirmedJGd=0)
and (ConfirmedJGt=9 or ConfirmedJGt=0)
and (stockname not like "%ST%")
and (stockname not like "%退%")
order by profitPd desc

//m30 old
select 0 as `index`,0 as `open`,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj,0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p,0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xg2022.*,"" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo,tusharestocklist.industry
from xg2022,tusharestocklist
where
riskPt<2 and profitPt>5
and tusharestocklist.ts_code=xg2022.stockcode
and (INSTR(`QSt`, "-2") >0 or INSTR(`QSt`, "-1")> 0)
and (ConfirmedJGd=7 or ConfirmedJGd=8 or ConfirmedJGd=9 or ConfirmedJGd=0)
and stockname not like "%ST%"
and stockname not like "%退%"
order by profitPt desc




















select 0 as index,0 as open,0 as zs,0 as zf, 0 as zv, 0 as zgj, 0 as zdj, 0 as spj, 0 as ztj, \
   0 as dtj, 0 as hsl, 0 as syl,0 as s1p, 0 as s2p, 0 as s3p , 0 as s4p, 0 as s5p,0 as b1p, \
   0 as b2p, 0 as b3p , 0 as b4p, 0 as b5p,0 as s1v, 0 as s2v, 0 as s3v , 0 as s4v, 0 as s5v,\
   0 as b1v, 0 as b2v, 0 as b3v , 0 as b4v, 0 as b5v, xgall.*,\
   "" as JGm,"" as JGw,"" as JGd,"" as JGt,"" as JGf,"" as JGo \
   from xgall 



select * from wxuser;

select stockcode,stockname,QSm from xgall where (QSm like "%+e-e%" and QSm like "%-e") and stockname not like "%ST%" and stockname not like "%退%" and QSm like "%-1ab%";

select stockcode,stockname,QSm from xgall where (QSm like "%+e-e") and stockname not like "%ST%" and stockname not like "%退%" and QSm like "%-1ab%";

select sum(case when xgall.hp between 0 and 10 then 1 else 0 end) as '0-10%',
sum(case when xgall.hp between 10 and 20 then 1 else 0 end)  as '10-20%', 
sum(case when xgall.hp between 20 and 30 then 1 else 0 end)  as '20-30%', 
sum(case when xgall.hp between 30 and 40 then 1 else 0 end)  as '30-40%', 
sum(case when xgall.hp between 40 and 50 then 1 else 0 end)  as '40-50%', 
sum(case when xgall.hp between 50 and 60 then 1 else 0 end)  as '50-60%', 
sum(case when xgall.hp between 60 and 70 then 1 else 0 end)  as '60-70%', 
sum(case when xgall.hp between 70 and 80 then 1 else 0 end)  as '70-80%', 
sum(case when xgall.hp between 80 and 90 then 1 else 0 end)  as '80-90%', 
sum(case when xgall.hp between 90 and 100 then 1 else 0 end)  as '90-100%', 
count(stockname) as ttl from xgall;

SELECT
    (
        CASE
        WHEN xgall.hp >= 0   AND xgall.hp < 10  THEN  "0~10"
        WHEN xgall.hp >= 10  AND xgall.hp < 20  THEN "10~20"
        WHEN xgall.hp >= 20  AND xgall.hp < 30  THEN "20~30"
        WHEN xgall.hp >= 30  AND xgall.hp < 40  THEN "30~40"
        WHEN xgall.hp >= 40  AND xgall.hp < 50  THEN "40~50"
        WHEN xgall.hp >= 50  AND xgall.hp < 60  THEN "50~60"
        WHEN xgall.hp >= 60  AND xgall.hp < 70  THEN "60~70"
        WHEN xgall.hp >= 70  AND xgall.hp < 80  THEN "70~80"
        WHEN xgall.hp >= 80  AND xgall.hp < 90  THEN "80~90"
        WHEN xgall.hp >= 90  THEN "90-100"
        END
    ) AS LEVEL,
    (
        count(*) * 100 / (SELECT count(DISTINCT(xgall.stockcode)) FROM xgall
        )
    ) AS per,
    count(stockcode) as qty
FROM xgall
GROUP BY
    (
        CASE
        WHEN xgall.hp >= 0   AND xgall.hp < 10  THEN  "0~10"
        WHEN xgall.hp >= 10  AND xgall.hp < 20  THEN "10~20"
        WHEN xgall.hp >= 20  AND xgall.hp < 30  THEN "20~30"
        WHEN xgall.hp >= 30  AND xgall.hp < 40  THEN "30~40"
        WHEN xgall.hp >= 40  AND xgall.hp < 50  THEN "40~50"
        WHEN xgall.hp >= 50  AND xgall.hp < 60  THEN "50~60"
        WHEN xgall.hp >= 60  AND xgall.hp < 70  THEN "60~70"
        WHEN xgall.hp >= 70  AND xgall.hp < 80  THEN "70~80"
        WHEN xgall.hp >= 80  AND xgall.hp < 90  THEN "80~90"
        WHEN xgall.hp >= 90  THEN "90-100"
        END
    ) order by LEVEL desc;