--测试代码
use
SQL_Test
go
create table ttime(
bh varchar(200) not null,
sj datetime,
num int not null
)
insert into ttime
select '2009111601','2009-11-16 15:01:46','yes'
union all
select '2009111601','2009-11-16 15:02:46','yes'
union all
select '2009111601','2009-11-16 15:03:46','yes'
union all
select '2009111601','2009-11-16 15:04:46','no'
union all
select '2009111602','2009-11-16 15:05:46','yes'
union all
select '2009111602','2009-11-16 15:06:46','no'--查询
??
--期望结果
no
no
我用了如下两个代码只能输出
yes
yes $result=mysql_query("SELECT num, max(sj) as sj FROM ttime GROUP BY bh");
while($row=mysql_fetch_array($result)){
echo $row["num"]."<br />";
}
use
SQL_Test
go
create table ttime(
bh varchar(200) not null,
sj datetime,
num int not null
)
insert into ttime
select '2009111601','2009-11-16 15:01:46','yes'
union all
select '2009111601','2009-11-16 15:02:46','yes'
union all
select '2009111601','2009-11-16 15:03:46','yes'
union all
select '2009111601','2009-11-16 15:04:46','no'
union all
select '2009111602','2009-11-16 15:05:46','yes'
union all
select '2009111602','2009-11-16 15:06:46','no'--查询
??
--期望结果
no
no
我用了如下两个代码只能输出
yes
yes $result=mysql_query("SELECT num, max(sj) as sj FROM ttime GROUP BY bh");
while($row=mysql_fetch_array($result)){
echo $row["num"]."<br />";
}
truncate table ttime
alter table ttime add num varchar(20) not null
insert into ttime
select '2009111601','2009-11-16 15:01:46','yes'
union all
select '2009111601','2009-11-16 15:02:46','yes'
union all
select '2009111601','2009-11-16 15:03:46','yes'
union all
select '2009111601','2009-11-16 15:04:46','no'
union all
select '2009111602','2009-11-16 15:05:46','yes'
union all
select '2009111602','2009-11-16 15:06:46','no'--MSSQL查询
select bh,num='no',max(sj) as sj from ttime group by bh
--Mysql查询
select bh,num,max(sj) as sj from ttime group by bh
--结果
2009111601 no 2009-11-16 15:04:46.000
2009111602 no 2009-11-16 15:06:46.000
SELECT * FROM ttime t
WHERE NOT EXISTS
(SELECT 1 FROM ttime WHERE bh=t.bh AND sj>t.sj);
Incorrect integer value: 'yes' for column 'num' at row 1
字段num的数据类型与插入数据'yes'的数据类型不一致
2、grouy 始终输出找到的第一个符合条件的字段
你需要
SELECT * FROM ttime t
WHERE NOT EXISTS
(SELECT 1 FROM ttime WHERE bh=t.bh AND sj>t.sj) group by bh
$result=mysql_query("select bh,num='no',max(sj) as sj from ttime group by bh");
while($row=mysql_fetch_array($result)){
echo $row["num"]."<br />";
}
?>
select bh,num,max(sj) as sj from ttime group by bh这句MYSQL的
我的理解是:
1、SELECT * FROM ttime t WHERE NOT EXISTS
首先选中不为空的所有行,将这些选中的值负值到t2、SELECT 1 FROM ttime WHERE bh=t.bh AND sj>t.sj
bh=t.bh就等同于group by bh了?
而sj>t.sj就等于max(sj)了?
实在不明白,还望达人指点一二
我试了下 //$result=mysql_query("select bh,num,max(sj) as sj from ttime group by bh(select bh,num='no',max(sj) as sj from ttime group by bh)"); $result=mysql_query("select bh,num='no',max(sj) as sj from ttime group by bh(select bh,num,max(sj) as sj from ttime group by bh)");
while($row=mysql_fetch_array($result)){
echo $row["num"]."<br />";
}但都返回
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\test\c.php on line 26
这个语句应该可以用,不过不是通用的语句,我本地暂时没调试环境,就按自己的猜测写的,希望有用
还有你的num类型要更改为字符类型
p.s. num的类型我之前就改了,1楼的主贴不能编辑了:)
出现了这个结果
然后跑回去在php叶面里面运行第二句,还是输出的两个yes