sql查询获取从一月到当月的所有数据，即使没有记录（sql获取上个月的数据）

25-02-21 14

关于sql查询获取从一月到当月的所有数据，即使没有记录和sql获取上个月的数据的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于MySQL查询当天、周、月,最近一周、一月的数据，以及当年每月

关于sql查询获取从一月到当月的所有数据，即使没有记录和sql获取上个月的数据的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于MySQL 查询当天、周、月,最近一周、一月的数据，以及当年每月的统计数据、MySQL 查询某个字段不重复的所有记录、mysql-MySQL查询一年中每个月的所有数据数量、MySQL查询不返回所有记录等相关知识的信息别忘了在本站进行查找喔。

本文目录一览：

sql查询获取从一月到当月的所有数据，即使没有记录（sql获取上个月的数据）
MySQL 查询当天、周、月,最近一周、一月的数据，以及当年每月的统计数据
MySQL 查询某个字段不重复的所有记录
mysql-MySQL查询一年中每个月的所有数据数量
MySQL查询不返回所有记录

sql查询获取从一月到当月的所有数据，即使没有记录（sql获取上个月的数据）

我对sql不好，所以任何帮助世界都很棒

我有一个SQL查询，该查询获取从1月到本月注册的记录

我的代码示例

SELECT DatePart(YEAR, p.createStamp) as TheYear, DatePart(MONTH, p.createStamp) as TheMonth ,  COUNT(p.pId) AS TOTALCOUNT FROM profile p with(nolock)where DatePart(YEAR, p.createStamp) = DATEPART(YEAR, GETDATE())GROUP BY YEAR(p.createStamp), MONTH(p.createStamp)ORDER BY YEAR(p.createStamp), MONTH(p.createStamp)

查询将如何带回

2月= 2、3月= 3、4月= 4和5月= 5

我想让它带回Jan = 1（总数为0）和June = 6（总数为0）以及任何想法如何做到这一点？

谢谢你。

答案1

小编典典

这是创建月/年组合并将其用作查询基础的循环：

declare @startDate as datetimeset @startDate = ''1/1/13''declare @currentDate as datetimeset @currentDate = ''6/6/13''select     month(@currentDate) as monthOfDate    ,year(@currentDate) as yearOfDateinto #allDateswhere 1=0while (@startDate <= @currentDate)begin    insert into #allDates values (month(@startDate),year(@startDate))    set @startDate = dateadd(m,1,@startDate)endselect      _monthYear.yearofDate    ,_monthYear.monthOfDate    , COUNT(p.pId) as totalfrom #allDates _monthYearleft join profile p with(nolock)    on month(p.createStamp) = _monthYear.monthOfDate    and year(p.createStamp) = _monthYear.yearOfDategroup by     _monthYear.yearofDate    ,_monthYear.montOfDatedrop table #allDates

MySQL 查询当天、周、月,最近一周、一月的数据，以及当年每月的统计数据

最近在做项目的时候，需要查询当月数据，就整理类似的这些语句，希望对大家有所帮助

---以下例子时间字段默认为时间格式,如时间字段存储格式为时间戳，需如下处理:
from_unixtime(`时间字段`)

查询当天数据:

SELECT * FROM 表名 WHERE TO_DAYS(表中时间字段) = TO_DAYS(NOW());

查询当周数据：

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(表中时间字段，‘%Y-%m-%d’)) = YEARWEEK(NOW());

查询当月数据：

 SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(表中时间字段，‘%Y-%m’)) = DATE_FORMAT(CURDATE(),''%Y-%m'');

查询最近一周数据：

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 WEEK) <= DATE(表中时间字段)；

查询最近一月内的数据：

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(表中时间字段)；

查询当年每月的统计数据：

SELECT MONTH(`表中日期字段`), COUNT(*) FROM `表名` WHERE YEAR(CURDATE()) GROUP BY MONTH(`表中日期字段`)

MySQL 查询某个字段不重复的所有记录

现在想从这5条记录中查询所有title不重复的记录

