查询“总结基于inv_no和日期> = 1/1/1 1992“的金额字段？ [英] Query on " summarise amount field based on inv_no and date>=1/1/1992"?

问题描述

我在编写查询时遇到问题，但是它失败了，请帮助



请帮助..长时间麻烦



我尝试了什么：



我试过这样，

< pre lang =SQL> 选择 id，empname，发票号，金额来自（从员工 group 按发​​票号码）其中 date> = ' 1-1-1992';

我在查询中提到的日期后提取的数据......

请帮助

解决方案

您的查询存在一些问题，解决方案3中的查询也存在一些问题。



1.避免使用储存器列名的编辑词。如果你确实使用了保留字，那么你应该用方括号括起列名，所以列 date 应该被称为 [日期]



2.同样，列名不应包含空格，它们也必须被方括号括起来，即发票没有应 [发票号]



3.查询中子句的顺序不正确 - WHERE 子句来了之前 GROUP BY 条款，而不是之后。



4.如果你是要使用子查询，则返回的每个项必须被赋予别名，以便包含在外部查询中，例如（但见下文）

 选择总和（金额）  AS  Amt  来自员工 group   by 发票号

5。关于子查询的主题，如果你打算使用它们，那么你需要检索所有你后来尝试引用的项目，即 id ， empname 等



6.您不需要子查询。



7.使用日期时，最好使用YYYY-MM-DD格式较少的格式。我们无法判断您的日期是MM-DD-YYYY还是DD-MM-YYYY，这可能会导致在尝试在应用程序中显示或使用日期时出现问题。日期应显示为'1992-01-01'



考虑到以上所有因素，但是，如果您没有向我们展示您的表格结构，任何样本数据或预期结果，那么此查询应该可以正常工作

 选择 id，[ date ]，empname，[invoice no]，sum（amount） as 金额来自员工
 其中 [ date ]> ; = '  1992-01-01' 
  group  按 id，[ date ]，empname，[发票号] 

选择id，date，empname，invoiceno，sum（amount）作为员工金额

where date> ='1-1-1992 '

按ID，日期，empname，发票n组o，发票否

我试着这样，



选择id，empname，发票号，金额来自（从发票号码中选择员工组别的金额（金额））日期> ='1-1-1992';



我提取的数据查询中提到的日期...

请帮助

I am getting problem in writing query but its failing, please help

Please help..its troubling from long time

What I have tried:

I tried to like this,

select id, empname,invoice no,amount from(select sum(amount) from employees group by invoice no ) where date>= '1-1-1992';

Data I am extracting after the mentioned date in the query...
please help

解决方案

There are several problems with your query, some of those problems also exist with the query in Solution 3.

1. Avoid the use of reserved words for column names. If you do use a reserved word then you should surround the column name with square brackets so column date should be referred to as [date]

2. Similarly, column names should not have spaces in them, they too must be surrounded by square brackets i.e. invoice no should be [invoice no]

3. The order of the clauses in your query is incorrect - the WHERE clause comes before the GROUP BY clause, not after.

4. If you are going to use sub-queries then each of the items returned must be given an alias name in order to be included in the outer query e.g. (but see below)

select sum(amount) AS Amt from employees group by invoice no 

5. Also on the subject of sub-queries, if you are going to use them then you need to retrieve all of the items you later try to refer to i.e. id, empname, etc

6. You do not need a sub-query for this.

7. When using dates it is better to use the less ambiguous format of YYYY-MM-DD. We can't tell if your date is MM-DD-YYYY or DD-MM-YYYY, and this can cause issues when trying to display or work with dates in an application. The date should be presented as '1992-01-01'

Taking all of the above into account, but with the caveat that you have not shown us your table structure, any sample data, or expected results, then this query should work

select id,[date], empname,[invoice no],sum(amount) as Amount from employees 
 where [date]>= '1992-01-01'
 group by id,[date],empname,[invoice no]

select id,date, empname,invoiceno,sum(amount) as Amount from employees
where date>= '1-1-1992'
group by id,date,empname,invoice no,invoice no

i tried to like this,

select id, empname,invoice no,amount from(select sum(amount) from employees group by invoice no ) where date>= '1-1-1992';

Data I am extracting after the mentioned date in the query...
please help

这篇关于查询“总结基于inv_no和日期&gt; = 1/1/1 1992“的金额字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！