SELECT * FROM test WHERE sn IN(SELECT sn FROM test GROUP BY sn HAVING COUNT(sn)>1)access不知是否支持上面的写法 几万条数据应该木有问题
在sn上建立索引 select * from tt a where exists(select 1 from tt where a.sn=sn and a.id>id) or select sn fom tt group by sn having count(*)>=2
select sn from 表test group by sn having count(*)>1
楼上正解我还有个通用版:select * from tablename group by fieldname having count(fieldname)>1;
THANKS~SELECT * FROM test WHERE sn IN(SELECT sn FROM test GROUP BY sn HAVING COUNT(sn)>1)另外,如果要加时间的条件,该如何写啊? 比如:2011-10-1 00:00:00 到 2011-11-1 00:00:00
select * from test where sn in (select sn from test where 时间 between '2011-10-1 00:00:00' and '2011-11-1 00:00:00' group by sn having count(*)>1)
加入时间比对后,ACCESS出现错误如下.是不是我类写的有点问题啊. Warning: odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\access\includes\cls_access.php on line 41我的調用語句如下:$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE BETWEEN '2008-12-25 00:00:00' AND '2008-12-27 00:00:00' GROUP BY SN HAVING count(SN) > 1) "); ACCESS类为:class Access { var $databasepath,$constr,$dbusername,$dbpassword,$link; function Access($databasepath,$dbusername,$dbpassword) { $this->databasepath=$databasepath; $this->username=$dbusername; $this->password=$dbpassword; $this->connect(); } function connect() { $this->constr='DRIVER={Microsoft Access Driver (*.mdb)};DBQ='.realpath($this->databasepath).';'; $this->link=odbc_connect($this->constr,$this->username,$this->password,SQL_CUR_USE_ODBC); if(!$this->link) echo "數據庫連接失敗!"; return $this->link; } function query($sql) { return @odbc_exec($this->link,$sql); } function fetch_array($query) { return odbc_fetch_array($query); } function fetch_row($query) { return odbc_fetch_row($query); } function num_rows($sql) { return odbc_num_rows($this->query($sql)); } function close() { odbc_close($this->link); } function getlist($sql) { $query=$this->query($sql); $recordlist=array(); while (!!$row=$this->fetch_array($query)) { $recordlist[]=$row; } return $recordlist; } }
忘了写时间,不有错误依旧$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE date BETWEEN '2008-12-25 00:00:00' AND '2008-12-27 00:00:00' GROUP BY SN HAVING count(SN) > 1) ");
很奇怪:DEBUG如下:$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE date BETWEEN 2008-12-25 AND 2008-12-27 GROUP BY SN HAVING count(SN) > 1) ");以上,沒有錯誤信息$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE date BETWEEN '2008-12-25' AND '2008-12-27' GROUP BY SN HAVING count(SN) > 1) ");加了引号之后,错误信息为: Warning: odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\access\includes\cls_access.php on line 41
我在网上找到了,ACCESS是用#号,$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1) ");还有一个问题,请帮忙解决一下: 如下是表数据结构: id sn other 1 8502 1 2 8502 0 3 4506 1 4 4506 1 我想实现找到SN相同的时候,看other字段是否也相同,SQL语句该怎么写啊? 谢谢~
這樣嗎?后面的Count呢? GROUP BY SN,OTHER HAVING count(SN) > 1
是否可以实现如下的功能啊: $other = 1时, SQL语句查询出other字段有相同的,都为1或者0 $other = 0时,SQL语句查询出other所有的,不管相同的还是不相同的? 如下代码只能查询出OTHER为1的,能不能帮忙改进一下,THANKS~$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE OTHER = 1 date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1) ");
//将other提到上面来判断,已经解决 if ($other) { $othersql = ' other = 1 OR other = 0'; } else { $othersql = ' 1'; } $other = $_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN (SELECT SN FROM sy_Test WHERE OTHER = $othersql date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1) ");
SELECT * FROM test WHERE sn IN(SELECT sn FROM test GROUP BY sn HAVING COUNT(sn)>1)access不知是否支持上面的写法 几万条数据应该木有问题
select * from tt a where exists(select 1 from tt where a.sn=sn and a.id>id)
or
select sn fom tt group by sn having count(*)>=2
比如:2011-10-1 00:00:00 到 2011-11-1 00:00:00
from test
where sn in (select sn from test where 时间 between '2011-10-1 00:00:00' and '2011-11-1 00:00:00' group by sn having count(*)>1)
Warning: odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\access\includes\cls_access.php on line 41我的調用語句如下:$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN
(SELECT SN FROM sy_Test WHERE BETWEEN '2008-12-25 00:00:00' AND '2008-12-27 00:00:00' GROUP BY SN HAVING count(SN) > 1)
");
ACCESS类为:class Access
{
var $databasepath,$constr,$dbusername,$dbpassword,$link;
function Access($databasepath,$dbusername,$dbpassword)
{
$this->databasepath=$databasepath;
$this->username=$dbusername;
$this->password=$dbpassword;
$this->connect();
} function connect()
{
$this->constr='DRIVER={Microsoft Access Driver (*.mdb)};DBQ='.realpath($this->databasepath).';';
$this->link=odbc_connect($this->constr,$this->username,$this->password,SQL_CUR_USE_ODBC);
if(!$this->link) echo "數據庫連接失敗!";
return $this->link;
} function query($sql)
{
return @odbc_exec($this->link,$sql);
} function fetch_array($query)
{
return odbc_fetch_array($query);
} function fetch_row($query)
{
return odbc_fetch_row($query);
} function num_rows($sql)
{
return odbc_num_rows($this->query($sql));
} function close()
{
odbc_close($this->link);
} function getlist($sql)
{
$query=$this->query($sql);
$recordlist=array();
while (!!$row=$this->fetch_array($query))
{
$recordlist[]=$row;
}
return $recordlist;
}
}
(SELECT SN FROM sy_Test WHERE date BETWEEN '2008-12-25 00:00:00' AND '2008-12-27 00:00:00' GROUP BY SN HAVING count(SN) > 1)
");
(SELECT SN FROM sy_Test WHERE date BETWEEN 2008-12-25 AND 2008-12-27 GROUP BY SN HAVING count(SN) > 1)
");以上,沒有錯誤信息$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN
(SELECT SN FROM sy_Test WHERE date BETWEEN '2008-12-25' AND '2008-12-27' GROUP BY SN HAVING count(SN) > 1)
");加了引号之后,错误信息为:
Warning: odbc_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\access\includes\cls_access.php on line 41
(SELECT SN FROM sy_Test WHERE date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1)
");还有一个问题,请帮忙解决一下:
如下是表数据结构:
id sn other
1 8502 1
2 8502 0
3 4506 1
4 4506 1
我想实现找到SN相同的时候,看other字段是否也相同,SQL语句该怎么写啊? 谢谢~
GROUP BY SN,OTHER HAVING count(SN) > 1
比如:
$other = 1时, SQL语句查询出other字段有相同的,
$other = 0时,SQL语句查询出other所有的,不管相同的还是不相同的?
$other = 1时, SQL语句查询出other字段有相同的,都为1或者0
$other = 0时,SQL语句查询出other所有的,不管相同的还是不相同的?
如下代码只能查询出OTHER为1的,能不能帮忙改进一下,THANKS~$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN
(SELECT SN FROM sy_Test WHERE OTHER = 1 date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1)
");
if ($other) {
$othersql = ' other = 1 OR other = 0';
} else {
$othersql = ' 1';
}
$other =
$_result = $_access->getlist("SELECT * FROM sy_Test WHERE SN IN
(SELECT SN FROM sy_Test WHERE OTHER = $othersql date BETWEEN #2008-12-25 00:00:00# AND #2008-12-27 00:00:00# GROUP BY SN HAVING count(SN) > 1)
");