select MIN(out-c2) from table where id in(3001,3002,3000);
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out-c2) from clickip where only in(188,257)' at line 1 SELECT MIN( out - c2 ) FROM clickip WHERE only IN ( 188, 257 ) 这句好像还执行不了
SELECT id,MIN(`out`-c2) AS m FROM test_csdb2 WHERE id IN(3001,3002,3000) GROUP BY id ORDER BY m ASC LIMIT 1; #id m #3001 2
SELECT MIN(`out`-c2) FROM test_csdb2 WHERE id IN(3001,3002,3000);#MIN(`out`-c2) #2
select * from tbl_name where (out-c2) in (select min(out-c2) from tbl_name where id in(3001,3002,3000)) and id in(3001,3002,3000)
这个我已经弄好了 就是刚才这个 SQL codeSELECT id,MIN(`out`-c2) AS m FROM test_csdb2 WHERE id IN(3001,3002,3000) GROUP BY id ORDER BY m ASC LIMIT 1; #id m #3001 2好像算出了 0是最小的 负数比0大
SELECT id FROM test_csdb2 a WHERE id IN(3001,3002,3000) AND 1> (SELECT COUNT(*) FROM test_csdb2 WHERE `out`-c2 < a.out-a.c2);
我测试了,没有你说的那个情况啊:SELECT *,MIN(`out`-c2) AS m FROM test_csdb2 WHERE id IN(3001,3002,3000,2998) GROUP BY id ORDER BY m ASC;看图:
一下被你们给绕进去了,哪有那么复杂SELECT * FROM test_csdb2 WHERE id IN(3001,3002,3000) order by `out`-c2 limit 1
我得出的这样 SELECT * , MIN( `out` - c2 ) AS m FROM clickip WHERE `date` = '20120605' AND only IN ( 287, 171, 80 ) GROUP BY only ORDER BY m ASC LIMIT 0 , 30 =============================================================== id only date in 点入次数 out 被点次数 c2 所产生的PV m 2906 171 20120605 118 217 200 17 2912 287 20120605 164 247 91 156 2924 80 20120605 102 136 142 18446744073709551610
字段 类型 整理 属性 Null 默认 额外 操作 id mediumint(8) UNSIGNED 否 无 auto_increment only varchar(7) gbk_chinese_ci 否 无 date int(9) UNSIGNED 否 0 in int(10) UNSIGNED 否 0 out int(10) UNSIGNED 否 0 c2 int(10) UNSIGNED 否 0
这样哪里不对吗?mysql> SELECT * , MIN( `out` - c2 ) AS m -> FROM test_csdb2 -> WHERE `date` = '20120605' -> AND only -> IN ( 257, 188, 150 ) -> GROUP BY only -> ORDER BY m ASC -> LIMIT 0 , 30; +------+------+----------+------+------+------+------+ | id | only | date | in | out | c2 | m | +------+------+----------+------+------+------+------+ | 3001 | 257 | 20120605 | 2 | 5 | 3 | 2 | | 3002 | 188 | 20120605 | 1 | 10 | 2 | 8 | +------+------+----------+------+------+------+------+ 2 rows in set (0.00 sec)mysql> select * from test_csdb2; +------+------+----------+------+------+------+ | id | only | date | in | out | c2 | +------+------+----------+------+------+------+ | 3002 | 188 | 20120605 | 1 | 10 | 2 | | 3001 | 257 | 20120605 | 2 | 5 | 3 | | 3000 | 258 | 20120605 | 2 | 2 | 2 | | 2999 | 279 | 20120605 | 2 | 9 | 2 | | 2998 | 150 | 20100605 | 1 | 6 | 7 | +------+------+----------+------+------+------+ 5 rows in set (0.00 sec)
有负数啊:mysql> SELECT * , MIN( `out` - c2 ) AS m -> FROM test_csdb2 -> WHERE `date` = '20120605' -> GROUP BY only -> ORDER BY m ASC; +------+------+----------+------+------+------+------+ | id | only | date | in | out | c2 | m | +------+------+----------+------+------+------+------+ | 2998 | 150 | 20120605 | 1 | 6 | 7 | -1 | | 3000 | 258 | 20120605 | 2 | 2 | 2 | 0 | | 3001 | 257 | 20120605 | 2 | 5 | 3 | 2 | | 2999 | 279 | 20120605 | 2 | 9 | 2 | 7 | | 3002 | 188 | 20120605 | 1 | 10 | 2 | 8 | +------+------+----------+------+------+------+------+ 5 rows in set (0.00 sec)+-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | only | int(3) | YES | | NULL | | | date | int(8) | YES | | NULL | | | in | int(2) | YES | | NULL | | | out | int(2) | YES | | NULL | | | c2 | int(2) | YES | | NULL | | +-------+--------+------+-----+---------+-------+
select MIN(out-c2) from table where id in(3001,3002,3000);
SELECT MIN( out - c2 )
FROM clickip
WHERE only
IN ( 188, 257 ) 这句好像还执行不了
#id m
#3001 2
SELECT MIN(`out`-c2) FROM test_csdb2 WHERE id IN(3001,3002,3000);#MIN(`out`-c2)
#2
where (out-c2) in (select min(out-c2) from tbl_name where id in(3001,3002,3000))
and id in(3001,3002,3000)
SQL codeSELECT id,MIN(`out`-c2) AS m FROM test_csdb2 WHERE id IN(3001,3002,3000) GROUP BY id ORDER BY m ASC LIMIT 1;
#id m
#3001 2好像算出了 0是最小的 负数比0大
我得出的这样
SELECT * , MIN( `out` - c2 ) AS m
FROM clickip
WHERE `date` = '20120605'
AND only
IN ( 287, 171, 80 )
GROUP BY only
ORDER BY m ASC
LIMIT 0 , 30
===============================================================
id only date in 点入次数 out 被点次数 c2 所产生的PV m
2906 171 20120605 118 217 200 17
2912 287 20120605 164 247 91 156
2924 80 20120605 102 136 142 18446744073709551610
id mediumint(8) UNSIGNED 否 无 auto_increment
only varchar(7) gbk_chinese_ci 否 无
date int(9) UNSIGNED 否 0
in int(10) UNSIGNED 否 0
out int(10) UNSIGNED 否 0
c2 int(10) UNSIGNED 否 0
-> FROM test_csdb2
-> WHERE `date` = '20120605'
-> AND only
-> IN ( 257, 188, 150 )
-> GROUP BY only
-> ORDER BY m ASC
-> LIMIT 0 , 30;
+------+------+----------+------+------+------+------+
| id | only | date | in | out | c2 | m |
+------+------+----------+------+------+------+------+
| 3001 | 257 | 20120605 | 2 | 5 | 3 | 2 |
| 3002 | 188 | 20120605 | 1 | 10 | 2 | 8 |
+------+------+----------+------+------+------+------+
2 rows in set (0.00 sec)mysql> select * from test_csdb2;
+------+------+----------+------+------+------+
| id | only | date | in | out | c2 |
+------+------+----------+------+------+------+
| 3002 | 188 | 20120605 | 1 | 10 | 2 |
| 3001 | 257 | 20120605 | 2 | 5 | 3 |
| 3000 | 258 | 20120605 | 2 | 2 | 2 |
| 2999 | 279 | 20120605 | 2 | 9 | 2 |
| 2998 | 150 | 20100605 | 1 | 6 | 7 |
+------+------+----------+------+------+------+
5 rows in set (0.00 sec)
-> FROM test_csdb2
-> WHERE `date` = '20120605'
-> GROUP BY only
-> ORDER BY m ASC;
+------+------+----------+------+------+------+------+
| id | only | date | in | out | c2 | m |
+------+------+----------+------+------+------+------+
| 2998 | 150 | 20120605 | 1 | 6 | 7 | -1 |
| 3000 | 258 | 20120605 | 2 | 2 | 2 | 0 |
| 3001 | 257 | 20120605 | 2 | 5 | 3 | 2 |
| 2999 | 279 | 20120605 | 2 | 9 | 2 | 7 |
| 3002 | 188 | 20120605 | 1 | 10 | 2 | 8 |
+------+------+----------+------+------+------+------+
5 rows in set (0.00 sec)+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| only | int(3) | YES | | NULL | |
| date | int(8) | YES | | NULL | |
| in | int(2) | YES | | NULL | |
| out | int(2) | YES | | NULL | |
| c2 | int(2) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
136 - 142 = 184467…… ?
你将c2 改为 `c2` 试试。我的显示正常:
136 - 142 = -6+------+------+----------+------+------+------+------+
| id | only | date | in | out | c2 | m |
+------+------+----------+------+------+------+------+
| 3003 | 187 | 20120605 | 1 | 136 | 142 | -6 |
| 2998 | 150 | 20120605 | 1 | 6 | 7 | -1 |
| 3000 | 258 | 20120605 | 2 | 2 | 2 | 0 |
| 3001 | 257 | 20120605 | 2 | 5 | 3 | 2 |
| 2999 | 279 | 20120605 | 2 | 9 | 2 | 7 |
| 3002 | 188 | 20120605 | 1 | 10 | 2 | 8 |
+------+------+----------+------+------+------+------+
蛋疼这个Limit有什么用。