时间戳

首页 > 解决方案 > Reducing lines and readability in SQL query

sql - Reducing lines and readability in SQL query

问题描述

I have 2 tables namely:

list_a:

 ID  Date  pg

23  2016-11-30  sub
23  2016-12-03  sub
23  2016-12-04  sub
23  2016-12-05  sub
69  2017-07-21  Closed
69  2017-07-22  Closed
69  2017-07-23  Closed
92  2016-11-29  sub
92  2016-11-30  sub
46  2017-01-11  In
46  2017-01-12  In
46  2017-01-13  In

list_b:

ID  Name

23  B
46  B
69  X
92  B

My task is to write an sql query such that I get count of each date with name 'B' and the count should be greater than 4.

So far I have written the following query which gives me the output:

SELECT date, count(Name) as CountName FROM
(
SELECT list_b.ID as Id,list_b.Name as Name,Date from list_b
inner JOIN list_a
on list_b.ID=list_a.ID
) t
where Name='B'
group by Date
Having count(Name)>4
order by count(Name) desc;

I am trying to make my query more readable and less chunkier. Is there a better way that you can suggest to solve this problem?

Note: the above tables are snippets of the original tables!

标签： sqlsql-server

解决方案

您有一个不必要的备用子查询。还有个人偏好使用别名而不是表名（在名为 t_databaseassociation 的表上，这确实有很大的不同）

尝试

SELECT date, count(name) as countname 
 from list_b b
  inner JOIN list_a a on b.ID= a.ID
 where Name='B'
  group by Date
   Having count(Name)>4
    order by count(Name) desc;

推荐阅读