PHP+mysql常用查询语句集合

PHP 1940 0 2013-04-30

一、查询表中最小的数据
select id,min(price) from table where id=8 group by price

二、查询指定某数之间
select id,price from table where id=8 and (price between {$sp} and {$ep})

三、多表查询
select hotel.* from hotel,room where hotel.id=room.j_hid order by hotel.id asc

四、查询重复数据且只输出一条
select *,count(distinct hid) from room group by j_hid

五、时间段查询
select * from tablename where date between '{$stime}' and '{$etime}'

上一篇：PHP将内容插入到XML文件中的简单方法

下一篇：PHPLIB中的template.inc应用实例

讨论数量：3

天涯网魂 3 杠 5 星2014-11-06 14:15:11

也可以这样查询时间段：

DATE_FORMAT(j_time,'%Y-%m-%d') between '{$s_date_s}' and '{$s_date_e}'

天涯网魂 3 杠 5 星2014-10-29 11:22:56

MYSQL+PHP查询时间段语句和循环显示

例1：

$sql1 = "select * from sort_info where DATE_FORMAT(vote_time,'%Y-%m') ='2014-10-29'";

例2：
$conn = new mysqli('localhost','root','','test'); //连接数据库

$result = $conn->query("select * from mydb where DATE_FORMAT(date, '%Y-%m-%d') >= '2009-02-22' and DATE_FORMAT(date, '%Y-%m-%d') <= '2012-03-23' "); //查询时间语句

while($row = $result->fetch_array()){ //循环显示 $result->fetch_array()

echo $row[0]."<br>";

}

天涯网魂 3 杠 5 星2013-09-17 11:58:09

按年汇总，统计：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable where 1 group by date_format(col, '%Y ') ;

按月汇总，统计：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable where 1 group by date_format(col, '%Y-%m ') ;

按季度汇总，统计：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable where 1 group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
select sum(mymoney) as totalmoney,count(*) as sheets from mytable where 1 group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

按小时：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable where 1 group by date_format(col, '%Y-%m-%d %H ') ;

一、年度查询
查询本年度的数据
SELECT *
FROM blog_article
WHERE year( FROM_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))

二、查询季度数据
查询数据附带季度数
SELECT ArticleId, quarter( FROM_UNIXTIME( `BlogCreateTime` ) )
FROM `blog_article`
其他的同前面部分：查询本季度的数据
SELECT *
FROM blog_article
WHERE quarter( FROM_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))

三、查询月度数据
本月统计(MySQL)
select * from booking where month(booking_time) =

month(curdate()) and year(booking_time) = year(curdate())

本周统计(MySQL)

select * from spf_booking where month(booking_time) =

month(curdate()) and week(booking_time) = week(curdate())

四、时间段

N天内记录

WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= N

请先登录再发表讨论。 2024-04-29

登录注册