我如何解决SQL查询问题 [英] How do I solve an sql query issue

问题描述

嗨。



我有一个包含各种列的数据库表。它是一个下载表，捕获'download_date'，'file_format'（从下载页面表单中获取下载的文件格式。它捕获数据，如：pdf，epub，...）。如何查询数据库以提取在特定时间范围内下载的每种文件格式的数量（pdf，epub，...）。





请尽快回复。



谢谢。

解决方案

请看下面的查询：

  SELECT 格式，COUNT（  DISTINCT  格式） AS  NumberOfDownloads 
  FROM  data_table 
  WHERE  download_date   '  2014-02-01' 和 '  2014-02-28' 
  GROUP   BY 格式

结果应该是：

 pdf 5 
 epub 19 
 ... 
 iso 2 
等。

DISTINC T 语句 COUNT（）函数用于消除重复行。这意味着每种格式（如：'pdf'，'epub'）只计算一次，即使有数千个pdf，epub也是。



更多信息，请参阅：使用DISTINCT消除重复项 [ ^ ]

尝试：

  SELECT  COUNT（Pdf） AS  PDF，COUNT（Epub） AS  EPUB 
  FROM  MyTable 
 < span class =code-keyword> WHERE  downLoadDate  BETWEEN  '  2014-01-01'  AND  '  2014年2月28日 

非常感谢你们！非常感谢你的帮助。在看到你的解决方案之后，我决定研究更多...我得到了我想要的完美解决方案。



这里是以下：

  SELECT 格式，COUNT（格式）
  FROM （ SELECT   DISTINCT 电子邮件，格式 FROM  data_table  WHERE  download_date  BETWEEN  '   2014-01-01'  AND  '   2014-02-28'）
  AS 结果
 < span class =code-keyword> GROUP   BY 格式

Hi.

I have a database table with various columns. Its a download table that captures the 'download_date', 'file_format'(which gets the downloaded file format from the download page form. it captures data like: pdf, epub,...). How do query the database to extract the number of each file format downloaded(pdf, epub,...) in a specific time frame.


Kindly reply soon.

Thank you.

解决方案

Please, have a look at below query:

SELECT format, COUNT(DISTINCT format) AS NumberOfDownloads
FROM data_table
WHERE download_date between '2014-02-01' and '2014-02-28'
GROUP BY format

A result should be:

pdf    5
epub   19
...
iso    2
etc.

DISTINCT statement inside COUNT() function is used to eliminate duplicate rows. It means that each format (like: 'pdf', 'epub') is counted only one time even if there is thousands of pdf's, epub's.

For further information, please see: Eliminating Duplicates with DISTINCT[^]

Try:

SELECT COUNT(Pdf) AS PDF, COUNT(Epub) AS EPUB 
FROM MyTable 
WHERE downLoadDate BETWEEN '2014-01-01' AND '2014-02-28'

Thank you so much guys! I really appreciate your assistance. After seeing your solutions, I decided to research more..and I got the perfect solution I was looking for.

Here it is below:

SELECT format, COUNT(format)
FROM (SELECT DISTINCT email, format FROM data_table WHERE download_date BETWEEN '2014-01-01' AND '2014-02-28')
AS result
GROUP BY format

这篇关于我如何解决SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！