假设现在有如下N条记录表明叫book
id author title
1 aaa AAA
2 bbb BBB
3 ccc CCC
4 ddd DDD
5 eee AAA
现在想从这5条记录中查询所有title不重复的记录
select distinct title,author from book这样是不可以的因为distinct只能作用于一个字段
想请教应该怎么写
答案:
代码如下:
select a.* from book a right join (
select max(id) id from book group by title) b on b.id = a.id
where a.id is not null

如果选第一条符合的记录，那么用min(id)就行了

代码如下:
select a.* from book a right join (
select min(id) id from book group by title) b on b.id = a.id
where a.id is not null

mysql-MySQL查询一年中每个月的所有数据数量

mysql

mysql写一条sql语句查询一年中每个月的所有数据数量。时间字段是uptime 是decimal类型的

MySQL查询不返回所有记录

我在我的数据库中有几个表,并使用左外连接来连接表并执行查询.问题是,并非所有匹配的记录都被返回,第一个记录总是因某种原因从结果中被跳过.

这是SQL Fiddle的链接,

架构测试数据：

MysqL> select * from main;
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
| username | date       | water_quantity | water_chilled | smoked_what | smoke_count | sleep_duration | study_duration | screen_duration | loud_level | heat_level | humidity_level | stress_physical | stress_mental | stress_notes             | menstruation |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
| test123  | 2012-09-16 |              1 | no            | cigarettes  |          20 |            480 |              0 |             420 |          2 |          7 |              7 |               6 |             4 | Roamed a lot on the bike | no           |
| test123  | 2012-09-13 |              2 | no            | cigarettes  |          12 |            300 |              0 |               0 |          1 |          1 |              1 |               6 |             3 | met friends              | no           |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
2 rows in set (0.00 sec)

MysqL> select * from food;
+----------+------------+--------------+
| username | date       | food         |
+----------+------------+--------------+
| test123  | 2012-09-16 | rice         |
| test123  | 2012-09-16 | pizza        |
| test123  | 2012-09-16 | french fries |
| test123  | 2012-09-16 | burger       |
| test123  | 2012-09-13 | naan         |
| test123  | 2012-09-13 | fried rice   |
| test123  | 2012-09-13 | lemon juice  |
+----------+------------+--------------+
7 rows in set (0.00 sec)

MysqL> select * from alcohol;
+----------+------------+--------------+------------------+
| username | date       | alcohol_type | alcohol_quantity |
+----------+------------+--------------+------------------+
| test123  | 2012-09-16 | beer         |                0 |
| test123  | 2012-09-16 | beer_ale     |                0 |
| test123  | 2012-09-16 | absinthe     |                0 |
| test123  | 2012-09-13 | rum          |                0 |
| test123  | 2012-09-13 | tequila      |                0 |
+----------+------------+--------------+------------------+
5 rows in set (0.00 sec)

MysqL> select * from headache;
+----------+------------+-----------+----------+---------------------+
| username | date       | intensity | duration | notes               |
+----------+------------+-----------+----------+---------------------+
| test123  | 2012-09-16 |         6 |       12 | something something |
+----------+------------+-----------+----------+---------------------+
1 row in set (0.00 sec)

MysqL> select * from headache_areas;
+----------+------------+-----------------+
| username | date       | area            |
+----------+------------+-----------------+
| test123  | 2012-09-16 | left_temple     |
| test123  | 2012-09-16 | right_temple    |
| test123  | 2012-09-16 | behind_left_ear |
+----------+------------+-----------------+
3 rows in set (0.00 sec)

MysqL> select * from headache_symptoms;
+----------+------------+-----------+
| username | date       | symptoms  |
+----------+------------+-----------+
| test123  | 2012-09-16 | aura      |
| test123  | 2012-09-16 | vertigo   |
| test123  | 2012-09-16 | dizziness |
+----------+------------+-----------+
3 rows in set (0.00 sec)

