数据表如下:
———————-

表a: ———————- id num1 min 1 2 1 2 1 1 3 4 1 4 3 2

表b: ———————- a_id c_id num2 1 1 2 3 2 3 3 2 1 4 3 2 4 3 1

表c: ———————- id is_num2 1 1 2 1 3 0

条件:
———————-

1. 表a中的某些不与表b关联的记录，
   则使用(a.num1<=a.min)的条件。
2. 表a与表b，1对多关系的记录；表c与表b，1对多关系的记录，
   则使用(c.is_num2=1 and b.num2<=a.min) or (c.is_num2=0 and a.num1<=a.min)的条件。

result:
———————-

a.id	number
2	1
3	1

分析:
———————-
表a与表b为

1. 1对0时（取出表a中与表b不关联的记录）
   SELECT a.id, a.num1 AS number
   FROM a
   WHERE NOT EXISTS ( SELECT b.a_id FROM b WHERE a.id = b.a_id )
       AND a.num1 <= a.min
2. 1对多时
   SELECT a.id, IF ( c.is_num2 = 0, a.num1, min( b.num2 ) ) AS number
   FROM a LEFT JOIN ( b JOIN c ) ON ( a.id = b.a_id AND c.c_id = b.c_id )
   WHERE c.c_id IS NOT NULL
       AND (
           ( c.is_num2 = 1 AND b.num2 <= a.min ) OR ( c.is_num2 = 0 AND a.num1 <= a.min )
       )
   GROUP BY a.id
3. 将两条查询结果整合在一起,注意，两条查询结果中返回字段数量、类型和字段名（别名）都必须一致。
   ( … ) UNION ( … )

最终适用于MYSQL的T-SQL语句:
———————-
(
    SELECT a.id, a.num1 AS number 
    FROM a 
    WHERE NOT EXISTS ( SELECT b.a_id FROM b WHERE a.id = b.a_id ) 
        AND a.num1 <= a.min
)
UNION
(
    SELECT a.id, IF ( c.is_num2 = 0, a.num1, min( b.num2 ) ) AS number
    FROM a LEFT JOIN ( b JOIN c ) ON ( a.id = b.a_id AND c.c_id = b.c_id )
    WHERE c.c_id IS NOT NULL 
        AND ( 
            ( c.is_num2 = 1 AND b.num2 <= a.min ) OR ( c.is_num2 = 0 AND a.num1 <= a.min ) 
        )
    GROUP BY a.id
)

关键词:
———————-
(NOT )EXISTS: 子查询，记录是否存在
UNION: 合并查询结果
JOIN / INNER JOIN / LEFT|RIGHT JOIN: 多表关联查询

原文链接：http://docs.google.com/Doc?id=acb6gfjgmjf_1512hptmvhcr
原文作者：fangle

Tags：exists, join, mysql, union.
评论数量(
Fatal error: Call to undefined function comments_only_number() in /home/.wyatt/fangle/28star.com/blog/wp-content/themes/iblue/single.php on line 15