关于SQL查询的问题。 [英] Question about SQL Query.

问题描述

大家好，



我有3张桌子的桌子。



1 an ID 6 char

2 a ControlPoint 2 char（值从A0到A8）

3 a数据时间。



我的问题是关于我想获得比较小的ID A3和最小月份。



我可以通过sepparate，ControlPoint或Datetime获得它，但不能同时获得它。

我有任何选择来获取我的查询或那是不可能的吗？



感谢您的回答!!



PD：我无法更改表格的任何属性。 （禁止）

---

被PIEBALDconsult感动



想象一下这张桌子。



表A

Hi everybody,

I'm having a table with 3 fileds.

1 an ID 6 char
2 a ControlPoint 2 char (values are from A0 to A8)
3 a Datatime.

My question is about i want to get the ID's that are smaller to e.g. "A3" and a minimum month old.

I can get it by sepparate, ControlPoint or Datetime, but no both.
I have any option to get my query or that's impossible?

Thanks for your answers!!.

PD: I can't change any properties of the table. (Forbidden)

---

Moved by PIEBALDconsult

Imagine this table.

Table A

Field 1 ID Field 2 CP Field 3 Datetime
---------- ---------- ----------------
A00001     A0         10/10/2014


A10000     A0         10/10/2014
A10000     A1         11/10/2014
A10000     A2         11/11/2014
A10000     A3         11/11/2014
A10000     A4         15/12/2014
A10000     A5         17/12/2014

A20000     A0         10/10/2014
A20000     A1         12/10/2014
A20000     A2         13/11/2014
A20000     A3         14/11/2014
A20000     A4         15/12/2014
A20000     A5         17/12/2014
A20000     A6         17/12/2014

A30000     A0         10/10/2014
A30000     A1         12/10/2014
A30000     A2         01/11/2014

i想得到身份证t小于例如A3和今天03/12/2014之间的日期和场地必须至少一个月。



查询结果应为A00001和A30000 。



对不起我的英文。我知道不清楚。

i want to get the ID's that are smaller to e.g. "A3" and the date between today 03/12/2014 and the field has to be minimum a month old.

the result of the query should be A00001 and A30000.

Sorry for my english. I know isn't clear.

推荐答案

您好！查看此查询希望对您有所帮助。

Hi Check this query hope this will help you.

-- Create Table
Create Table A
(
ID  char(6),
CP char(2),
CPDate Datetime
)
--Sample data Insert
Insert into A Values('A00001',     'A0',         '10/10/2014')
 Insert into A Values('A10000',     'A0',         '10/10/2014')
  Insert into A Values('A10000',     'A1',         '10/11/2014')
 Insert into A Values('A10000',     'A2',         '11/11/2014')
  Insert into A Values('A10000',     'A3',         '11/11/2014')
 Insert into A Values('A10000',     'A4',         '12/15/2014')
 Insert into A Values('A10000',     'A5',         '12/17/2014')  
 Insert into A Values('A20000',     'A0',         '10/10/2014')
  Insert into A Values('A20000',     'A1',         '10/12/2014')
  Insert into A Values('A20000',     'A2',         '11/13/2014')
 Insert into A Values('A20000',     'A3',         '11/14/2014')
 Insert into A Values('A20000',     'A4',         '12/15/2014')
 Insert into A Values('A20000',     'A5',         '12/17/2014')
  Insert into A Values('A20000',     'A6',         '12/17/2014')  
 Insert into A Values('A30000',     'A0',         '10/10/2014')
  Insert into A Values('A30000',     'A1',         '10/12/2014')
  Insert into A Values('A30000',     'A2 ',         '11/01/2014')

-- Select Query
SELECT ID,count(CP) FROM A 
where CPDate between DateAdd(mm,-1,CPDate)
 and getdate()
GROUP BY ID
HAVING count(CP)<4 
order by ID;

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