现有两个表如下: 
t1:
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
t2:
+----+-------+-----------+--------------+-------+
| id | t1_id | sum_count | single_count | state |
+----+-------+-----------+--------------+-------+
|  1 | 1     |         5 |            0 | s1    |
|  2 | 1     |         5 |            2 | s2    |
|  3 | 1     |         5 |            2 | s2    |
|  4 | 2     |         3 |            0 | s1    |
|  5 | 2     |         3 |            3 | s2    |
|  6 | 3     |         4 |            0 | s1    |
|  7 | 3     |         4 |            3 | s2    |
+----+-------+-----------+--------------+-------+
现在想写一个sql语句,查询t1中的name, 条件是 t2中状态为s1的sum_count的和 > 状态为s2的single_count的和,按t1_id分组.
类似下面的语句,但下面的是错误的: 
select distinct name from t1 c,t2 p where c.id=p.t1_id and (select sum(sum_count) from t2 where t2.state='s1' group by p.t1_id) > (select sum(single_count) from t2 where t2.state<>'s1' group by p.t1_id);

解决方案 »

  1.   

    select name from t1 where id in
    (select t1_id from (
    select t1_id,state,sum(sum_count) as sumcount,sum(single_count) as single from csdn1 group  by t1_id,state
    ) as tmep where sumcount>single
      

  2.   

    select name
    from (
    select t1_id,t1.name,sum(IF(strcmp(state,'s1'),sum_count,0) as s1Sum,sum(IF(strcmp(state,'s2'),single_count,0) as s2Sum
    from t2 inner join t1 on t1.id = t2.t1_id
    group by t1_id,t1.name
    )
    where s1Sum=s2Sum
      

  3.   

    select name from t1,(select t1_id,sum(if(strcmp(state,'s1'),0,sum_co
    unt)) as  s1sum,sum(if(strcmp(state,'s2'),0,single_count)) as s2sum from t2 g
    roup by t1_id having s1sum>s2sum) as temp1 where t1.id=temp1.t1_id;