MysqL> select * from alcohol;
+----------+------------+--------------+------------------+
| username | date       | alcohol_type | alcohol_quantity |
+----------+------------+--------------+------------------+
| test123  | 2012-09-16 | beer         |                0 |
| test123  | 2012-09-16 | beer_ale     |                0 |
| test123  | 2012-09-16 | absinthe     |                0 |
| test123  | 2012-09-13 | rum          |                0 |
| test123  | 2012-09-13 | tequila      |                0 |
+----------+------------+--------------+------------------+
5 rows in set (0.00 sec)

MysqL> select * from drugs;
+----------+------------+----------+
| username | date       | drug     |
+----------+------------+----------+
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test123  | 2012-09-13 | ching    |
| test123  | 2012-09-13 | chong    |
| test123  | 2012-09-13 | blah1    |
| test123  | 2012-09-13 | blurg2   |
+----------+------------+----------+
10 rows in set (0.00 sec)

查询我尝试了结果：

MysqL> SELECT m.*,->                 GROUP_CONCAT(disTINCT f.food SEParaTOR ',') AS food,->                 GROUP_CONCAT(disTINCT a.alcohol_type SEParaTOR ',') AS alcohol,->                 a.alcohol_quantity,->                 GROUP_CONCAT(disTINCT d.drug SEParaTOR ',') AS drug,->                 h.intensity AS headache_intensity,->                 h.duration AS headache_duration,->                 GROUP_CONCAT(disTINCT ha.area) AS headache_areas,->                 GROUP_CONCAT(disTINCT hs.symptoms) AS headache_symptoms,->                 h.notes AS headache_notes
    ->             FROM main AS m 
    ->                 LEFT OUTER JOIN food AS f ON f.username = m.username AND f.date = m.date 
    ->                 LEFT OUTER JOIN headache AS h ON h.username = m.username AND h.date = m.date
    ->                 LEFT OUTER JOIN headache_symptoms AS hs ON hs.username = m.username AND hs.date = m.date
    ->                 LEFT OUTER JOIN headache_areas AS ha ON ha.username = m.username AND ha.date = m.date
    ->                 LEFT OUTER JOIN drugs AS d ON d.username = m.username AND d.date = m.date
    ->                 LEFT OUTER JOIN alcohol AS a ON a.username = m.username AND a.date = m.date
    ->            ;
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+
| username | date       | water_quantity | water_chilled | smoked_what | smoke_count | sleep_duration | study_duration | screen_duration | loud_level | heat_level | humidity_level | stress_physical | stress_mental | stress_notes             | menstruation | food                                                       | alcohol                            | alcohol_quantity | drug                     | headache_intensity | headache_duration | headache_areas                           | headache_symptoms      | headache_notes      |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+
| test123  | 2012-09-16 |              1 | no            | cigarettes  |          20 |            480 |              0 |             420 |          2 |          7 |              7 |               6 |             4 | Roamed a lot on the bike | no           | rice,pizza,french fries,burger,naan,fried rice,lemon juice | beer,beer_ale,absinthe,rum,tequila |                0 | ching,chong,blah1,blurg2 |                  6 |                12 | left_temple,right_temple,behind_left_ear | aura,vertigo,dizziness | something something |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+
1 row in set (0.00 sec)

它没有向我显示表中的其他记录.有人可以帮我解决这个问题吗？提前致谢.

最佳答案

它完全输出你“要求它”做的事情(使用group concat)：

USERNAME    |...| FOOD                                                       | ALCOHOL  ...
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
test123     |...| rice,lemon juice |  beer,tequila  ...

使用组concat时,它将分组列的所有值连接到一个输出字段.

更新：
我想我知道你想要实现什么,因为它在表main的两个记录中都是相同的用户名,它将显示为一列,解决你需要找到第一个字段,这两个记录不相同 – 这是约会.所以你要做的就是将GROUP BY m.username,date添加到end of your query.

关于sql查询获取从一月到当月的所有数据，即使没有记录和sql获取上个月的数据的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于MySQL 查询当天、周、月,最近一周、一月的数据，以及当年每月的统计数据、MySQL 查询某个字段不重复的所有记录、mysql-MySQL查询一年中每个月的所有数据数量、MySQL查询不返回所有记录的相关信息，请在本站寻找。

本文标签：