Michael的笔记本

分表逻辑：

• d_stock_history为分表的逻辑表，实际分表为d_stock_trade_daily__2015,d_stock_trade_daily__2020，分片键为trade_date。
• d_stock_trade_daily为分表的逻辑表，实际分表为d_stock_trade_daily_2015,d_stock_trade_daily_2020，分片键为trade_date。
• d_stock_moneyflow为分表的逻辑表，实际分表为d_stock_moneyflow_2015,d_stock_moneyflow_2020，分片键为trade_date。

【子查询】

1、逻辑表在子查询中，导致无法走分表逻辑

 SELECT DATE_FORMAT(trade_date,'%Y-%m-%d')
		FROM (SELECT *
		      FROM d_stock_trade_daily
		      WHERE ts_code = '000001'
		      ORDER BY trade_date DESC
		      LIMIT 0, 10) a
		ORDER BY trade_date ASC
		LIMIT 0, 1

2、子查询的*号不识别

SELECT a.*,
       round(profitSum * 100 / sellSum, 2) `yieldRate`,
       buySum - sellSum `holdingAmount`
FROM (SELECT sum(buy_amount) `buySum`,
             ifnull(sum(if(status = 1, buy_amount, 0)), 0) `sellSum`,
             ifnull(sum(profit), 0) `profitSum`
      FROM ss_trade_record
      WHERE status != -1) a

mysql查询如下：

sharding-proxy查询如下：

无法解析子查询的号。
需要将子查询的号修改成具体的字段名字再查询就正常了

SELECT a.buySum,a.sellSum,a.profitSum,
       round(profitSum * 100 / sellSum, 2) `yieldRate`,
       buySum - sellSum `holdingAmount`
FROM (SELECT sum(buy_amount) `buySum`,
             ifnull(sum(if(status = 1, buy_amount, 0)), 0) `sellSum`,
             ifnull(sum(profit), 0) `profitSum`
      FROM ss_trade_record
      WHERE status != -1) a

【关联查询】
1、注意，分表策略相同的表，一定要设置绑定表关系，不然就会走笛卡尔积关联查询，如下面：

SELECT cu.code,
       cu.name,
       cu.market,
       cu.industry,
       round(sum(t.amount) / 10, 0) `s_amount`,
       avg(t.`pct_chg`) `a_pct_chg`,
       m.money
FROM d_stock_concept_useful cu,
     d_stock_concept_detail cd,
     d_stock_profile p,
     d_stock_trade_daily t,
     d_stock_moneyflow m
WHERE     cu.code = cd.id
      AND cd.ts_code = p.ts_code
      AND cd.out_date IS NULL
      AND p.industry = cu.industry
      AND p.market = cu.market
      AND p.list_status = 'L'
      AND p.ts_code = t.ts_code
      AND t.ts_code = m.ts_code
      AND t.trade_date = m.trade_date
      AND t.trade_date = '20210927'
GROUP BY cu.code,
         cu.name,
         cu.market,
         cu.industry
ORDER BY a_pct_chg DESC
LIMIT 0, 100

d_stock_trade_daily与d_stock_moneyflow的分表逻辑一致，是绑定表，如果不设置绑定表，那么d_stock_moneyflow会被路由到d_stock_moneyflow_2015,d_stock_moneyflow_2020这两张表去做查询。

2、分表关联自己查询失败
下面sql无法定位到具体的分表中查询，只定位到第一个分表(d_stock_trade_daily_basic_2015)，而没有定位到对的分表(d_stock_trade_daily_basic_2020)中去。

SELECT b.*
FROM d_stock_trade_daily_basic a, d_stock_trade_daily_basic b
WHERE     a.ts_code = b.ts_code
      AND a.trade_date = '20210802'
      AND b.trade_date = '20210803'
      AND b.pe_ttm * 2 < a.pe_ttm
      AND b.pe > a.pe

如果加入第三张表做关联，就可以正常查询，如下：

SELECT r.*,d.*
FROM d_stock_recommend r, d_stock_change_stat c, d_stock_change_stat d
WHERE     r.ts_code = c.ts_code
      AND r.b_date = c.trade_date
      AND r.ts_code = d.ts_code
      AND r.b_date = d.trade_date
ORDER BY r.b_date DESC, r.ts_code ASC;

持续更新中.....