如何使用两个不同的列mySQL从查询中获取数据而不是在其他查询中? [重复]

时间:2021-06-28 11:59:06

This question already has an answer here:

这个问题在这里已有答案:

I tried to do that query but it doesn't work because of the error 1241 I can't use NOT IN with more than 1 column and I can't use two atributtes before the NOT IN.

我试图做那个查询,但它不起作用,因为错误1241我不能使用NOT IN超过1列,我不能在NOT IN之前使用两个atributtes。

SELECT * FROM futbol.puntos WHERE Equipo, Liga
NOT IN
(SELECT  der.Equipo,der.Liga,der.Puntos 
FROM (SELECT p.Equipo, p .Liga, p.Puntos,
    IF(@prev_liga != p.Liga,
        @rownum:= 1,
        @rownum := @rownum + 1) rank,
    @prev_liga:=p.Liga
FROM (SELECT Equipo, Liga, Puntos FROM puntosmitadliga GROUP BY Liga, Puntos, Equipo ORDER BY Liga, Puntos DESC) p, 
     (SELECT
            @rownum:= NULL,
            @prev_liga := 0) r) der WHERE der.rank <= 3 ORDER BY der.Liga, der.rank) GROUP BY Liga HAVING max(puntos);

How can I resolve it?

我该如何解决?

1 个解决方案

#1


0  

SELECT * FROM my_table;
+----------------+-------------+
| customerNumber | orderNumber |
+----------------+-------------+
|             40 |           1 |
|             30 |           2 |
|             40 |           3 |
|             20 |           4 |
|             30 |           5 |
+----------------+-------------+

SELECT * 
  FROM my_table 
 WHERE (customernumber,ordernumber) NOT IN ((40,1),(30,3));
+----------------+-------------+
| customerNumber | orderNumber |
+----------------+-------------+
|             30 |           2 |
|             40 |           3 |
|             20 |           4 |
|             30 |           5 |
+----------------+-------------+
4 rows in set (0.07 sec)

mysql>

#1


0  

SELECT * FROM my_table;
+----------------+-------------+
| customerNumber | orderNumber |
+----------------+-------------+
|             40 |           1 |
|             30 |           2 |
|             40 |           3 |
|             20 |           4 |
|             30 |           5 |
+----------------+-------------+

SELECT * 
  FROM my_table 
 WHERE (customernumber,ordernumber) NOT IN ((40,1),(30,3));
+----------------+-------------+
| customerNumber | orderNumber |
+----------------+-------------+
|             30 |           2 |
|             40 |           3 |
|             20 |           4 |
|             30 |           5 |
+----------------+-------------+
4 rows in set (0.07 sec)

mysql>