具有相关查询的MySQL Having子句不起作用

I have a problem in my having clause. I want to compare between two sum having the same Bid but different sens fields I get this output:

我的条款有问题。我想比较具有相同Bid但具有不同sens字段的两个sum我得到此输出：

SELECT v.bid 
FROM   v1 v 
WHERE  sens = 'c' 
GROUP  BY bid 
HAVING Sum(mont) < (SELECT Sum(l.mont) 
                    FROM   v1 l 
                    WHERE  sens = 'd' 
                           AND l.bid = v.bid group by l.bid);

ERROR 1054 (42S22): Field 'v.bid unknown in field list`

ERROR 1054（42S22）：字段'v.bid字段列表中未知

Edit : V1 is a view, i used aliases l and v trying to ling the sub query to the main query

编辑：V1是一个视图，我使用别名l和v试图将子查询修改为主查询

Sorry guys thank you all for your answers, i was having an issue wuth the columns of the original table and now it is solved :)

对不起伙计们，谢谢你们所有的答案，我有一个问题wuth原始表的列，现在它已经解决了:)

3 个解决方案

#1

No need for two selects from this table, you can use CASE EXPRESSION for this purpose :

无需从此表中选择两个，您可以使用CASE EXPRESSION来实现此目的：

SELECT v.bid FROM v1 v
GROUP BY v.bid
HAVING SUM(CASE WHEN v.sens = 'c' THEN v.mont ELSE 0 END) <
       SUM(CASE WHEN v.sens = 'd' THEN v.mont ELSE 0 END) 
   AND COUNT(CASE WHEN v.sens = 'c' THEN 1 END) > 0

#2

The alias in subselect is not visible/available so you get and column not found error but you can refactor your queru this way

subselect中的别名不可见/可用，因此您得到并且找不到列错误但您可以通过这种方式重构您的查询

  select  v.bid, t.tot 
      from v1 v 
    Join (
       select  bid, sum(mont)  tot
        from v1  
        where sens = 'd' 
        group by bid
        ) t on t.bid = v.bid
    where v.sens = 'c' 
    and v.bid < tot

#3

I was having an issue with the visibility of the columns because the view was improperly declared , i apologies guys :'( i feel really bad :(

我对列的可见性存在问题，因为视图声明不正确，我很抱歉：'（我觉得很糟糕:(

the script:

剧本：

mysql> select * from op;
+------+------+------+------+
| bid  | cid  | sens | mont |
+------+------+------+------+
|    1 |    1 | c    | 2000 |
|    1 |    1 | c    | 2000 |
|    1 |    1 | c    | 2000 |
|    1 |    1 | c    | 2000 |
|    1 |    2 | c    | 2000 |
|    1 |    3 | c    | 2000 |
|    2 |    3 | c    | 2000 |
|    1 |    1 | d    | 2000 |
|    2 |    3 | d    | 4000 |
+------+------+------+------+
9 rows in set (0.00 sec)


create view v1 as ( select bid , cid , sens , sum(mont) as sumcli from op group by bid,cid,sens);

mysql> select * from v1 ;
+------+------+------+--------+
| bid  | cid  | sens | sumcli |
+------+------+------+--------+
|    1 |    1 | c    |   8000 |
|    1 |    1 | d    |   2000 |
|    1 |    2 | c    |   2000 |
|    1 |    3 | c    |   2000 |
|    2 |    3 | c    |   2000 |
|    2 |    3 | d    |   4000 |
+------+------+------+--------+
6 rows in set (0.00 sec)

mysql> SELECT v.bid
    -> FROM   v1 v
    -> WHERE  sens = 'c'
    -> group by v.bid
    -> HAVING Sum(v.sumcli) < (SELECT Sum(l.sumcli)
    ->                     FROM   v1 l
    ->                     WHERE  sens = 'd'
    ->                            AND l.bid = v.bid group by l.bid);
+------+
| bid  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

#1